Time Sheet

[peggyal]

I am doing a time sheet that registers time worked in tenths of an hour. My start time and end time is in one column by a '24 hour' clock. Is there a formula that will subtract the end time from the start time and display in TENTHS? Example Start 1500, end 2352 (a total of 8 hours and 52 minutes). Must then be displayed as 8.9

 

[Von Pookie]

Take a look at the formulas supplied in the example here:
http://www.cpearson.com/excel/overtime.htm

I used the formulas there to make my own timesheet for days when my hours are different than usual, then just formatted the cells for the results as Number with 1 decimal to get it to display as 7.3, 8.9, etc.

 

[Joe4]

I came up with this formula, assuming A1 is your start time and A2 is your end time and these cells are formatted as times and entered as such (i.e. A1=15:00 and A2=23:52):

=HOUR(A2-A1)+ROUND(MINUTE(A2-A1)/60,1)

 

[Von Pookie]

An example of the sheet I use:

timesheet.xls
	A	B	C	D	E	F
1		Mon.	Tues.	Wed.	Thurs.	Fri.
2	In	7:30 AM	7:30 AM	7:30 AM	3:00 PM	7:30 AM
3	Out	10:00 AM
4	In	11:30 AM
5	Lunch Beg.	11:30 AM	11:30 AM	11:30 AM		11:30 AM
6	Lunch End	12:15 PM	12:15 PM	12:15 PM		12:15 PM
7	Out	4:15 PM	4:15 PM	4:15 PM	11:52 PM	4:15 PM
8	Hours Worked	6.5	8.0	8.0	8.9	8.0
9	Total Worked for week:	39.4
Sheet2

Rows 3 & 4 ("Out" and "In") are used when I need to leave and come back during the day. So if I come in at 7:30, but leave for an appointment at 10 and then return at 11:30, those times get entered there.

 

[Joe4]

Response from OP from PM:

Thank you for your rapid response, but that doesn't work. I'm trying to take a time, ex. 0700 (I don't want the colon in it --07:00) until another time, ex. 1513 (no colon), then present this in hours and tenths of minutes.

Eg A1 (0700), A2 (1513) is 8 hours 13 minutes, or 8.2 (b1) Everytime I put in 0700 as a number or a time, it either changes the number to 700, or puts a colon in it. And also, what happens if the time is from 0700 to 0100 (the following day), which is a total of 18 hours....

I'd appreciate any help!!!

I had mentioned in my post that it will work IF your entries are entered as time. It appears that your entries are not entered as time. Is there some reason why you do not want to enter the entries as time entries? It can be done otherwise, but it is usually a little easier to work with data when it is entered into the format that best represents it.

Are they entered as numbers or text? If you have "0700", then you either have:
1. a text entry, or
2. a numeric entry with a custom format to keep the leading 0

It will make a difference how these values are entered.

 

[peggyal]

I do not want the 'am' or 'pm'. If i leave them as a number, how do I force a leading 0? I'm new at this. Forgive me is I'm posting to the wrong place.

 

[Joe4]

The following formula will work if they are entered as Text or Numeric (and not as Time):

=INT((A2-A1)/100)+ROUND(MOD(A2-A1,100)/60,1)

If entered as numeric, use custom format "0000" to keep the leading zero.

 

[peggyal]

That works!!!! Except what if the time rolls over into the next day? It comes up as a negative number....

 

[Joe4]

That works!!!! Except what if the time rolls over into the next day? It comes up as a negative number...

Here is a simple modification to handle that:

=INT((A2-A1)/100)+ROUND(MOD(A2-A1,100)/60,1)+IF(A1>A2,24,0)

 

[peggyal]

That works perfectly. Thank you so much! One last question along the Time Sheet question, I log overnights on this sheet by typing the name of the city. Is there anyway to assign '1' to each typed overnight and thus total at the bottom of the sheet? In other words, A1 (MIA) A8 (TLH) A22 (EWR) = 3?