By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,805 Members | 1,640 Online
Bytes IT Community
+ Ask a Question
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
  1. =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),
  2. (24*(DayEnd-DayStart)*
  3. (MAX(NETWORKDAYS(StartDT+1,EndDt-1,HolidayList),0)+
  4. INT(24*(((EndDt-INT(EndDt))-
  5. (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
  6. MOD(ROUND(((24*(EndDt-INT(EndDt)))-24*DayStart)+
  7. (24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
  8. ROUND((24*(DayEnd-DayStart)),2))))))
  9.  
Thanks!
Feb 15 '08 #1
Share this Question
Share on Google+
3 Replies


Jim Doherty
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

ADezii
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

ADezii
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

Post your reply

Sign in to post your reply or Sign up for a free account.