+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    139

    Arrow 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. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    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).
    Last edited by ben_hensel; 09-06-2012 at 04:31 PM.

  3. #3
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    139

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

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

  4. #4
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    139

    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. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    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. #6
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    139

    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. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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")
    Audere est facere

  8. #8
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    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
    Gary's Student

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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