Convert days in to years,months,days (1 Viewer)

[jlocke]

I need help on how i can convert 1184 days to years, months, days. thanks

 

[neileg]

It may seem strange but you need to define a year and a month in terms of number of days. Leap years have 366 days, not 365, so are you going to account for every fourth year having an extra day? How many days should a month have? 28, 29, 30 or 31?

 

[jlocke]

date conversion

i'm not really worried about that to much i would just assume 365 days in a year.

 

[neileg]

So divide by 365 and take the integer part of the answer. You could use the Mod operator to do this. Then subtract the whole number of years and divide by 30 to get the months. Whatever is left is the number of days.

 

[jlocke]

thank you

i knew that.... i've used that for time calc. just didn't remember. thank you

 

[ColinEssex]

It may be slightly more accurate if you assume a year is 365.25 days

Col

 

[sargon]

Hi,

I have this function to convert from days to years, months, days:

Function YMD(No As Integer) As Variant
Dim Y As Long
Dim M As Long
Dim D As Long

Y = M / 365.25
M = (No - Y * 365.25) / 30.4375
D = No - (Y * 365.25) - (M * 30.4375)

YMD = Y & " years " & M & " months " & D & " days"
End Function

But the problem is:

?YMD(55)
0 years 2 months -6 days

Is not what I want , the result must be:
0 years 1 months 24 days

Can you, please, tell me what is wrong?

 

[missinglinq]

jlocke and Sargon, here's two functions for you, depending on what degree of accuracy you're willing to accept. Of course, any way you do it is going to suffer from some inaccuracy because all years aren't 365 days long and all months are not 30 days long.

Function YMD(No As Integer) As Variant
Dim Y As Long
Dim M As Long
Dim D As Long

  Y = Int(No / 365.25)
  M = Int((No - (Int(No / 365.25) * 365.25)) / 30.4375)
  D = No - ((Y * 365.25) + (M * 30.4375))

  YMD = Y & " years " & M & " months " & D & " days"
End Function

Function D2YMD(No As Integer) As Variant
Dim Y As Long
Dim M As Long
Dim D As Long

  Y = Int(No / 365)
  M = Int((No - (Int(No / 365) * 365)) / 30)
  D = No - ((Y * 365) + (M * 30))

  D2YMD = Y & " years " & M & " months " & D & " days"
End Function

 

[sargon]

Thanks, it works fine!

 

[missinglinq]

Glad it works for you! It was fun figuring out!

Linq

 

[The_Doc_Man]

Be VERY careful with those functions. You are inviting truncation because your input involves fractional numbers in division without explicit typecasts. Yes, you want integers as your Y/M/D result but you might have some oddball issues crop up.

Now... here is a crazy thought for you.

Take that input integer and use it as CDate(CDbl(NO))

Then format it as a short date. It will come out as something like 01/24/00

So separate the parts between slashes to get 00 years 1 month 24 days.

For SMALL values of NO, this might be off just a bit - but for 1184 days, you will get years, months, and days on a consistent scale. The larger NO happens to be, the better this gets as it takes leap days into account.

The thing is that technically (and this is ONLY for you purists out there), Neileg is spot-on. If you don't have a start date or an end date to put that number into context, it has no "real" meaning for conversion purposes. Out of context, X number of days is just X number of days. No matter HOW big it is, it is NO years and NO months. Because (thanks to Julius Caesar and Pope Gregory) our calendar is irregular. Without that reference point, TECHNICALLY your desired answer floats a couple of days either way.

Now, if you only wanted an approximation, go for it as noted. But be aware that the answer has about a 57% chance of being at least one day off on ANY arbitrary number of days and up to 3 days off for N less than 4 years.

PARTICULARLY if this triplet of <years,months,days> will be used in ANY OTHER computation, do the computation in days. If the result is an end in itself and is only being forwarded to management because they don't understand numbers of days greater than 30, you might get away with it - as long as they don't start marking things down on calendars.

 

[missinglinq]

Tried you hack, entered 55 days, but didn't get 01/24/00, got 2/23/00 which continuing your hack, would yield 1 month too many! Tried 1184 as well, with the same results; the results would provide a value for months that was 1 too many! I suppose you could simply parse the month value out of the "date" and then manipulate it so that you could subtract 1 from the result.

And obviously, because of the factors already listed above, this is not going to be dead on accurate.