Date Calculations

[tansar]

Hi,
I've been away from Acces for a year of two now and having some difficulty with calculating dates.

I have an 'InstallDate' field, which I would like to work out the difference from todays date. Basically this would tell the user (when opening the DB) the age of the installation.

I hope this make some sense, adn would be grateful if some1 could please assist. Thank you

 

[Corticus]

Hi tansar!

The formula you need will depend on what format you need to see this difference.

In mm/dd/yyyy:

DiffForm: Format(Now()-[installdate],"mm/dd/yy")

in just days:

Diffdays: round(Now()-[installdate],0)

HTH,

 

[tansar]

Hi Corticus,

Thanks for the reply. I've cut n pasted the code but get an error (#Name?) for both the codes.

The format I am after is in years (or could be months).

Any further advice?

TIA

 

[Corticus]

This is all in a query.

Make a new query, add the table that contains the date from which you want to find the difference from today's date, and add the field that contains this date, which I've called 'installdate'.

Go to first blank field while viewing the query in design view, and enter the following to take the difference between the current date and the date contained in the field 'installdate' in months

MonthDiff: 12*(Year(Now()-[installdate])-1900)+Month(Now()-[installdate])-1

Use this expression for the difference in years:

YearDiff: Year(Now()-[installdate])-1900

Here's an example in case you still can't get it to work:

http://www.theillumni.com/posts/datediff.mdb

edit: There are a number of different formulas you can use to get this difference...these will do the job, though.

HTH,

 

[kmacd6951]

How would we calculate the DAYS difference?

I see your examples for Months or Years. Tried the link listed below too, but it has expired. Can you explain how to do Days difference?
I tried typing the DiffDays: round(Now()-[installdate],0)
into the criteria field in Design view, but got an error saying
"The expression you entered has an invalid .(dot) or ! operator or invalid parentheses"

This is all in a query.

Make a new query, add the table that contains the date from which you want to find the difference from today's date, and add the field that contains this date, which I've called 'installdate'.

Go to first blank field while viewing the query in design view, and enter the following to take the difference between the current date and the date contained in the field 'installdate' in months

MonthDiff: 12*(Year(Now()-[installdate])-1900)+Month(Now()-[installdate])-1

Use this expression for the difference in years:

YearDiff: Year(Now()-[installdate])-1900

Here's an example in case you still can't get it to work:

http://www.theillumni.com/posts/datediff.mdb

edit: There are a number of different formulas you can use to get this difference...these will do the job, though.

HTH,

 

[Corticus]

Try the above link now.