☰

Excel - 自动用与特定模式匹配的日期范围填充一行

tag-icon • tag-icon microsoft-excel
Excel - 自动用与特定模式匹配的日期范围填充一行

给定一个特定的开始日期（例如 2012 年 1 月 11 日），我想要自动在 Excel 中填写一行，日期范围与以下天数模式相匹配（双月支付期）。

1. 11th - 25th
2. 26th - 10th

当然，每个单元格中都有实际日期，包括月份和年份（而不是简单地重复上面的两个值）。我正在寻找一种输入一个或两个特定日期范围的方法，例如：

January 11th, 2012 - January 25th, 2012
January 26th, 2012 - February 10th, 2012

并让 Excel 按照相同的模式自动填充更多单元格：

January 11th, 2012 - January 25th, 2012
January 26th, 2012 - February 10th, 2012
February 11th, 2012 - February 25th, 2012
....
December 11th, 2012 - December 25th, 2012

我怎样才能做到这一点？

答案1

将列格式化为您喜欢的日期格式。在单元格 A1 中输入日期 2012 年 1 月 11 日，在单元格 B2 中输入日期 2012 年 1 月 25 日。将以下代码复制到 A2 中；

=DATE(YEAR(B1),MONTH(B1), DAY(B1)+1)

并将其复制到 B2；

=IF(DAY(B1)=10, DATE(YEAR(B1), MONTH(B1), DAY(25)), DATE(YEAR(B1),MONTH(B1)+1, DAY(10)))

继续并突出显示 A2 和 B2，以将它们向下拖动到所需的位置。

完成后应该看起来像这样；

截屏

答案2

这是单行解决方案。警告，这是一个庞大的公式，需要单独的查找表。

您需要在工作簿中的某个位置创建下表：

0   January
1   February
2   March
3   April
4   May
5   June
6   July
7   August
8   September
9   October
10  November
11  December

在我的示例中，此表位于 I1:J12，因此请相应地调整公式。在 A1 中输入：

=VLOOKUP(MOD(ROUNDDOWN((ROW()-0.5)/2,0),12),$I$1:$J$12,2,FALSE) & IF(ISODD(ROW())," 11th, "," 26th, ") & 2012 + INT((ROW()-0.5)/24) & " - " & VLOOKUP(MOD(ROUNDDOWN(ROW()/2,0),12),$I$1:$J$12,2, FALSE) & IF(ISODD(ROW())," 25th, "," 10th, ") & 2012 + INT(ROW()/24)

为了帮助解析这一点，下面按部分分解公式：

=VLOOKUP(MOD(ROUNDDOWN((ROW()-0.5)/2,0),12),$I$1:$J$12,2,FALSE) & 
 IF(ISODD(ROW())," 11th, "," 26th, ") &
 2012 + INT((ROW()-0.5)/24) &
 " - " &
 VLOOKUP(MOD(ROUNDDOWN(ROW()/2,0),12),$I$1:$J$12,2, FALSE) &
 IF(ISODD(ROW())," 25th, "," 10th, ") &
 2012 + INT(ROW()/24)

答案3

无公式方法

  1. 输入A1：11/1/2012
    输入A2：11/2/2012
    强调A1:A2，向下填充（通过鼠标拖动），例如最多A12（11/12/2012）

  2. 输入A13：26/1/2012
    输入A14：26/2/2012
    强调A13：A14，向下填充（通过鼠标拖动），例如最多B24（26/12/2012）

  3. 排序列A这将是期间开始日期

  4. （a）对柱重复上述操作乙这是期限结束日期（每月 25 日和 10 日）
    或者
    （b）将下一个开始日期 - 1 设置为结束日期，即输入B1: =A2-1，然后复制到B2：B23

  5. 格式化列答:按照单元格格式 (Ctrl-1) 中的要求，例如mmmm dd, yyyy

奖金

要在用连字符分隔的单个单元格中显示开始和结束日期：

  1. 输入C1：=TEXT(A1,"mmmm dd, yyyy") & " - " & TEXT(B1, "mmmm dd, yyyy")
    它会给你类似的东西January 11, 2012 - January 25, 2012

  2. 复制到C2:C24

相关内容