From number of days to Years, months and days DATEDIFF madness!!

[zozew]

Hi Everybody,

I have this great little sheet doing some calculations with VBA and excel formulas. All is well until I decided to convert the resulting days into yers months and days..

=DATEDIF(0,3675,"y") & " years, " & DATEDIF(0,3675,"ym")&" months, " & DATEDIF(0,3675,"md")&" days"

And the result is below...all look great right....NO!!!! its supposed to be 25 days not 22!!!
10 years, 0 months, 22 days
(the 3675 above comes from a cell D35)

I've checked with various calculators on google and the resulting days should be 25

Can anyone please help me keep some of my hair....

Much appreciated

[Phuocam]

The leap years are: 1900,1904,1908 so there are only 22 days, not 25

[mehmetcik]

if you format 0 as a date then excel returns 00/01/1900 which is obviously wrong but it is what it is.

if you format 3675 as a date then excel returns 22/01/1910

So 22 Days, 0 Months and 10 years.



So what were you trying to calculate?

[bebo021999]

If a year suppose to 365 days
3675/365 = 365*10 + 25
gives 10 years and 25 days. Is it are you looking for?

[zozew]

sorry guys totally my mistake i used a solution I found online for the converting of days (as in a date) to years, months and days

that's totally wrong. What I really wanted to use is a formula that takes a number of days (not as in a date from 1900) and then displays a year, month and day result as in below..

now im trying to get the years months and days separated..hmmm any suggestion how to do that..

years is pretty simple I can round that to whole numbers but months/days is trickier...

365/12 = 30.416

3675 days / 30.416 = 120.8246 months

that's 10 full years and 0 months and 0.8246 days

then to get the 0.8246 x 30.416 is 25.08 rounded down to 25 days

my brain is starting to melt slightly...how do I get the months and then all into a formula....? input days then display 10, 0, 25 in a cell

[zozew]

If a year suppose to 365 days
3675/365 = 365*10 + 25
gives 10 years and 25 days. Is it are you looking for?

hahaha yes! I'm pretty daft sometimes.... so how would I get the months separated if lets say its 3775 days

meaning how would I construct a formula that checks for full years and then months and then days...months wold have to be 30 days I guess...or 365/12=30.41667

[mehmetcik]

Ok

Step back a bit. Each Month and Year may have different numbers of years So....

3775 Days After what date?

3775 Days Before what date?



=DATEDIF(TODAY(),TODAY()+3675,"y") & " years, " & DATEDIF(TODAY(),TODAY()+3675,"ym")&" months, " & DATEDIF(TODAY(),TODAY()+3675,"md")&" days"
Returns
10 years, 0 months, 23 days


=DATEDIF(TODAY()-3675,TODAY(),"y") & " years, " & DATEDIF(TODAY()-3675,TODAY(),"ym")&" months, " & DATEDIF(TODAY()-3675,TODAY(),"md")&" days"
Returns
10 years, 0 months, 22 days

[zozew]

Hi,

so the days are not connected to any date...

it could be 65 days or 10657 days...i just want them converted into years months and days something like

8 years, 4 months, 6days

for example:

I have 10657 days how many years months and days is that

10657/365=29 full years and 72 days

72/(365/12)= 2.36712329 so 2 full months and (365/12)*0.36712329 remaining days

(365/12)*0.36712329 = 8.933333334 so that's 9 days rounded up

result is

29 years, 2 months, 9 days

my question is how do I make a formula out of that...

[bebo021999]

It seems 365 days/year, 30 days/month ?

If A1 = 3775

Year =INT(A1/365) = 10
Month = =INT(MOD(A1,365)/30) = 4
Days =MOD(MOD(A1,365),30) = 5

test: 10*365+4*30+5 = 3775

Combine them with "years", "months" and "days"

[zozew]

Great Thanks!

Ill try your formulas

im using 365/12 as days per month

I'm testing this now

year = INT((A1/365))
Month =INT((((A1/365)-TRUNC((A1/365)))*365)/(365/12))
day =ROUND((((((((A1/365)-TRUNC((A1/365)))*365)/(365/12))-TRUNC(((((A1/365)-TRUNC((A1/365)))*365)/(365/12))))))*(365/12),0)

I know it looks ridiculous but it seems to work

[mehmetcik]

Not Possible. Choose a date as a reference. like in your original post you used 0. try using 1.

Then all your calculations will be based on 01/01/1900 and you will get consistent results.

=DATEDIF(1,3675,"y") & " years, " & DATEDIF(1,3675,"ym")&" months, " & DATEDIF(1,3675,"md")&" days"

[zozew]

It seems 365 days/year, 30 days/month ?

If A1 = 3775

Year =INT(A1/365) = 10
Month = =INT(MOD(A1,365)/30) = 4
Days =MOD(MOD(A1,365),30) = 5

test: 10*365+4*30+5 = 3775

Combine them with "years", "months" and "days"

your solution gives exactly the same result as mine...just that yours is much easier to read hahaha thanks

[zozew]

Not Possible. Choose a date as a reference. like in your original post you used 0. try using 1.

Then all your calculations will be based on 01/01/1900 and you will get consistent results.

=DATEDIF(1,3675,"y") & " years, " & DATEDIF(1,3675,"ym")&" months, " & DATEDIF(1,3675,"md")&" days"

hi, thanks for the suggestion but the solution from bebo021999 is the type I was looking for

cheers!

[bebo021999]

zozew, to get number of days, months, years, normally using DATEDIF, with 365/366 days/year, 28,29,30,31 days/months are counted.

In this case, years and months are fixed to 365 days and 12 months/year

Therefore, my solution in #9 is an option.

Your formula in #10 works for years and months, but for days, it does not.
Days is alway interger, if you have to round it, it is not correct.
test with 10657, your formula gives 11 that is not correct. (29*365+2*60+11=10656)

[zozew]

zozew, to get number of days, months, years, normally using DATEDIF, with 365/366 days/year, 28,29,30,31 days/months are counted.

In this case, years and months are fixed to 365 days and 12 months/year

Therefore, my solution in #9 is an option.

Your formula in #10 works for years and months, but for days, it does not.
Days is alway interger, if you have to round it, it is not correct.
test with 10657, your formula gives 11 that is not correct. (29*365+2*60+11=10656)

You are totally correct! but for my purposes this is totally acceptable I am using your formulas changing the months to 365/12 and it works great