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 and

 

[zozew]

Datadif is used like =datadif(date1;date2;"y or m or a").
The diference between date 0 (1/1/1900) and date 3675 (22/1/1910) is 3675 days.
I don't know the technical details of this function or the date count for this period. Sorry.
Maybe someone else can help.

Thank you very much! ill keep digging

 

[zozew]

Datadif is used like =datadif(date1;date2;"y or m or a").
The diference between date 0 (1/1/1900) and date 3675 (22/1/1910) is 3675 days.
I don't know the technical details of this function or the date count for this period. Sorry.
Maybe someone else can help.

Ahhhhh i think i know why now....like you mention it treats the 3675 days as a real date when in fact its only a number of days for me..so I really want to convert it from a number of days to y, m and d with no leap years or other strangely date affecting things....

 

[eduzs]

DATEDIF function - Microsoft Support

Calculates the number of days, months, or years between two dates.

support.microsoft.com

See this detail on the page above:

"MD"	The difference between the days in start_date and end_date. The months and years of the dates are ignored. Important: We don't recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.

 

[eduzs]

Ahhhhh i think i know why now....like you mention it treats the 3675 days as a real date when in fact its only a number of days for me..so I really want to convert it from a number of days to y, m and d with no leap years or other strangely date affecting things....

Yes serial date, not number of days.

 

[zozew]

sorry double post cant delete it hehe

 

[mrshl9898]

It's a funny problem. Starting at date 0 here are the days per year. (1900 with the extra day 0 for Sat 0 Jan 1900 for the value 0)

21 days are needed to get to your original 3675.

*original suspicion was leap years started earlier in the century, but that was the 1700's


EDIT: no it's not, thought you had 25 for a sec...

year	days per year
1900​	367​
1901​	365​
1902​	365​
1903​	365​
1904​	366​
1905​	365​
1906​	365​
1907​	365​
1908​	366​
1909​	365​
	3654​	total days
	3675​	plus 21

 

[zozew]

Yes serial date, not number of days.

My simple solution or kinda solution...

I have to divide 365 with 12, use the 30.416 result, and then the remaining number convert that into days rounded up...then it kinda works

so..

3675 days / 30.416 = 120.8246 months

0.8246 x 30.416 = 25.081 so 25 days

10years, 0 months, 25 days

 

[zozew]

It's a funny problem. Starting at date 0 here are the days per year. (1900 with the extra day 0 for Sat 0 Jan 1900 for the value 0)

21 days are needed to get to your original 3675.

*original suspicion was leap years started earlier in the century, but that was the 1700's


EDIT: no it's not, thought you had 25 for a sec...

year	days per year
1900​	367​
1901​	365​
1902​	365​
1903​	365​
1904​	366​
1905​	365​
1906​	365​
1907​	365​
1908​	366​
1909​	365​
	3654​	total days
	3675​	plus 21

yeah you're right...i was looking at it totally wrong....thanks!

 

[zozew]

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...

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....?