Excel formula in Access

[Guest]

I have a really long formula in Excel that I would like to apply in Access,
but I am not sure how it will translate. It basically is a formula that
figures up the days it takes to work a service order based on the application
date and date worked. It takes in to account weekends and holidays. So how
do I get it to work in Access?

 

[Guest]

I would copy the formula to Access and replace the cell references from Excel
with the field names in Access - be it in a query or a an unbound field on a
form.

Thanks

 

[Guest]

I tried that Nathan, but it still didn't work. I keep getting error
messages.
Here is the formula:

=IF(OR(B725=0,F725=0)*AND(A725<>"SL"),"NO APP or
OK",IF(I725=0,"SCHEDULED",IF(B725>=F725,NETWORKDAYS(B725,I725,{37257,37403,37441,37501,37588,37589,37613,37614,37615,37622,37767,37806,37865,37952,37953,37979,37980,38353,38502,38537,38600,38680,38681,38709,38712,38716,38866,38902,38964,39044,39045,39076,39077})-1,NETWORKDAYS(F725,I725,{37257,37403,37441,37501,37588,37589,37613,37614,37615,37622,37767,37806,37865,37952,37953,37979,37980,38353,38502,38537,38600,38680,38681,38709,38712,38716,38866,38902,38964,39044,39045,39076,39077})-1)))

I think some of the holiday 'codes' aren't necessary. Any other suggestions?

 

[Douglas J. Steele]

You may want to translate what you're doing in that function into English,
so that people can offer an alternative...

For instance, all of those numbers correspond to specific dates: do you have
some way of getting those dates from a table, or are you willing to change
the dates each year?

 

[Guest]

Access uses SQL-like syntax.
E.g. the part in Excel with: OR(B725=0, F725=0)
would become: IIf (B725=0 or F725=0, true part, false part)
in Access.

 

[Guest]

Jen - It really isn't an Iif true/false statement, I guess I could make it
that way though. But it is a little more complicated if you look at the rest
of the formula.

Douglas - The formula is in English. The string of numbers represent the
holidays in EDATE form. That is how the formula can recognize them.

What I am trying to accomplish is this: We have a service order, the date
we receive the service order, the date the service gets approved, and the
date the service gets worked.
I want Access to automatically calculate the days it takes to work the
service order based on the date we receive the service order and the date it
gets worked. I need it to take
into account weekends and holidays. Is there another way I can approach
this without even using the formula from Excel?

 

[Douglas J. Steele]

What I had meant by "translate what you're doing in that function into
English" was an explanation of what the function was supposed to be doing in
words. You've sort of done that, but not completely. However, based on what
you're saying, I'd suggest taking a look at the code in
http://www.mvps.org/access/datetime/date0012.htm ("Doing WorkDay Math in
VBA") at "The Access Web"

 

[Guest]

Douglas - Sorry about that. I know your signature says no private e-mails,
but I have to use
'quota' time at work to look at these postings, is there anyway you would
make an exception?
If so you can reach me at (e-mail address removed). Thanks!

Ashley

 

[Douglas J. Steele]

I'd prefer not to. Sending private e-mail, to my mind, defeats the purpose
of the newsgroups. First, it's of less benefit to you, since now you're tied
to me having to check my e-mail, find time to reply, etc., whereas if you
post to the newsgroup, someone else may be able to answer your question
instead of me. As well, if you have a particular problem, odds are someone
else out there has the same problem, and they won't benefit from private
e-mails. Remember, too, that everything posted to the newsgroups gets
archived at services such as http://groups.google.com/advanced_group_search
, so others can benefit for a long time (and perhaps I won't have to answer
the question again <g>).

 

[Ron2005]

What I did for that (because it was not done too frequently)

1) create a table of holidays
2) create a function that
a) started at first day and
if date was not a weekend (datepart function) AND
if date was NOT in the holiday table
added one to a counter

add one to date and go back to a)
b) after the last day was done I had the number of workdays it
took.

ALTERNATIVE


1) create a holiday table that ONLY includes the week/work days that
will really be off (Monday if holiday is on Sunday)
2) the following will tell you how many week days are between two
dates:

wrkDays = DateDiff("d", StartDte, EndDte) - (DateDiff("ww",
StartDte, EndDte, 7) + DateDiff("ww", StartDte, EndDte, 1))

3) Count the number (with dCount or a query) of holidays between the
two dates
4) subtract 3 from 2 and you have it.

Check math.... you may have to add 1 depending on whether the first day
is counted. If it starts today and ends tomorrow is that 1 or 2
workdays? Your decision.

Ron