将十进制天数转换为年，月，日，小时，分钟和秒

What I have:

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

一个电子表格,其中C列包含十进制格式的天数。

What I need:

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

我需要E列将C列的值转换为年,月,日,小时,分钟和秒。

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.

使用上面的公式,我怎样才能表示小时,分钟和秒?我无法理解嵌套和舍入。

3 个解决方案

#1

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.

DATEDIF函数(类似于VBA的DateDiff函数)是计算整年,月,日,小时等的快速方法。

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.

理想的情况是,您将值存储为“天数”(应尽可能完成的事情,因为Excel以相同的方式存储它的日期)。

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.

TEXT工作表函数(或VBA中的FORMAT函数)可用于格式化持续时间和时间跨度,其格式与日期和时间格式相同。

For example:

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

如果单元格A1包含您想要格式化的值,例如12345(12,345天),您可以使用:

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

使用Y,M,D,H,M,S等指定日期/时间值的位置。你可以在数字之间指定你想要的任何东西;如果您想要的字母是上面列出的字母之一,您可以放置​​\之前强制显示字母(如上例中的d)。

---

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

如果A1中的值包含部分日期,如12345.6789,则可以将其转换为:

=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的无证工作表函数(类似于VBA的DATEDIFF函数),它可以快速计算年,月,日,小时等之间的差异......

---

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

“DATEDIF被视为Formula家族的醉酒表亲.Excel知道它过着幸福和有用的生活,但在礼貌的谈话中不会谈论它。” - Chip Pearson

Usage:

The syntax for DATEDIF is as follows:

DATEDIF的语法如下:

=DATEDIF( Date1, Date2, Interval )

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

其中: - Date1是第一个日期。 - Date2是第二个日期。 - Interval是要返回的间隔类型。

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

---

• More information available on Chip Pearson's page.

有关Chip Pearson页面的更多信息。

#2

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:

一个简单的方法是使用TEXT函数为您提供小时,分钟和秒(与ian0411的注释相同),因此使用原始公式的略微修改版本:

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

= INT(C9 / 365)&“年”,&INT(MOD(C9,365)/ 30)&“月”,&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""")

= INT(C9 / 365)&“年”,&INT(MOD(C9,365)/ 30)&“月”,&INT(MOD(MOD(C9,365),30))&“Days”&TEXT(C9, “h”“小时”“m”“分钟”“s”“秒”“”

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

请注意,TEXT函数是特定于语言的,因此对于非英语excel版本h:mm:ss可能需要更改

#3

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

编辑:ROUNDDOWN代替ROUND几分钟(可以在一秒钟的时间内完成)并包括完整的公式