Use an Excel formula to work out how many hours have been worked, excluding weekends
Q Our charity booth is open from 7:30am to 4:45pm everyday, except weekends.
Is there an Excel formula for calculating how many hours and minutes someone has worked there, allowing for them to be half an hour late on the first day and leaving half an hour early on the last day? Ali Dhillon
A The NETWORKDAYS function can eliminate the Saturdays and Sundays from the result. A combination of MEDIAN and MOD can help calculate the rest. Format cells C6 and D6 to ‘ddd d/m/yyyy h:mm AM/PM’. Format cell E6:
[h]” hrs”:mm” mins”
The square brackets tell Excel to calculate elapsed time. The result is in hours and minutes. If the person starts half an hour late on the first day, say 30 June this year, enter in cell C6:
30/6/2009 08:00
If they leave half an hour early on 7 July, enter in D6:
7/7/2009 16:15
For the total time worked, in cell E6 enter:
=(NETWORKDAYS(C6,D6) 1)*(“16:45” “07:30”)+IF(NETWORKDAYS(D6,D6),MEDIAN(MOD(D6,1),”16:45”,”07:30”),”16:45”) MEDIAN(NETWORKDAYS(C6,C6)*MOD(C6,1),”16:45”,”07:30”)
The correct answer to this example is 54 hours 30 minutes.
