the date difference in the form of yyyy mm dd

[Starmo]

Hello peoples!


I have two columns, as in the picture. How to make the difference between these two dates in the third column shows that difference as yyyy mm dd.

Thanks!

[ranman256]

You do date math with DATEDIFF.
format is irrelevant since all dates are numeric.

[Starmo]

You do date math with DATEDIFF.
format is irrelevant since all dates are numeric.

I did this for months, but how to show me for example
2 years 8 months 15 days.

[isladogs]

A common reason for doing this is to calculate somebody's age
Ranman is correct about using DateDiff but its not quite as simple as that

Using:

Difference: DateDiff("yyyy",[StartDate],[EndDate]) & " " & DateDiff("m",[StartDate],[EndDate]) & " " & DateDiff("d",[StartDate],[EndDate])

will give you:



Method 2 is better but still unhelpful

Difference: DateDiff("yyyy",[StartDate],[EndDate]) & " " & DateDiff("m",[StartDate],[EndDate]) Mod 12 & " " & DateDiff("d",[StartDate],[EndDate]) Mod 365

Getting the number of days is quite tricky due to leap years

Method 3:

Format(CDate(CLng([EndDate])-CLng([StartDate])),"yyyy mm dd")

This is still unhelpful. Try it & you'll see why
You can fiddle around with this and combine it with method 2
However, I suggest you use code specifically designed for the purpose e.g. http://allenbrowne.com/func-08.html

[Starmo]

Thanks ridders52!
I changed a little your code, which would look like this:
Expr1: DateDiff ("yyyy"; [StartDate]; [EndDate]) & "years" & DateDiff ("m"; [StartDate]; [EndDate]) Mod 12 & "month" & DateDiff ("d" ]; [EndDate]) Mod 365
As you can see the difference is just in: you put "," and I have made a ";"
I have little trouble with the days, I'll show you in the picture I got

[Starmo]

I got this by implementing your code

[Starmo]

Sorry ridders52 in a hurry I did not read your previous post well

[isladogs]

As I said, you could use method 2 for years & months but not days

Method 3 gives the days but the years & months both need adapting!


Method 3 is solvable e.g. by subtracting 1900 from the years, subtract 1 from the months then adjust the days (but needs MUCH more checking!)
There are reasons for the above due to the start date for date calculations in Access (Dec 30 1899 was day zero)

However, a custom function will do it all for you e.g. Allen Browne's function in the link I provided

[Starmo]

Thank you very much ridders 52!