WORK HOURS DIFFERENCE BETWEEN TWO DATES

CHRISTI

unread,

Jan 16, 2008, 6:21:01 AM1/16/08

to

I need to calculate the total WORK-hours (08:00-17:00) between two
date/time-stamps;
-excluding WEEKENDS
-excluding PUBLIC HOLIDAYS

eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 11-01-2008 09:00:00
A2 11-01-2008 11:00:00
A3 02:00

eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 11-01-2008 09:00:00
A2 14-01-2008 11:00:00
A3 11:00

eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 14-01-2008 09:00:00
A2 16-01-2008 11:00:00
A3 20:00

Mike H

unread,

Jan 16, 2008, 6:44:01 AM1/16/08

to

Hi,

Try this

=(NETWORKDAYS(A1,A2)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1),B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)

Where:-
A1= Earlier date/time
A2= Later date/time
B1 = 08:00
B2 = 17:00

Mike

Bernd P

unread,

Jan 16, 2008, 7:26:14 AM1/16/08

to

Hello,

maybe
http://www.sulprobil.com/html/count_hours.html
can help you somewhat. It does not take holidays into account, though.

Regards,
Bernd

Mike H

unread,

Jan 16, 2008, 8:04:00 AM1/16/08

to

Hi,

Your other response made me aware that I'd missed publich holidays so change
to this

=(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1),B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)

Where C1 to C8 is your holiday list. Just a point to note is that I have
assumed that a public holiday wouldn't be either of the dates in A1 or A2. If
these dates were public holidays then the real start/end date is a day
later/earlier and in any case I'm struggling to work it out otherwise.
Perhaps someone can enlighten us.

Mike

CHRISTI

unread,

Jan 16, 2008, 9:02:02 AM1/16/08

to

Mike - thank you this solved all; would never have figured this out by myself!

Mike H

unread,

Jan 16, 2008, 9:05:00 AM1/16/08

to

your welcome

Kamper

unread,

Jan 29, 2008, 4:43:01 PM1/29/08

to

Mike -
I tried this, but had to change the cell references, but that did not work.
Below is my example. Do you see what I'm doing wrong?

Here is my formula:
=(NETWORKDAYS(A2,B2)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),D2,C2)

A2 = 1/16/2008 10:00 am
B2 = 1/22/2008 9:34 am
C2 = 08:00 am
D2 = 17:00 pm

My results are showing 1.48

Thanks,
Kamp

Rohit

unread,

Oct 14, 2008, 5:49:02 AM10/14/08

to

Hi Mike,

From many days I am working on this, but not able solve this issue. Thanks
for your help...

Regards,
Rohit

DAH

unread,

Jan 8, 2009, 10:31:00 PM1/8/09

to

I have to thank you too because I was looking for this same answer!!! You're
awesome! ~Dee

wim.ga...@gmail.com

unread,

Dec 7, 2012, 6:24:15 AM12/7/12

to

This formula calculates te total work hours and minutes between two dates.
I use it to calculate how long it takes for someone to answer my mails at work.
One workday is 10 hours in this formula. You can change this.

B3(mail send), format as d/mm/yyyy h:mm , 23/02/2012 09:00
G3(reply received), format as d/mm/yyyy h:mm , 28/02/2012 17:00

H3(hours and minutes, format as h:mm):

=((((((NETWORKDAYS(B3;G3))-1)*10)+(IF(HOUR(B3)<HOUR(G3);HOUR(G3)-HOUR(B3);-(HOUR(B3)-HOUR(G3)))))*60)+(IF(MINUTE(B3)<MINUTE(G3);MINUTE(G3)-MINUTE(B3);(60-(MINUTE(B3)-MINUTE(G3)))-60)))/1440

plinius

unread,

Dec 7, 2012, 10:22:14 AM12/7/12

to

slim mode:
=(NETWORKDAYS(B3,G3)-1)*10/24+(MOD(G3,1)-MOD(B3,1))

Hi,
E.

hirpara....@gmail.com

unread,

Mar 25, 2013, 7:05:13 AM3/25/13

to

Assumption is Start time as 7:30 AM and End time as 5:30 PM for the consideration of Working HRS.

A1=Start Date and Time
B1=End Date and Time

The formula is:

=IF((B1-A1)<=1,TEXT(MOD(A1,60),"hh:mm")-TEXT(0.3125,"hh:mm")+TEXT(0.729166666666667,"hh:mm")-TEXT(MOD(B1,60),"hh:mm"),(((NETWORKDAYS(A1,B1,E1)+1)/24)*10)-(TEXT(MOD(A1,60),"hh:mm")-TEXT(0.3125,"hh:mm")+TEXT(0.729166666666667,"hh:mm")-TEXT(MOD(B1,60),"hh:mm")))

GS

unread,

Mar 25, 2013, 10:51:36 AM3/25/13

to

This formula also accounts for times that go past midnight (ie: 11pm to
7am)...

=IF(AND(Start<>"",Stop<>""),ROUND(MOD(Stop-Start),1)*24,2),"")

..where 'Start' and 'Stop' are column-absolute,row-relative defined
ranges with local scope. The cell remains empty until both Start/Stop
have time values entered.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

benwi...@googlemail.com

unread,

Apr 9, 2015, 4:05:31 AM4/9/15

to

" If these dates were public holidays then the real start/end date is a day
later/earlier and in any case I'm struggling to work it out otherwise".

Mike's formula is great, but having issue when start date is a public holiday, does anyone know how to resolve this?

Thank you.

dm...@khatuwala.net

unread,

Mar 18, 2020, 4:16:29 AM3/18/20

to

=(NETWORKDAYS.INTL(B5,C5,11)-2)*(B2-B1)+(B2-MOD(B5,1))+(MOD(C5,1)-B1)
use this formula to clear your query if you need to add office holidays then
add Holiday list in networkdays formula

Sarah Saleh

unread,

Aug 31, 2022, 5:35:06 AM8/31/22

to

Hey there,

It seems that this method is not working anymore, do you have another solution?

Thanks,