在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个(季节)，但是如果我使用=YEAR(A1)-YEAR(A2)，结果是0-是否有一种简单的方法在这些计算中将开始年和结束年作为值包括在内? /p>

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次公式，1952年是 1905年5月5日，而1953年是 1905年5月6日.如果使用YEAR()函数剥离每个年份的年份，则将从1905中减去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屋！