Convert days -> Years, Months, Days, Hours, Minutes, Seconds

[brharrii]

I'm trying to setup a formula that will convert a number like: 75618.2154 into:

# years, remainder # months, remainder # days, remainder # hours, remainder #minutes, remainder #seconds

I've been working with some code that i found online that effective broke my number into years, months days, but did not calculate hours minutes seconds. I tried to modify it as best I could based on the logic in the formula, but I'm getting an error message that says that I've entered too few arguments for this function. Does anyone know what I've done wrong? Or does anyone know an easier way of going about this?

Thanks!

Please Login or Register  to view this content.

[ben_hensel]

Easier way... just use the built-in excel time functions??? I mean, since it's built as an excel serial number anyway....

YEAR()
MONTH()
DAY()
HOUR()
MINUTE()
SECOND()

Done....

(BTW 75618.2154 is 5:10:11 AM, Jan 12th, 2107; that's 107 years since the excel calender start at midnight on Jan 1 1900. You can display that directly, without any formula, by changing the cell format to date+time).

[brharrii]

Can these all be used in the same cell at the same time?

[brharrii]

Yeah, I don't think this is what I'm looking for at all.

I'm sorry if my explenation was unclear.

I need to take a number of days 542.55 for example. And know how many years that is. In this case it would be 1 year with a remainder. That remainder should be converted to months. The remainder from the months should be converted to days. The ramainder of days should be converted to hours, etc.

[ben_hensel]

A1 = input
B1 = Year(A1) - 1900 & " years, " & Month(A1) & " months, " & day(A1) & " days"

Does that give you what you want for y/m/d?

This might handle leap-years wrong, depending on what your start date is, but that's going to happen no matter what with the data you've presented, so whatever.

What's the relationship between hours-minute-seconds and time?

I mean, since there's 86400 seconds in a day, "0.55 days" marks a period 864 seconds long; that's like, fourteen minutes. So there is a precision issue there.

[brharrii]

hmmm, almost does what I want it to... I pu t in 365 into A1 and it comes up 0 years 12 months 30 days. Not sure what happened. This is what I have:

Please Login or Register  to view this content.

[daddylonglegs]

What result would you expect for your example 542.55 days? I note that 0.55 of a day is 13 hours 12 minutes, so would you expect the hours/mins/seconds part of the output to be 13:12:00? If you do then that means you probably need to assume whole numbers of days in years and months (so you can't use 365.25 as representative of a year for example).

Perhaps try this formula

=DATEDIF(366,366+A1,"y")&" years "&DATEDIF(366,366+A1,"ym")&" months "&DATEDIF(366,366+A1,"md")&" days "&TEXT(A1,"hh:mm:ss")

[Jakobshavn]

In A1 enter a starting date:
1/1/2000
In A2 enter the ending date:
=A1+75618.2154 (this will display Jan 14th 2207)
In A3 enter:
=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days" & TEXT(A2-INT(A2)," HH:MM:SS")
This displays:
207 years, 0 months, 13 days 05:10:11