Dates to days/months/years

[Ian]

Hi folks,

I have around 40 years worth of data and I need to convert the dates into
days, months and years. Tried several things but they don't seem to work.
I calculated there are 13200 days in total - how can you convert this to
days/months/years?

Big thanks in advance!

Ian

 

[Nikos Yannacopoulos]

Ian,

Do you mean you want to "break down" dates in a column to three separate
columns with year, month and date? If yes, functions:

YEAR(somedate)
MONTH(somedate)
DAY(somedate)

will give you what you want. If you meant something else, please clarify.

HTH,
Nikos

 

[Ian]

Sorry, what I meant was: for example: say I have dates between 1/1/1990 and
1/2/1993, which totals 761 days. How would you translate this into days,
months and years (i.e..e. end up with a result of 2 years, 1 month, 0 days)?

Thanks again in advance!

Ian

 

[Ian]

Ignore the last post, posted with errors!!

Sorry, what I meant was: for example: say I have dates between 1/1/1990 and
1/2/1992, which totals 761 days. How would you translate this into days,
months and years (i.e. end up with a result of 2 years, 1 month, 0 days)?

Thanks again in advance!

Ian

 

[Nikos Yannacopoulos]

Ian,

This is easy if you are willing to go for an approximation, like:

Years: C1 = INT((B1-A1)/365)
Months: D1 = INT(((B1-A1)-C1*365)/30)
Days: E1 = B1-A1-C1*365-D1*30

HTH,
Nikos

If you want to go for accuracy, taking into account leap uears and actual
days in each month, then I'm afraid it will take a custom function in VB.

 

[Ian]

Thanks again. I had thought of that, but I have to be accurate. Do you
think there are any other ways?

Ian

 

[Nikos Yannacopoulos]

It could be done with custom functions in VB but it's quite tricky.

The Years part is easy: take the start dat and start adding one to the year
part and comparing with the end date, until the end date is surpassed. Years
= number of iterations -1. Example code:
Function cYears(StartDate As Date, EndDate As Date)
If StartDate > EndDate Then
cYears = "Error!"
Exit Function
End If
cYears = -1
cTemp = StartDate
Do Until cTemp > EndDate
cTemp = DateSerial(Year(cTemp) + 1, Month(cTemp), Day(cTemp))
cYears = cYears + 1
Loop
End Function

The Days part would always be the simplest one, once the Years and Months is
calculated.

The Months part is the real pain. A similar approach to the Years one would
fail if the start date is, say, Jan-30, because there's no Feb-30, so what
do you do there? You could use an array holding the number of days per month
and consult it in each loop, but how you treat it is a user convention, not
a mathematical or programming issue. I suppose that's why the world goes
with the 12 X 30 convention (thus Excel's built-in function DAYS360()).

Nikos

 

[Myrna Larson]

Use the DATEDIF function, like this:

=DATEDIF(A1,B1,"y") for years
=DATEDIF(A1,B1,"ym") for months in the last partial year
=DATEDIF(A1,B1,"md") for days in the last partial month

 

[Ian]

Yup, that works! Thank you very much!

Ian

Use the DATEDIF function, like this:

=DATEDIF(A1,B1,"y") for years
=DATEDIF(A1,B1,"ym") for months in the last partial year
=DATEDIF(A1,B1,"md") for days in the last partial month