唔得, 因為喺Data Validation個list入面就假設左係Allow
除非玩VBA
或者用Conditional Formatting將成格變紅色
Sub GenerateDates()
Dim sourceRange() As Variant
Dim sourcerangetemp As String
Dim dateRanges() As String
Dim singleRange() As String
Dim startDate As Long
Dim endDate As Long
Dim currentDate As Long
Dim outputRow As Integer
' 讀取A1單元格的值
outputRow = 1 ' 假設從第1行開始輸出
ReDim sourceRange(1 To Range("A1:A2").Count)
' 將儲存格資料存儲到ARRAY中
For i = 1 To Range("A1:A2").Count
sourceRange(i) = Range("A" & i).Value
Next i
For j = LBound(sourceRange) To UBound(sourceRange)
dateRanges = Split(sourceRange(j), ", ")
' 輸出開始的行號
' 遍歷所有的日期範圍
For i = LBound(dateRanges) To UBound(dateRanges)
' 使用短劃線分隔起始日期和結束日期
singleRange = Split(dateRanges(i), "-")
yearmonth = Left(singleRange(0), 6)
startDate = CLng(singleRange(0))
endDate = CLng(yearmonth & singleRange(1))
' 生成起始日期和結束日期之間的所有日期
For currentDate = startDate To endDate
' 輸出當前日期到單元格中
Cells(outputRow, 3).Value = currentDate '在COLUMN C輸出
' 移動到下一行
outputRow = outputRow + 1
Next currentDate
Next i
Next
End Sub
=LET(
Data,A1,
Comma,SEARCH(",",Data),
SplitA,LEFT(Data,Comma-1),
hyphenA,SEARCH("-",SplitA)-1,
SplitA1,LEFT(SplitA,hyphenA),
LenA,((LEFT(SplitA,6)&RIGHT(SplitA,2))-LEFT(SplitA,hyphenA))+1,
OutputA,SEQUENCE(LenA,1,SplitA1),
SplitB,MID(Data,Comma+2,11),
hyphenB,SEARCH("-",SplitB)-1,
SplitB1,LEFT(SplitB,hyphenB),
LenB,((LEFT(SplitB,6)&RIGHT(SplitB,2))-LEFT(SplitB,hyphenB))+1,
OutputB,SEQUENCE(LenB,1,SplitB1),
TRANSPOSE(VSTACK(OutputA,OutputB)))
=LET(DataRange,A1:A5,FILTER(DataRange,ISNUMBER(SEARCH("A",DataRange))*ISNUMBER(SEARCH("C",DataRange))))