hdgarmin
asked on
Calculate elapsed business days using a MySQL query
I need to calculate the number of business days elapsed between date values in StartDT and EndDt. Being able to factor out holidays would be good as well. I've got a decent formula to do this in Excel, but need help to do this in MySQL.
=IF(AND(INT(StartDT)=INT(EndDt),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDt),ROUND(24*(EndDt-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDt-1,HolidayList),0)+
INT(24*(((EndDt-INT(EndDt))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDt-INT(EndDt)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
ASKER
I do reporting on Help Desk tickets and want to show via a MYSQL query how many working/business days (to one digit right of the decimal point) each ticket is open. The first date is the "StartDT" value and the last date is the "EndDT" value. Yes, Sat/Sun are off days. Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm bit confused.. you want number of working days(Business days) between start & end date?... can you please elaborate your requirement??
I've written small function which gives me the business days between any two given two dates(s<e) (taking Sat/Sun as off days).