Date difference calculation in Access

I managed to create a formula in Excel that gives the difference in months and days between two dates. (To get years as well you just add in the first position in the parentheses the same formula as for months but replace ym with y)

The advantage of my formula is that it removes the "months" if it is zero, which avoids outputs like: "0 months and 15 days"; instead it will be "15 days".

--> I compared today's date with a future date

formula: =IF(OR(AND(MONTH(TODAY())=MONTH(B3);DAY(TODAY())<day>MONTH(B3);MONTH(TODAY())=(MONTH(B3)-1);DAY(TODAY())>DAY(B3)));(DATEDIF(TODAY();B3;"md")&" jours");(DATEDIF(TODAY();B3;"ym")&" mois et "&DATEDIF(TODAY();B3;"md")&" jours"))

In my formula B3 corresponds to a date greater than today's date

--> If you want to compare a date earlier than today, you would need to make some modifications to the formula.

fab</day>

Hello,
I’m designing a software for managing a daycare.
And I’m having a lot of trouble.
I saw that you were given some tips to calculate the difference between 2 dates.
I need to be able to calculate the age of children from the current date.

I tried with DATEDIFF()
("yyyy", DATE(), DATEOFBIRTH)
But it gives me a ceiling (rounding up) and I don’t know how to modify that rounding....

THANKS IN ADVANCE

Try this:

Ent((Date()-DateOfBirth)/365)
--
Why make life difficult by doing simple things when it's so easy to do complicated things?

Hello I have a class but we no longer have a calculation facility/assessment, this is to ask if we can finalize/finish it please Natacha Bertrand Aurélie Cécile Célà Fannyrosse Gwendolyne thank you

I think the difference should be made in a macro (VBA) and not in a query (it's a bit of a question?)
There is a DateDiff function in VBA (if I recall), but if you are going to explore VBA, consult the help.

a+
dje-dje

There are 10 types of people in the world,
those who understand binary and the others

Here I have the seniority. already a big THANK YOU. Now I have it in days, I need it in years and months. So I divide by 360 for the year and the remainder by 30? 0 or 1? I’m trying because I’m thinking about it as I write. Wow what a strange guy!

mmmba here’s some good ham on the bone

Hello,
I’m looking for someone who can help me with Access.
In a query, I would like to be able to count the rows by Date. The problem is that rows with the same date should count as 1 day in my result, how can I do that?
Thank you for your valuable help.

You start by making a query that extracts just the dates, and on which you perform a grouping so that identical dates appear only once, then a second query that counts the dates from the first one!!!

I managed to create an Excel formula that gives the difference in months and days between two dates. (to also have years, you just add at the first position in the parentheses the same formula as for months but with y instead of ym)

The advantage of my formula is that it removes the "months" part if it equals zero, which avoids outputs like: 0 months and 15 days; instead it will be: 15 days

--> I compared today's date with a future date OF THE SAME YEAR

formula: =IF(OR(AND(MONTH(TODAY())=MONTH(B3);DAY(TODAY())<day>MONTH(B3);MONTH(TODAY())=(MONTH(B3)-1);DAY(TODAY())>DAY(B3)));(DATEDIF(TODAY();B3;"md")" days");(DATEDIF(TODAY();B3;"ym")" months and " & DATEDIF(TODAY();B3;"md")" days"))

In my formula, B3 corresponds to a date later than today

If you want to compare a date earlier than today, you would need to make some modifications to the formula.

fab</day>

here is my dear a software named tempheur which will allow you this calculation it is not bad I tested it

http://www.logitheque.com/logiciels/windows/bureautique/horloges/telecharger/plage_de_temps_10008.htm

=IIf((Month(Date())>Month([Date of birth])) Or (Month(Date())=Month([Date of birth]) And Day(Date())>Day([Date of birth]));DateDiff("yyyy";[Date of birth];Date());DateDiff("yyyy";[Date of birth];Date())-1)

works every time, the only ugly thing is that it displays 20,0 years
but the calculation remains correct

Share:
In Access SQL, to obtain the age of an individual at a given event... and when the time is not needed:

IIf(Mid([DATE_EVENEMENT],6,5) < Mid([Date_Naissance],6,5),
(Year([DATE_EVENEMENT])-Year([Date_Naissance]))-1,
Year([DATE_EVENEMENT])-Year([Date_Naissance]))
AS Age

Or the Canadian French Access version... ;-)
Age:VraiFaux
(
ExtracChaîne([DATE_EVENEMENT];6;5) < ExtracChaîne([Date_Naissance];6;5);
(Année([DATE_EVENEMENT])-Année([Date_Naissance]))-1;
Année([DATE_EVENEMENT])-Année([Date_Naissance])
)