Date Diff 2

[Kosmo2]

This Maybe a common question but can anybody give me a line on working out the number of days between 2 work days in a query ?? That is between Monday to Friday on any given date, my fields are :- "Stop Date","Re-Start Date" and number of "Elapsed Days "



Thanks

Mick

 

[SQLBI]

Mick,

To work out the difference between 2 dates, use DateDiff()

DateDiff("d", 1stDate, 2ndDate)

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[Kosmo2]

Thanks Leigh, but does this takin into consideration weekends ??? I need a count of WORK DAYS only . . .



Thanks for the quick response though



Mick

 

[lespaul]

Try FAQ181-261

 

[Kosmo2]

thanks Les, that looks good , however its a little more complex than I envisioned , do you know if there is any quick and easy equation which I can simply use as an arguement within my query without creating new tables and complex things ???

 

[BobJacksonNCI]

Not that common, Mick...
Just look at my simple question - thread702-652802
"If it's Access, it ain't easy"

Just considering Monday through Friday being workdays and not considering holidays the following should get you there. Access considers Sunday as the first (1) day of the week and Saturday as the last (7).
I assume your date range starts with "stop date" and ends with "restart date" - minor adjustment if it's the other way around. And note there's no provision for invalid dates.

Dim n As Long
Dim nbrdays As Integer
Dim Elapsed_Days As Integer
Dim workdate As Date

(Add 1 for inclusive dates)
nbrdays = DateDiff("d", stopdate, restartdate) + 1

For n = 0 To nbrdays
workdate = DateAdd("d", n, stopdate)
If Weekday(workdate) not in (1, 7) Then
Elapsed_Days = Elapsed_Days + 1
End If
Next




HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[Kosmo2]

Thanks Bob , this looks interesting , I think it will suit me fine , YA I guess your right "If it's Access, it ain't easy" , I figured it was a common question and something simple which MS built into Access , I guess I was wrong


Thanks to everybody who gave a reply


Mick