3

What I have:

A spreadsheet in which Column C consists of number of days in decimal format.

What I need:

I need Column E to convert the value of Column C to years, months, days, hours, minutes and seconds.

What I've tried:

The following formula works by representing years, days and months but doesn't represent hours, minutes and seconds.

=INT(C9/365)&" Years, "&INT(MOD(C9,365)/30)&" Months, "&ROUND(MOD((MOD(C9,365)),30),0)&" Days"

My question:

Using the above formula, how can I also represent hours, minutes and seconds? I can't make sense of the nesting and rounding.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Clarus Dignus
  • 3,847
  • 3
  • 31
  • 57
  • So you can't just use something like this: `=TEXT(C9,"yyyy-mm-dd hh:mm:ss")`? Or you must have the wording of year, month, day, hour, minute and second? – ian0411 Oct 21 '17 at 01:23
  • @ian0411 - that won't work correctly for years and months, e.g. 20 in C9 would give this result `1900-01-20 00:00:00`.....but you can use it for `hh:mm:ss` appended to the original formula - see my answer – barry houdini Oct 21 '17 at 12:29

3 Answers3

0

A simple way to do this is to use TEXT function to give you the hours, minutes and seconds (along the same lines as ian0411's comment), so using a slightly revised version of the original formula:

=INT(C9/365)&" Years, "&INT(MOD(C9,365)/30)&" Months, "&INT(MOD(MOD(C9,365),30))&" Days "&TEXT(C9,"h:mm:ss")

or if you want to spell out the units you can use this version

=INT(C9/365)&" Years, "&INT(MOD(C9,365)/30)&" Months, "&INT(MOD(MOD(C9,365),30))&" Days"&TEXT(C9," h"" Hours ""m"" Minutes ""s"" Seconds""")

Note that TEXT function is language specific so for non English excel versions h:mm:ss might need to change

barry houdini
  • 45,615
  • 8
  • 63
  • 81
0

Let Excel do the math for you! ...use the TEXT function.

The DATEDIF function (similar to VBA's DateDiff function) is a quick way to calculate whole years, months, days, hours, and more.

It's ideal that you're storing the value as "number of days" (something that should be done whenever possible, since Excel stores it's dates the same way.

The TEXT worksheet function (or the FORMAT function in VBA) can be used to format durations and timespans in the same way it they can format dates and times.

For example:

If cell A1 contains the value you want format, like 12345, (for 12,345 days), you could use:

=TEXT(A1,"y \yr, M \m, d \d")

...which would return:

33 yr, 10 m, 18 d

Specify placement of date/times values with Y,M,D,H,M,S and more. You can specify whatever you want between the numbers; if the letter you want are one of the ones listed above, you can place a \ before to force the letter to display (like the d in the above example.

If your value in A1 included a partial day, like 12345.6789, you could convert it with:

=TEXT(A1,"y \yr, M \m, d \d, h \hr, m \m, s \s")

....to return:

33 yr, 10 m, 18 d, 16 hr, 17 m, 37 s

Undocumented Worksheet Function: DATEDIF

There is an undocumented worksheet function called DATEDIF (similar to VBA's DATEDIFF function) which makes quick work of calculating difference between year, months, days, hours, and more...

"DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation." — Chip Pearson

Usage:

The syntax for DATEDIF is as follows:

=DATEDIF( Date1, Date2, Interval )

Where:
- Date1 is the first date.
- Date2 is the second date.
- Interval is the interval type to return. 

Code  Meaning                         Complete 'calendar' days/months/years        
---- ------------------------------- -------------------------------------------------------------- 
 m    Months                          Months between the dates
 d    Days                            Days between the dates
 y    Years                           Years between the dates
 ym   Months Excluding Years          Months between the dates as if they were of the same year
 yd   Days Excluding Years            Days between the dates as if they were of the same year
 md   Days Excluding Years & Months   Days between the dates as if they were of the same month & year
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
-1

Maybe an more efficient way to do this, but I believe this should work. If it doesn't just leave a comment and we can work it out.

Hours

=INT((C9-INT(C9))*24)

Minutes

=INT((((C9-INT(C9))*24)-INT((C9-INT(C9))*24))*60)

Seconds

=ROUNDDOWN((((((C9-INT(C9))*24)-INT((C9-INT(C9))*24))*60)-INT(((((C9-INT(C9))*24)-INT((C9-INT(C9))*24))*60)))*60,0)

The whole thing

=INT(C9/365)&" Years, "&INT(MOD(C9,365)/30)&" Months, "&ROUND(MOD((MOD(C9,365)),30),0)&" Days,  "&INT((C9-INT(C9))*24)& " Hours,  "&INT((((C9-INT(C9))*24)-INT((C9-INT(C9))*24))*60)&" Minutes, "&ROUNDDOWN((((((C9-INT(C9))*24)-INT((C9-INT(C9))*24))*60)-INT(((((C9-INT(C9))*24)-INT((C9-INT(C9))*24))*60)))*60,0)&" Seconds"

EDIT: ROUNDDOWN instead of ROUND for minutes (can be off a portion of a second) and included the complete formula

David Lee
  • 2,040
  • 17
  • 36
  • I'm having difficulty combining the formulae. This is my resulting formula: https://pastebin.com/TAh2xAW5 however a value of 27.3 converts the value for seconds to 60 seconds. I.e. 0 Years, 0 Months, 27 Days 4 Hours 47 Minutes 60 Seconds – Clarus Dignus Oct 20 '17 at 22:36
  • @ClarusDignus I have updated my answer. Includes a reference fix from your paste bin, changed `ROUND` to `ROUNDDOWN` and added commas, please try out the new formula. – David Lee Oct 20 '17 at 23:00