Formula help for timesheet?

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,203

Office Version

1. 365
2. 2010

Platform

1. Windows

• Apr 25, 2014

• #2

To enter a time hold down Ctrl and Shift keys and Press the ":" key. Format column C as a number in the example below.

Excel Workbook
	A	B	C
1	In	Out	Total
2	8:00 AM	12:30 PM	4.50
3	1:00 PM	8:15 PM	7.25
Sheet3 (2)

 

Upvote 0

B

bmorris

New Member

Joined

Oct 1, 2010

Messages

48

• Apr 25, 2014

• #3

Hi Joe,
I am trying to make the time in and time out super easy for some guys who are limited computer users. Is there another way they can just type the time in and the formula in c column will work?

 

Upvote 0

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,203

Office Version

1. 365
2. 2010

Platform

1. Windows

• Apr 25, 2014

• #4

If typing is easier than pressing three keys simultaneously, then type it in. If you want to pre-load the C column so it remains blank until both time in and time out are entered, modify the formula to this:

=IF(OR(A2="",B2=""),"",MOD(B2-A2,1)*24)
and copy down to cover as much of column C as you need.

 

Upvote 0

B

bmorris

New Member

Joined

Oct 1, 2010

Messages

48

• Apr 25, 2014

• #5

hmmmm....I feel silly because I can not get this to work! What format should column A and B be? I copied your formula for column c and I get nothing when I type a time in A and B.

 

Upvote 0

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,203

Office Version

1. 365
2. 2010

Platform

1. Windows

• Apr 25, 2014

• #6

bmorris said:

hmmmm....I feel silly because I can not get this to work! What format should column A and B be? I copied your formula for column c and I get nothing when I type a time in A and B.

Click to expand...

Can you give me an example of what you have entered in A and B when you get nothing in C?

 

Upvote 0

B

bmorris

New Member

Joined

Oct 1, 2010

Messages

48

• Apr 25, 2014

• #7

I think the problem may be the formatting of A and B......in this shot A and B are formatted as a number. When I format as text....naturally the formula in C column wouldn't work. So I am unsure how to format A and B to make C work! LOL

Time In	Time Out	Total Hours
8.00	10.00

<colgroup><col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;" span="2" width="89"> <col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3896;" width="110"> <tbody>
</tbody>

 

Upvote 0

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,203

Office Version

1. 365
2. 2010

Platform

1. Windows

• Apr 25, 2014

• #8

bmorris said:

I think the problem may be the formatting of A and B......in this shot A and B are formatted as a number. When I format as text....naturally the formula in C column wouldn't work. So I am unsure how to format A and B to make C work! LOL

Time In	Time Out	Total Hours
8.00	10.00

<tbody>
</tbody>

Click to expand...

format C as a number, A & B as hh:mm and enter times in those columns as 8:00 and 10:00.

 

Upvote 0

B

bmorris

New Member

Joined

Oct 1, 2010

Messages

48

• Apr 25, 2014

• #9

This is what option I used to format h:mm
and the result is

Time In	Time Out	Total Hours
0:00	0:00

<colgroup><col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;" span="2" width="89"> <col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3896;" width="110"> <tbody>
</tbody>

 

Upvote 0

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,203

Office Version

1. 365
2. 2010

Platform

1. Windows

• Apr 25, 2014

• #10

bmorris said:

This is what option I used to format h:mm
and the result is

Time In	Time Out	Total Hours
0:00	0:00

<tbody>
</tbody>

Click to expand...

You need to go to the format menu, choose Custom and enter hh:mm.

 

Upvote 0