P: 1

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 nonwork 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*(EndDtStartDT),2),

(24*(DayEndDayStart)*

(MAX(NETWORKDAYS(StartDT+1,EndDt1,HolidayList),0)+

INT(24*(((EndDtINT(EndDt))

(StartDTINT(StartDT)))+(DayEndDayStart))/(24*(DayEndDayStart))))+

MOD(ROUND(((24*(EndDtINT(EndDt)))24*DayStart)+

(24*DayEnd(24*(StartDTINT(StartDT)))),2),

ROUND((24*(DayEndDayStart)),2))))))

Thanks!
 
Share this Question
Expert 100+
P: 897

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 nonwork 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*(EndDtStartDT),2),
(24*(DayEndDayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDt1,HolidayList),0)+
INT(24*(((EndDtINT(EndDt))
(StartDTINT(StartDT)))+(DayEndDayStart))/(24*(DayEndDayStart))))+
MOD(ROUND(((24*(EndDtINT(EndDt)))24*DayStart)+
(24*DayEnd(24*(StartDTINT(StartDT)))),2),
ROUND((24*(DayEndDayStart)),2))))))
Thanks!
Hi Skiddle
Put simply....look at the DateDiff function in Access amongst others ( Datepart function, DAggregate Functions...look 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
  Expert 5K+
P: 8,675

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 nonwork 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*(EndDtStartDT),2),
(24*(DayEndDayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDt1,HolidayList),0)+
INT(24*(((EndDtINT(EndDt))
(StartDTINT(StartDT)))+(DayEndDayStart))/(24*(DayEndDayStart))))+
MOD(ROUND(((24*(EndDtINT(EndDt)))24*DayStart)+
(24*DayEnd(24*(StartDTINT(StartDT)))),2),
ROUND((24*(DayEndDayStart)),2))))))
Thanks!
You may be able to utilize the Excel Formula
  Expert 5K+
P: 8,675

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 nonwork 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*(EndDtStartDT),2),
(24*(DayEndDayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDt1,HolidayList),0)+
INT(24*(((EndDtINT(EndDt))
(StartDTINT(StartDT)))+(DayEndDayStart))/(24*(DayEndDayStart))))+
MOD(ROUND(((24*(EndDtINT(EndDt)))24*DayStart)+
(24*DayEnd(24*(StartDTINT(StartDT)))),2),
ROUND((24*(DayEndDayStart)),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.
    Question stats  viewed: 3631
 replies: 3
 date asked: Feb 15 '08
