计算两个日期之间的天数(取决于月份) [英] Calculating number of days between two dates dependent on month

问题描述

我正在尝试创建一个公式，以便可以计算每个客户在酒店住宿的夜晚数.很简单.

I'm trying to create a formula so that I can calculate the number of nights each customers hotel stay was for. Easy enough.

但是，在我的电子表格中，一年中的几个月中我有12列(从C列开始)，其中A列为到达日期，B列为离开日期.

However In my spreadsheet I have 12 columns for months of the year (columns C onwards) with Column A having the arrival date and Column B the departure date.

如果到达日期是2014年6月10日B列中的出发日期为2014年7月13日我需要从C列开始使用公式来计算与该月相关的夜晚/天数，即6月(H列)为21天而7月为12天(因为它们在第13天出发，这不计算在内).其他月份列将不返回任何结果.

If the arrival date was the 10th of June 2014 Departure date in Column B was the 13th of July 2014 I need formulas in Column C onwards to calculate the number of nights/days that are relevant for that month i.e. Junes (column H) would be 21 days and July would be 12 (as they depart on the 13th day this isn't counted). Other month columns would return no result.

推荐答案

为简单起见，而不是C1:N1中的一月"，二月"等，我只输入了数字(1-12)

for simplicitys' sake instead of "january" "february" etc in C1:N1 I have put just numbers (1-12)

我在这里假设开始日期和结束日期都是相同的(如果不是，则需要更多编码，但是您只提到了12个月的列...)

I assume here the YEAR of BOTH the start and finish dates is the same (more coding if not, but you only mentioned 12 month columns...)

我还假设我们正在A2中的开始日期和B2中的完成日期.

I also assume that we are working on start date in A2 and finish date in B2.

C2的公式:

= IF(AND(MONTH($ A2)= C1，MONTH($ B2)= C1)，$ B2- $ A2，IF(MONTH($ A2)= C1,1 + EOMONTH($ A2,0)-$ A2，IF(MONTH($ B2)= C1，$ B2-DATE(YEAR($ B2)，C1,1)，IF(AND(MONTH($ A2)< C1，MONTH($ B2)> C1)，DAYSINMONTH(DATE(YEAR($ A2)，C1,1))，0))))

复制/拖动到D2:N2

copy / drag to D2:N2

说明:

• 如果开始日期和结束日期在同一月份，则只需计算出差异，然后在月份编号匹配的列中使用该差异即可.
• 现在我们知道开始和结束的月份是不一样的.如果开始月份=参考月份，则天数将通过 last_day_of_month-开始日期(第一个晚上为+1)
• 现在我们知道开始月份和结束月份不相同，参考月份也不是开始月份.如果参考月份是最后一个月份，则天数将是最终日期-first_day_of_month
• 如果以上都不是，则参考月份要么完全在我们的日期范围内，要么不在我们的日期范围内.如果start_month<参考月<final_month then days =该月(正确年份)的所有天数

• if the start and finish dates are in the same month, just calculate the difference, use that in the column where month number matches.
• now we know start and finish months are not the same. if the start month = reference month, then number of days will be calculated by last_day_of_month - start date (+1 for the first night)
• now we know the start and finish months are not the same, and reference month is not the start month. If the reference month is the final month, than the number of days will be final_date - first_day_of_month
• if none of the above then the reference month is either wholly within our date range or outside of it. If start_month < reference month < final_month then days = all days in that month (of the correct year)

这篇关于计算两个日期之间的天数(取决于月份)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！