# Thread: convert number of days to year, months, days (Access 2K)

1. ## convert number of days to year, months, days (Access 2K)

If I subtract Resignation date from Sworn in date I get a number of days between those two points. How do I convert that to years, months, and days. Thanks Fay

2. ## Re: convert number of days to year, months, days (Access 2K)

The most elegant way to do this is to create VBA functions that hide the nasty details. But you can also create expressions to do this, for use in queries and in the control source of text boxes. Here are the expressions, for fields named Date1 and Date2; replace these by Sworn in date and Resignation date.

Years: Year([Date2])-Year([Date1])+(Month([Date2])<Month([Date1]) Or Month([Date2])=Month([Date1]) And Day([Date2])<Day([Date1]))

Months: (Month([Date2])-Month([Date1])+(Day([Date2])<Day([Date1]))+12) Mod 12

Days: DateDiff("d",DateSerial(Year([Date2]),Month([Date2])+(Day([Date2])<Day([Date1])),Day([Date1])),[Date2])

Phew!

3. ## Re: convert number of days to year, months, days (Access 2K)

Hi Fay,

You can't reliably convert a number of days to an equivalent number of calendar years, months and days, because the answer depends on both the StartDate and EndDate (Sworn and Resignation in your example). You can do an approximation, though, based on a count of the number of days difference between the StartDate and the EndDate (ie EndDate-StartDate=Period), as in the following generic formulae:
Years: =INT((Period+1)/365.25)
Months: =INT(MOD(Period+1,365.25)*12/365.25)
Days: =INT(Period-INT(Years*365.25+Months*365.25/12))
Note: In this approach, the decision on whether to include or exclude the StartDate is taken when the Period is defined. The Period should be reduced by one day if the StartDate is to be excluded. The formula syntax may also be different in Access.

If you want the real number of calendar years, months and days, an approach like the one outlined by Hans is needed, but you'd need to add one day if you want to count both the Sworn and Resignation dates (eg use Sworn date minus 1 day), or subtract one day if you don't want to count either of the Sworn and Resignation dates (eg use Resignation date minus 1 day).

Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•