Accessibility options

Use Excel to calculate your working hours

Author: Stephen Wells
Date: Fri, 02 Apr 2010 09:30:00 GMT

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.

More features

Find legal advice and services online
Make the most of legal advice freely available on the internet Thanks to the internet you can do several things online that used to require a trip to a lawyer’s office, such as applying for a ...
10 useful free Google tools
Best known for its search engine, Google has developed a range of interesting and useful free tools. Here’s our pick of them Google made its name as a search engine but, over the years, it has ...
Working with windows in Windows
Microsoft Windows is all about – perhaps unsurprisingly – windows. We explain how to master them It may be stating the obvious, but windows are a fundamental part of Microsoft Windows. Almost ...
How to spot and avoid online scammers
Fake emails and websites can look very realistic, and trick thousands – but we explain how to outsmart them According to the website Bank Safe Online, 20 million UK citizens now access their ...
How to install and configure a monitor
Setting up a new display can be fiddly – we explain how to do it Not only are flat-panel LCD monitors cheaper than ever, but they are also bigger, brighter, clearer and easier to use. Replacing ...

Advertisement starts


Advertisement ends


Intel’s new Core vPro starts PC fight-back in the enterprise
Intel brings Sandy Bridge to business systems with features to keep the ...


Dell PowerEdge M-Series Blade Server review
An impressive blade server system that can match anything from HP and ...


Working with windows in Windows
Microsoft Windows is all about – perhaps unsurprisingly – windows. We ...


Faster Windows with fewer visual effects
Fine-tuning the way Windows uses visual effects can improve performance in XP, Vista and 7


Review: Intel Classmate PC
Review: Intel Classmate PC. A classroom computer that's shock-resistant -

Free newsletter

Enter your email address below and receive your Free technology newsletter.


Advertisement starts

Advertisement ends