| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting and error while calculate business hours between 2 dates with holidays.
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 a 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting and error while calculate business hours between 2 dates w
Change your finish time on the EndDte to 18:00 (not 06:00) as this formula
does not work over midnight hour. " wrote: 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 a 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting and error while calculate business hours between 2 dat
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? "Toppers" wrote: Change your finish time on the EndDte to 18:00 (not 06:00) as this formula does not work over midnight hour. " wrote: 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 a 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting and error while calculate business hours between 2 dat
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting and error while calculate business hours between 2 dat
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(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATC H(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD( EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(I NT(StartDateTime),PublicHolidays,0)))*MOD(StartDat eTime,1),WDEnd,WDStart) " wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate business Dates | Excel Discussion (Misc queries) | |||
| Calculating Business Hours Between 2 Dates | Excel Discussion (Misc queries) | |||
| formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
| Is there a way to calculate business working days between dates i. | Excel Worksheet Functions | |||
| Calculate work hours between two dates | Excel Worksheet Functions | |||
Hybrid Mode
