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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, January 16, 2008 at 2:44:01 PM UTC+3, 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)
Where:-
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

Hey there,

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

Thanks,

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)