Time Sheet

peggyal

New Member
Joined
Nov 22, 2005
Messages
40
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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
Joined
Feb 17, 2002
Messages
13,686
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 Worked
9Total Worked for week:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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
Joined
Nov 22, 2005
Messages
40
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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
Joined
Nov 22, 2005
Messages
40
That works!!!! Except what if the time rolls over into the next day? It comes up as a negative number....
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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
Joined
Nov 22, 2005
Messages
40
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?
 

Forum statistics

Threads
1,078,472
Messages
5,340,542
Members
399,383
Latest member
rahmanab001

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top