以年，月，日表示的两个日期之间的差异(在一列中) [英] Difference between two dates expressed as years, months, days (in one column)

问题描述

我在Google表格中设置了一组列，这些列使用 DATEDIF 函数显示两个日期之间的时差:

I have a set of columns set up in Google Sheets that display the difference between two dates using the DATEDIF function:

=DATEDIF (AS2, TODAY(), "D") 

(自某个日期以来今天过去的天数)

(number of days passed today since a certain date)

=DATEDIF (AR11, AS11, "D")

(两个特定日期之间经过的天数)

(number of days passed between two certain dates)

这些值表示为一个数字(以天为单位).这对于较短的持续时间是可以的，但是对于987天之类的时间，我想将值显示为更直观的内容，例如:

The values are represented as a number (of days). This is fine for shorter durations, but for something like 987 days I would like to display the value into something more intuitive, such as:

| 2 Years, 8 months, 17 days | 

如果在同一列中不可能做到这一点，那么我至少希望有一组三列，以三种不同的值类型显示此持续时间:

If that is not possible within the same column, I would at least like to have a set of three columns that display this time duration in three separate value types:

| 2 Years | 8 months | 17 days |

仅更改每列的值类型(例如，从几天到几个月)就足够简单了，但是我不确定如何继续保持相互之间显示的值(而不是只是在不同的持续时间类型中显示相同的值.

Just changing the value type for each column (from days to months for example) would, of course, be simple enough, but I'm not sure how to proceed in keeping the values displayed in relation to each other (and not just have the same value be displayed in different duration types).

有什么建议吗?

推荐答案

=IF(DATEDIF(A1, B1, "D")>365, QUOTIENT(DATEDIF(A1, B1, "D"), 365)&" year(s) "&
                          QUOTIENT(MOD(DATEDIF(A1, B1, "D"), 365), 30)&" month(s) "&
                      MOD(QUOTIENT(MOD(DATEDIF(A1, B1, "D"), 365), 30), 30)&" day(s)",
 IF(DATEDIF(A1, B1, "D")>30,  QUOTIENT(DATEDIF(A1, B1, "D"), 30)&" month(s) "&
                                   MOD(DATEDIF(A1, B1, "D"), 30)&" day(s)", 
                                       DATEDIF(A1, B1, "D")&" day(s)"))

这篇关于以年，月，日表示的两个日期之间的差异(在一列中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！