Calculate total number of days between two dates in a query (1 Viewer)

leslijaxin · Feb 5, 2008

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?

Jon K · Feb 6, 2008

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

.

badari · Jan 12, 2009

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

Ritu · Apr 3, 2009

tom_broke said:
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

Ritu · Apr 3, 2009

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 .

rlassalle · Feb 24, 2013

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

Brianwarnock · Feb 24, 2013

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

rlassalle · Feb 25, 2013

Thanks I will try it

Alvin85 · Jul 14, 2018

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.

moke123 · Jul 14, 2018

answered in your other post...https://access-programmers.co.uk/forums/showthread.php?t=300534

TVTV · Dec 18, 2018

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:

pbaldy · Dec 18, 2018

Try

IIf(DateDiff(...) = 0, 1, DateDiff(...))

isladogs · Dec 18, 2018

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

TVTV · Dec 18, 2018

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!!!

TVTV · Dec 18, 2018

PBaldy:
THIS WORKS!!!
IIf(DateDiff("d",[Admit Date],Date()) = 0, 1, DateDiff("d",[Admit Date],Date()))

Thanks so much for the help!!!

TVTV · Dec 18, 2018

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.

isladogs · Dec 18, 2018

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 ...

TVTV · Dec 19, 2018

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!

isladogs · Dec 19, 2018

No problem.
Sorry about my earlier mistake. Perils of copying & pasting

dataadept · Feb 19, 2019

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

Calculate total number of days between two dates in a query (1 Viewer)

New member

Registered User.

New member

New member

New member

New member

Retired

New member

Registered User.

AWF VIP

New member

Wino Moderator

MVP / VIP

New member

New member

New member

MVP / VIP

New member

MVP / VIP

New member

Similar threads

Users who are viewing this thread