☰

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 中輸入日期 1/11/2012，在儲存格 B2 中輸入日期 1/25/2012。將以下程式碼複製到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. 設定列格式A：B例如，根據需要選擇儲存格格式 (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

相關內容