两个日期之间的差异表示为年、月、日(在一栏中) [英] 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屋！