Convert seconds to elapsed time in excel?

Question

I am doing a project that states a number in scientific notation as seconds and I want to convert that to years/days/hours/minutes/seconds. For example, 1.00E+8 is 100 million seconds and that is about 3.17 year. There are precisely 365.2564 days in a year (or 31558152.96 seconds in a year) and I want a format like "(long) years / DDD days / HH hours / MM minutes / SS seconds".

Note that there are no months since using the sidereal time is more precise. Also "long" in the format years can get very long as the exponents increase. I looked all over for this one and while I found some answers it was for durations much less than I intend to use.

garbo7441 · Accepted Answer

I am not at all sure this is what you are looking for, but the following macro will display a message box returning the Years, Days, Hours, Minutes, and Seconds for the value in cell A1.  It will function with numbers up to Excel's 15 digit limit of precision.

Copy the following macro to the clipboard:

Sub Convert_Seconds()
Dim i, intYrs, secYrs, secDays, intDays, secHours, secMinutes, secSeconds
intYrs = Format(Int(Range("A1").Value / 31558152.96), "#,##0")
secYrs = intYrs * 31558152.96
secDays = Range("A1").Value - secYrs
intDays = Int(secDays / 86400)
secHours = Int(((secDays / 86400) - intDays) * 24)
secMinutes = Int(((((secDays / 86400) - intDays) * 24 - _
Int(((secDays / 86400) - intDays) * 24)) * 60))
secSeconds = 60 * ((((secDays / 86400) - intDays) * 24 - _
Int(((secDays / 86400) - intDays) * 24)) * 60) Mod secMinutes
MsgBox Chr(10) & _
intYrs & " Year(s)                                          " & Chr(10) & _
intDays & " Day(s)" & Chr(10) & _
secHours & " Hour(s)" & Chr(10) & _
secMinutes & " Minute(s)" & Chr(10) & _
secSeconds & " Second(s)", vbOKOnly, Range("A1").Value & " Seconds = "
End Sub


Press ALT + F11

In the menus at the top of the VBE, select INSERT > MODULE

Paste the macro into the editing area to the right.

Close the VBE and return to the worksheet.

Press ALT + F8

When the Macros window opens, highlight the macro and click 'Options..'

Enter a letter to be used as a keyboard shortcut and click 'OK'.

Close the Macros window.

Enter a value in A1 and press Ctrl + your shortcut letter.

Note:  I created a formula to do this, but it was rejected by Excel as 'too long'.  It was rather unwieldy, to say the least.

If you wish to return the elapsed time in a cell, please advise and I will revise the macro.

? · Answer

If one cellular has one hundred twenty:00:00 then make your 2d cellular 24*first cellular and convert format to ordinary or style. Excel treats circumstances as fractions of an afternoon. So in case you reformat one hundred twenty:00:00 as ordinary, you will get 5

Trending News

Convert seconds to elapsed time in excel?

2 Answers