S
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
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