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

1. ## Convert days -> Years, Months, Days, Hours, Minutes, Seconds

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

2. ## Re: Convert days -> Years, Months, Days, Hours, Minutes, Seconds

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

3. ## Re: Convert days -> Years, Months, Days, Hours, Minutes, Seconds

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

4. ## Re: Convert days -> Years, Months, Days, Hours, Minutes, Seconds

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.

5. ## Re: Convert days -> Years, Months, Days, Hours, Minutes, Seconds

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.

6. ## Re: Convert days -> Years, Months, Days, Hours, Minutes, Seconds

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

7. ## Re: Convert days -> Years, Months, Days, Hours, Minutes, Seconds

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")

8. ## Re: Convert days -> Years, Months, Days, Hours, Minutes, Seconds

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

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1