WORK HOURS DIFFERENCE BETWEEN TWO DATES

C

CHRISTI

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
 
M

Mike H

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
 
M

Mike H

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
 
C

CHRISTI

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

Mike H said:
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 said:
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
Click to expand...
Click to expand...
 
M

Mike H

your welcome


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

Mike H said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
K

Kamper

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

Mike H said:
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


CHRISTI said:
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
Click to expand...
Click to expand...
 
R

Rohit

Hi Mike,

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

Regards,
Rohit

Mike H said:
your welcome


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

Mike H said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
D

DAH

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

Mike H said:
your welcome


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

Mike H said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
W

wim.gaethofs

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
 
P

plinius

Il 07/12/2012 12:24, (e-mail address removed) ha scritto:
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
Click to expand...

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

Hi,
E.
 
H

hirpara.ashish.v

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")))
 
G

GS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Work Hours 1
Subtracting time...again 2
Calculate elapsed time excluding specific timeframe 10
Time query 9
Peak call times/hours 2
Calculate the time difference excluding weekends and out of businesshours ( i.e. 8AM - 5 PM) 3
Difference between two date/time values 5
//time calculations 4

Top