Convert Excel Formula to Access Formula

[skiddle]

I've got an Excel formula that calculates the total business hours that a help desk ticket is open. As one would expect, it factors out holidays and non-work hours. What would be the corresponding formula in Access?

=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))))))

Thanks!

[Jim Doherty]

I've got an Excel formula that calculates the total business hours that a help desk ticket is open. As one would expect, it factors out holidays and non-work hours. What would be the corresponding formula in Access?

=IF(AND(INT(Sta rtDT)=INT(EndDt ),NOT(ISNA(MATC H(INT(StartDT), HolidayList,0)) )),0,ABS(IF(INT (StartDT)=INT(E ndDt),ROUND(24* (EndDt-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAY S(StartDT+1,End Dt-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*(DayE nd-DayStart)),2))) )))

Thanks!

Hi Skiddle

Put simply....look at the DateDiff function in Access amongst others (Datepart function, DAggregate Functions...loo k them up in help) to see if it assists you as a startpoint. You can then at least begin to think of how you might rebuild it in Access. As is, there is not a specific builti in function that converts a rather complex looking custom formulae directly and specifically, if that is what you was hoping for.

Jim

[ADezii]

I've got an Excel formula that calculates the total business hours that a help desk ticket is open. As one would expect, it factors out holidays and non-work hours. What would be the corresponding formula in Access?

=IF(AND(INT(Sta rtDT)=INT(EndDt ),NOT(ISNA(MATC H(INT(StartDT), HolidayList,0)) )),0,ABS(IF(INT (StartDT)=INT(E ndDt),ROUND(24* (EndDt-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAY S(StartDT+1,End Dt-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*(DayE nd-DayStart)),2))) )))

Thanks!

You may be able to utilize the Excel Formula

[ADezii]

I've got an Excel formula that calculates the total business hours that a help desk ticket is open. As one would expect, it factors out holidays and non-work hours. What would be the corresponding formula in Access?

=IF(AND(INT(Sta rtDT)=INT(EndDt ),NOT(ISNA(MATC H(INT(StartDT), HolidayList,0)) )),0,ABS(IF(INT (StartDT)=INT(E ndDt),ROUND(24* (EndDt-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAY S(StartDT+1,End Dt-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*(DayE nd-DayStart)),2))) )))

Thanks!

You may be able to utilize the Excel Formula within the context of Excel and pass to it Access data via Automation code. This may/may not be difficult depending on the specifics.