Getting and error while calculate business hours between 2 dates with holidays.

[sharadmittal80]

Hi

I am using the formula of "Working Days And Hours Between Two Dates
And Times" from http://www.cpearson.com/excel/DateTimeWS.htm. However,
the numbers I am getting are not correct. I think there is a bug in
the formula.

The values are:
StartDT: 28-Sep-06 12:30 PM
EndDT: 03-Oct-06 6:00 AM
DayStart: 10:00 AM
Dayend: 6:00 PM
Holiday list: 2-Oct-06
Hours: 9.5

The hours actually should be:
28 Sept: 5.5
29 sept: 8
30 Sept and 1 Oct:0 (weekend)
2 oct: holiday
RESULT: 13.5

However, the result is 9.5 hours

Altering the values to:
StartDT 28-Sep-06 12:30 PM
EndDT 29-Sep-06 6:00 AM
DayStart 10:00 AM
Dayend 6:00 PM
Holiday list 2-Oct-06
Hours 1.5

So, the hours got reduced by 8 for the 29th of September. Thus, the
problem seems to be at Startdate and time.

I think this is a bug in the formula. Can you trace it?

Also, if one would like to add Saturdays as working, then how can the
formula be tweaked?

Thanks
Mehta

 

[Guest]

Change your finish time on the EndDte to 18:00 (not 06:00) as this formula
does not work over midnight hour.

 

[Guest]

Note that for EndDT of 03/10/06 06:00 AM business effectively finishes at
02/10/06 18:00: shouldn't the latter be your EndDT value?

 

[sharadmittal80]

Thanks. I tried it but now I am facing a peculiar issue. If the
enddate is 2 oct 2006 6 PM which is a holiday, the formlua considers
it as a working day!

Also, I am actually calculating the work hours we get when a client
gives an asignment to us. The deadline of such assignments are
generally very early in the morning. Any way to calculate in this way.
Example:

StartDT
28-Sep-06 12:30 PM
28-Sep-06 12:30 PM
02-Oct-06 12:00 PM
27-Sep-06 12:00 PM
15-Sep-06 12:00 PM
22-Sep-06 12:00 PM
29-Sep-06 12:00 PM
02-Oct-06 12:00 PM
27-Sep-06 12:00 PM
04-Oct-06 12:00 PM
03-Oct-06 12:00 PM
04-Oct-06 12:30 PM
05-Oct-06 12:00 PM
04-Oct-06 12:00 PM
05-Oct-06 12:00 PM

EndDT
02-Oct-06 6:00 PM
02-Oct-06 5:00 PM
03-Oct-06 3:30 PM
04-Oct-06 2:30 AM
04-Oct-06 2:30 AM
04-Oct-06 2:30 AM
05-Oct-06 1:15 AM
05-Oct-06 2:30 AM
05-Oct-06 11:30 AM
05-Oct-06 1:30 PM
06-Oct-06 2:30 AM
06-Oct-06 12:00 AM
06-Oct-06 2:30 AM
06-Oct-06 8:30 AM
06-Oct-06 2:30 PM


Also, any way to include a saturday in it as working?

I am sorry for taking so much of your time.

Thanks

 

[Guest]

I had another (very quick) look at the formula (and I don't pretend to
completely understand the detail of the logic) and it does ignore the holiday
if it happens to be the end-date.

If you make EndDT 03/10/06 10:00 you will get the correct result.

As for including Saturday as a working day, I am sure I have seen a similar
question and reply on the NGs so perhaps you can search here first.

And finally, I wasn't sure how to interpret your lists of StartDT and EndDT
and the "Any way to calculate in this way." Nor the fact that deadlines are
generally early in the morning as this formula does NOT calculate over
midnight: are hours after 18:00 working hours?

Blelow is from a previous posting so may want to see if you can use this:
the Author was "DaddylongLegs"

This formula will work when StartDateTime and EndDateTime are any time, even
at weekend, evenings etc.


=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATCH(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD(EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(INT(StartDateTime),PublicHolidays,0)))*MOD(StartDateTime,1),WDEnd,WDStart)