Click here for important information about

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

January 25, 2010 at 04:26:42

First a little background on Excel's time and date system

Excel 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 noon

If your start and end times include the correct dates then just subtract the end from the start

start date & time in cell A1
end date and time in cell A2
This formula in cell A3:


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 A1
end time in cell A2
This formula in cell A3:


If you were sure that there was no date information in cells A1 and A2 you could dispense with the modulus calculation and the formula is just:


You can control time entry into cells using Validation to stop date information being entered, or use a short macro that enters the time 'now' for the user.


Report •

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.

Report •

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


Report •
Related Solutions

Ask Question