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.

Hi, 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

Example:

start date & time in cell A1

end date and time in cell A2

This 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 A1

end time in cell A2

This formula in cell A3:=IF(MOD(A2,1)<MOD(A1,1),MOD(A2,1)+1-MOD(A1,1),MOD(A2,1)-MOD(A1,1))If you were sure that there was

nodate information in cells A1 and A2 you could dispense with the modulus calculation and the formula is just:=IF(A2<A1,A2+1-A1,A2-A1)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.

Regards

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.

Regards

Bruce

You're very welcome, Humar

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History