# Time Sheet

#### peggyal

##### New Member
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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Von Pookie

##### MrExcel MVP
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

##### MrExcel MVP
An example of the sheet I use:
timesheet.xls
ABCDEF
1Mon.Tues.Wed.Thurs.Fri.
2In7:30 AM7:30 AM7:30 AM3:00 PM7:30 AM
3Out10:00 AM
4In11:30 AM
5Lunch Beg.11:30 AM11:30 AM11:30 AM11:30 AM
6Lunch End12:15 PM12:15 PM12:15 PM12:15 PM
7Out4:15 PM4:15 PM4:15 PM11:52 PM4:15 PM
8Hours Worked6.58.08.08.98.0
9Total 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

##### New Member
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

##### New Member
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

##### New Member
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?

Replies
1
Views
189
Replies
20
Views
664
Replies
0
Views
132
Replies
3
Views
502
Replies
2
Views
447

1,195,672
Messages
6,011,084
Members
441,581
Latest member
rp4717

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back