excel表格计算年龄_在Excel中计算年龄

excel表格计算年龄

Can you remember how old you are? Or are you like me, and have to ask, "What year is it?" and then subtract your birth year? Or do you like calculating ages in Excel -- let it do the arithmetic for you!

你还记得你几岁吗？ 还是您像我一样，不得不问：“这是哪一年？” 然后减去你的出生年份？ 还是您喜欢在Excel中计算年龄-让它为您做算术！

Excel DATEDIF函数 (Excel DATEDIF Function)

Fortunately, calculating ages in Excel is easy, if you use the handy, but poorly documented, DATEDIF function.

幸运的是，如果使用方便的但记录不充分的DATEDIF函数，则在Excel中计算年龄很容易。

You won't find DATEDIF in Excel's help, and the Microsoft website suggests a different method of calculating date differences. If anyone knows why, I'd love to hear about it.

在Excel的帮助中找不到DATEDIF，Microsoft网站建议使用另一种计算日期差异的方法。 如果有人知道为什么，我很想听听。

UPDATE and WARNING: In some versions of Excel, there is a bug in the DATEDIF function for some intervals. For details, see the article ktDATEDIF Function, which suggests a user defined function as an alternative. (Thanks to Sam and Rick for their comments below.)

更新和警告 ：在某些版本的Excel中， DATEDIF函数中存在一段时间的错误 。 有关详细信息，请参见文章ktDATEDIF Function ，它建议使用用户定义的函数作为替代。 (感谢Sam和Rick在下面的评论。)

创建日期列表 (Create a List of Dates)

It was my son's birthday last weekend, and he was complaining about getting old. Geez, if he's old, what does that make me? (Please don't answer!)

这是我儿子上周末的生日，他一直在抱怨变老。 哎呀，如果他老了，那对我有什么影响？ (请不要回答！)

To keep track of family birthdays and ages, you can create a list in Excel. In one column, enter everyone's birth date. Here's an example, with birth dates created using the RANDBETWEEN function.

为了跟踪家庭的生日和年龄，您可以在Excel中创建一个列表。 在一个栏中，输入每个人的生日。 这是一个示例，其中使用RANDBETWEEN函数创建了出生日期。

=RANDBETWEEN($F$2,$G$2)

= RANDBETWEEN($ F $ 2，$ G $ 2)

使用DATEDIF函数 (Using the DATEDIF Function)

To calculate each person's current age, you can use the DATEDIF function, to compare their birth date with today's date. The DATEDIF has 3 arguments:

要计算每个人的当前年龄，可以使用DATEDIF函数将其生日与今天的日期进行比较。 DATEDIF具有3个参数：

=DATEDIF(Date1, Date2, Interval)

= DATEDIF(日期1，日期2，间隔)

Date1 must be earlier than Date2, or the result will be a #NUM! error. Interval is the unit of time that you want to show the results in, such as:

Date1必须早于Date2，否则结果将为#NUM！ 错误。 时间间隔是您想要显示结果的时间单位，例如：

• y for years
  y年
• m for months
  数月
• yd for calendar days between dates, ignoring the years
  yd表示日期之间的日历天，忽略年份

For example, to find the current age, in years, for the birth date in cell B2, enter this formula in cell C2:

例如，要在单元格B2中查找出生日期的当前年龄(以年为单位)，请在单元格C2中输入以下公式：

=DATEDIF(B2,TODAY(),"y")

= DATEDIF(B2，TODAY()，“ y”)

更改间隔 (Change the Interval)

To see a person's age in months, you can use "m" as the interval, instead of "y".

要查看某人的年龄(以月为单位)，可以使用“ m”作为间隔，而不是“ y”。

=DATEDIF(B3,TODAY(),"m")

= DATEDIF(B3，TODAY()，“ m”)

DATEDIF的问题 (Problems With DATEDIF)

Thanks to Sam and Rick, who commented below. They pointed out that Excel 2007 and Excel 2003 may give incorrect results in some situations, especially for the "yd" and "md" intervals.

感谢Sam和Rick，他们在下面发表了评论。 他们指出， 在某些情况下 ，尤其是对于“ yd”和“ md”间隔，Excel 2007和Excel 2003可能会给出错误的结果 。

You should use the more reliable user defined ktDATEDIF Function to calculate these date differences, or use Microsoft's suggested formula for calculating date differences.

您应该使用更可靠的用户定义的ktDATEDIF函数来计算这些日期差，或者使用Microsoft建议的公式来计算日期差 。

更多DATEDIF信息 (More DATEDIF Information)

Chip Pearson has more information on the DATEDIF function, which he describes as "treated as the drunk cousin of the Formula family." He shows several examples of using DATEDIF, and explains how to use it with leap years.

Chip Pearson拥有有关DATEDIF功能的更多信息，他将其描述为“被视为Formula家族的堂兄”。 他展示了几个使用DATEDIF的示例，并解释了如何在leap年使用它。

Microsoft has DATEDIF details for SharePoint, which looks the same as the missing DATEDIF info for Excel.

Microsoft具有SharePoint的DATEDIF详细信息，与Excel缺少的DATEDIF信息相同。

DATEDIFF功能 (DATEDIFF Function)

And finally, don't confuse the Excel worksheet function, DATEDIF, with the VBA function DATEDIFF, or the Access DATEDIFF function.

最后，不要将Excel工作表函数DATEDIF与VBA函数DATEDIFF或Access DATEDIFF函数混淆。

Although the functions have similar results, the DATEDIFF functions have different arguments, and use different interval settings. For example, "yyyy" is the setting for year, instead of "y". ________________

尽管函数具有相似的结果，但DATEDIFF函数具有不同的参数，并使用不同的间隔设置。 例如，“ yyyy”是年份的设置，而不是“ y”。 ________________

翻译自: https://contexturesblog.com/archives/2010/02/01/calculating-ages-in-excel/

excel表格计算年龄