# Calculte hours/mins across dates

Microsoft Excel 2007
January 24, 2010 at 21:00:53
Specs: Windows XP
 In Excel 2007 I'm trying to calculate the number of hours and minutes an employee works when the shift commences at 9:00pm on one day and ends at 6:00am the following day.

See More: Calculte hours/mins across dates

#1
January 25, 2010 at 04:26:42
 Hi,First a little background on Excel's time and date systemExcel stores dates and times in the same variable. The date is stored in the Integer (whole number) part and the decimal part is the time.The date increments by 1 each day. The default is 1 for 01-Jan-1900.40203 is 25 January 2010 and 40204 is 26 Jan.40203.5 is half-way through the 25 Jan, i.e. 12 noonIf your start and end times include the correct dates then just subtract the end from the startExample:start date & time in cell A1end date and time in cell A2This formula in cell A3:`=A2-A1`Format all cells with custom format "hh:mm"If your entries are time only, with no date information or the date part of the entry may or may not have the correct date, test the end time against the start time. If end is 'earlier' than start, add 1 to finish, then subtract, or else just subtract start from end.I use a modulus division by 1 to ensure that I only have the time part of the number for the calculation, then all date information is ignored.start time in cell A1end time in cell A2This formula in cell A3:`=IF(MOD(A2,1)

Report •

#2
January 25, 2010 at 17:35:25
 Many, many thanks for your help. I have now been able to assemble the staff roster and total working hours across each day and the week correctly. This roster is for staff who provide services to people with disabilities, and to support them I proivide my (limited) computer skills on a voluntary basis - so your input is greatly appreciated. I could not have done this without your assistance, thank you once again.RegardsBruce

Report •

#3
January 26, 2010 at 03:57:22
 You're very welcome,Humar

Report •

Related Solutions