Calculate total number of days between two dates in a query

  • Thread starter Thread starter tom_broke
  • Start date Start date
Calculating total number of working days between two dates

In Excel, the formula is:
networkdays(start_date,end_date)

I want to calculate the difference between two dates in Access, but have it return the number of "working days" only (Monday-Friday).

Can someone help?
 
NumberOfWorkingDays:
[EndDate]-[StartDate]+1
-(DateDiff("ww",[StartDate],[EndDate],7)-(Weekday([StartDate])=7))
-(DateDiff("ww",[StartDate],[EndDate],1)-(Weekday([StartDate])=1))

.
 
hi,

i have two date columns in my database like datefrom and dateend
i want to compare the two date columns using the between Query
if datefrom="21/5/08" and dateto ="26/5/08"
if user try to enter into in between these days like "24/5/08"
it should say that the dates are booked
 
Hello all,

I have a bit of a challenge here, I want to calculate the total number of days between two dates, but I want to show this total everytime we run the query. One field is DateReceived, the other is DateClosed, I would like to be able to generate a third column within the query that performs the calculation for this two values.

Thank you for your assistance...
Reply as soon as possible
 
There are two fields date_from & date_when in the table attendance_staff.Both having date data type.How calculate number of days between date_from & date_when .
 
I try these sentences = DateDiff("d",[appdate],now()) and I got compilation error the same happen with the function Left(name,1) to get the inictial of the name. I do know if something in the compiler of my access 2007. the date in the field appdate is a text field with this format 20120525. do i have to convert it? I tried ctod(appdate) but always i got a compile error. I am working in designe view. Also i tried DateDiff("m",#12/24/2000#,#11/26/2000#) and also it does not work.

Thanks for your help
Thanks for your help
 
This is a very old thread with a lot of posts, you would be better starting a new thread especially as your problem may include a missing reference, I do not have 2007 so will skip that and point out a few things.

1 yes the appdate must be a date so you do need to convert, I prefer to use Dateserial but others use CDate, read both in help or Google.
2 name is an Access reserved word so that may be the problem with the Left example.
3 in your hard coded dates in the second Datediff example the lesser date should be first.
4 if you are not interested in times only dates use Date() rather than Now()

Brian
 
May i ask how do you count the number days in each month? Example: I have a textbox name txtDate for the user to key in the date. I want to find out the total number of days in that month of the date in which the user had keyed.
 
Hello, New to AccessWorld here.. I have searched forever on the 'Net - I need a simple solution that if the function =0, then change it to a 1. Without posting all my "IIf" trial & error madness here, I hope someone can please help provide me with an answer: (DateDiff("d",[Admit Date],Date()) - Thanks! :) :banghead:
 
Try

IIf(DateDiff(...) = 0, 1, DateDiff(...))
 
It would have been better to start a new thread.
You haven't said what you want to happen when the difference isn't zero....

Something like this
Code:
=IIf(DateDiff("d",[Admit Date],Date())=0,1,some other value)

Or more simply
Code:
=IIf([Admit Date]=Date(),1,some other value)

UPDATE: I've just corrected an error in the second solution
 
Last edited:
PBaldy:
THIS WORKS!!!
IIf(DateDiff("d",[Admit Date],Date()) = 0, 1, DateDiff("d",[Admit Date],Date()))

isladogs: 1. =IIf(DateDiff("d",[Admit Date],Date())=0,1,some other value),

IIf(DateDiff("d",[Admit Date],Date()) = 0, 1, DateDiff("d",[Admit Date],Date()))

THIS WORKS! It is the same as PBaldy's: Changed "some other value" to DateDiff("d",[Admit Date],Date())

Errors below - What am I missing here - I'd still like to know what I am doing wrong:

2.A. =IIf([Admit Date]=Date())=0,1,some other value)
IIf(([Admit Date],Date())=0,1,DateDiff("d",[Admit Date],Date()))
Result: Syntax Error (comma) in query expression
2.B. IIf([Admit Date],Date())=0,1,DateDiff([Admit Date],Date())
IIf([Admit Date],Date())=0,1,DateDiff([Admit Date],Date())
Result: Expression you entered contains invalid syntax, or you need to enclose you text data in quotes
2.C. IIf(([Admit Date],Date())=0,1,DateDiff([Admit Date],Date())
Result: Expression you entered has a function containing the wrong number of arguments.

You guys are awesome! Thanks for the Fast Help!!! :)
 
PBaldy:
THIS WORKS!!!
IIf(DateDiff("d",[Admit Date],Date()) = 0, 1, DateDiff("d",[Admit Date],Date()))

Thanks so much for the help!!! :)
 
isladogs: 1. =IIf(DateDiff("d",[Admit Date],Date())=0,1,some other value),

IIf(DateDiff("d",[Admit Date],Date()) = 0, 1, DateDiff("d",[Admit Date],Date()))

THIS WORKS! It is the same as PBaldy's: Changed "some other value" to DateDiff("d",[Admit Date],Date())

Errors below - What am I missing here - I'd still like to know what I am doing wrong:

2.A. =IIf([Admit Date]=Date())=0,1,some other value)
IIf(([Admit Date],Date())=0,1,DateDiff("d",[Admit Date],Date()))
Result: Syntax Error (comma) in query expression
2.B. IIf([Admit Date],Date())=0,1,DateDiff([Admit Date],Date())
IIf([Admit Date],Date())=0,1,DateDiff([Admit Date],Date())
Result: Expression you entered contains invalid syntax, or you need to enclose you text data in quotes
2.C. IIf(([Admit Date],Date())=0,1,DateDiff([Admit Date],Date())
Result: Expression you entered has a function containing the wrong number of arguments.

Thanks for your help! And I'd still like to know my errors, as I do like the simpler coding efforts. :)
 
My first solution was indeed the same as Paul's. We answered at the same time

My 2nd solution is simpler as it doesn't use DateDiff for the first part
NOTE: Sorry - it had an error originally - now corrected below:
Code:
=IIf([Admit Date]=Date(),1,DateDiff("d",[Admit Date],Date()))
That should now work

As for your 2A/B/C, each of those has the same error
You've omitted the DateDiff("d" section at the start but not used my intended method
In B/C you've also omitted the "d" section in the second part ...
 
Last edited:
Hello,

Yes, that works! I noticed there was an extra parenthesis ")" & "=0" in the first post of the "Date())=0" function... I'll go with the simpler code for now, and adding both functions to my Notes.

Thanks again! Thumbs Up!
 
No problem.
Sorry about my earlier mistake. Perils of copying & pasting
 
Please Help Me Experts

Date1: 22/06/2014
Date2: 12/01/2019
Answer= 4 Years 6 Months 19 Days
it is possible in Access Query

excel formula =DATEDIF(A2,C2,"y")&" Years "&DATEDIF(A2,C2,"ym")&" Months "&DATEDIF(A2,C2,"md")&" Days "

i need it in access query

Thanks in advance
 

Users who are viewing this thread

Back
Top Bottom