448,805 Members | 1,640 Online Need help? Post your question and get tips & solutions from a community of 448,805 IT Pros & Developers. It's quick & easy.

# Convert Excel Formula to Access Formula

 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 non-work hours. What would be the corresponding formula in Access? Expand|Select|Wrap|Line Numbers =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! Feb 15 '08 #1
3 Replies

 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 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! 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 Feb 16 '08 #2

 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 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! You may be able to utilize the Excel Formula Feb 16 '08 #3

 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 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! 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. Feb 16 '08 #4 