[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

[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)

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

Mike


"CHRISTI" wrote:

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

[Bernd P]

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]

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" wrote:

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

[christi]

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

"Mike H" wrote:

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" wrote:

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]

your welcome


"CHRISTI" wrote:

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

"Mike H" wrote:

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" wrote:

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

[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" wrote:

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)

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

Mike


"CHRISTI" wrote:

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

[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" wrote:

your welcome


"CHRISTI" wrote:

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

"Mike H" wrote:

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" wrote:

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

[DAH]

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

"Mike H" wrote:

your welcome


"CHRISTI" wrote:

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

"Mike H" wrote:

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" wrote:

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

[[email protected]]

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]

Il 07/12/2012 12:24, 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


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

Hi,
E.

[[email protected]]

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[_2_]]

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

[[email protected]]

" 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.

[[email protected]]

On Thursday, April 9, 2015 at 1:35:31 PM UTC+5:30, wrote:
" 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.

=(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