在 YEAR 计算中包括开始年份和结束年份 [英] Including start year and end year in YEAR calculations

问题描述

对于计算，我试图执行一年 = 一个季节，我拥有的数据是开始年份和结束年份.

For the calculation I'm trying to perform one year = one season and the data I have is start year and end year.

所以 1952 1953 需要加起来 2 (seasons) 但如果我使用 =YEAR(A1)-YEAR(A2) 结果是 0 - 有吗在这些计算中包含开始年份和结束年份作为值的简单方法?

So 1952 1953 needs to add up to 2 (seasons) but if I use =YEAR(A1)-YEAR(A2) the result is 0 - is there a simple way to include the start year and end year as a value in these calculations?

推荐答案

看来您已经发现问题并进行了纠正.为了可能发现此线程的其他人的利益，这就是它的行为方式.

It looks like you have found the problem and rectified it. For the benefit of others who might have found this thread, this is why it was behaving the ways it was.

背景 - Excel(和许多其他应用程序)将 1899 年 12 月 31 日之后的每一天的日期视为 1.今天恰好是 42,079.时间是一天的小数部分，因此 42,079.75 将是 2015 年 3 月 16 日下午 06:00.

Background - Excel (and many other applications) treat dates as 1 for every day past Dec 31, 1899. Today happens to be 42,079. Time is a decimal portion of a day so 42,079.75 would be Mar 16, 2015 06:00 PM.

您在 A1:A2 中将年份作为数字；不是完整的日期.使用 1-per-day 公式，1952 年是 1905 年 5 月 5 日，1953 年是 1905 年 5 月 6 日.如果你用 YEAR() 函数去掉每个年份的年份，你就是从 1905 减去 1905；结果为零.

You had the years as numbers in A1:A2; not as full dates. Using the 1-per-day formula, 1952 is May 5, 1905 and 1953 is May 6, 1905. If you peel out the year of each of those with the YEAR() function, you are subtracting 1905 from 1905; resulting in zero.

解决方案是在 A1:A2 中输入完整日期并将单元格的格式设置为 yyyy 以便它们显示 1952 &1953 但保留其完整日期性质，例如=ABS(YEAR(A1) - YEAR(A2)) + 1 ，或者仅使用年份作为数字并完全丢弃 YEAR() 函数，例如=ABS(A1 - A2) + 1 得到跨越(包括)的季节数.

The solution would be to either type full dates into A1:A2 and format the cells as yyyy so they display 1952 & 1953 but retain their full date nature e.g. =ABS(YEAR(A1) - YEAR(A2)) + 1 , or use the years as numbers only and discard the YEAR() function altogether, e.g. =ABS(A1 - A2) + 1 to get the spanned (inclusive) number of seasons.

这篇关于在 YEAR 计算中包括开始年份和结束年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！