DateDiff Problems in Report

[s1345]

I have a report written that calculates the number of days from today compared to another date. I am having problems figuring out the criteria to successfully omit weekends.

My formula in my Access Report is

=DateDiff('d',[Receipt Date],Now()) where Receipt date is the date field I am pulling from the underlying table and Now() is how i am specifying today.

Any advice on how to write the formula to omit saturdays and sundays?

 

[pbaldy]

You'll have to use a custom function, or there's a formula here:

http://www.pacificdb.com.au/MVP/Code/Dates.htm

Paul
MS Access MVP 2007/2008

 

[RivetHed]

If public holidays will be an issue you might like to check out MichaelRed's FAQ here.

http://www.tek-tips.com/faqs.cfm?pid=181&fid=261

 

[s1345]

I am not a huge technical person but I looked at the formulas with

http://www.pacificdb.com.au/MVP/Code/Dates.htm

and here is what I came up with but isn't working


=DateDiff("d",[Receipt Date],Date())-DateDiff("d",[Receipt Date],Date(),1) * 2 If(Date(), 1) = 7, If([Receipt Date], 1) = 7, 0, 1), If([Receipt Date], 1) = 7, -1, 0))

I am not overally comfortable with custom functions so hoping someone has a suggestion on tweaking my formula to work.

thanks

 

[dhookom]

I would use a custom function. They are not that difficult to implement. If you prefer to use your own complex express, start by changing all "If" to "IIf". There also seems to be a comma or something missing from left of one of your If. That is one of the reasons we create small custom functions.

Duane MS Access MVP

 

[pbaldy]

Frankly I would echo the recommendation of a custom function, as that's what I would do. In any case, you've changed IIf to If, "ww" to "d", etc. If you want to go that way, copy and paste his formula and replace the field names.

Paul
MS Access MVP 2007/2008

 

[dhookom]

Paul,
Welcome to Tek-Tips. Your signature suggests you are a MS Access MVP. I had to check out your profile. Haven't seen you around much. Are you planning on attending the Summit?

You should find your way to the private news groups.

Duane MS Access MVP

 

[pbaldy]

Thanks for the welcome, Duane. Yes, someone left the MVP door open and I was able to sneak through. I'm sure they'll realize their mistake. Yes, I'm attending the Summit, in part because my wife has been wanting to visit Seattle for a awhile, so I can kill two birds with one stone. Are you going?

I read the private newsgroup pretty regularly, but don't post much.

Paul
MS Access MVP 2007/2008

 

[dhookom]

I'm looking forward to meeting you in Seattle. I don't think MS makes mistakes when awarding MVP.

Duane MS Access MVP