Google Sheets - create date(YY:MM) by on row formula

1
votes

I have multiple dates from other sheets and could find a first date and last date. Could I use the one-row formula to create a date sequence by firstDate(C2) and lastDate(D2) in google-sheets without app-script?

enter image description here

2
google-sheetsgoogle-sheets-formula
from column A or standalone? - player0
I hope it could create by C2 and D2 - Chen Leo

2 Answers

2
votes

try if C2 and D2 are true/regular dates:

={"Date"; INDEX(UNIQUE(TEXT(ROW(INDIRECT(C2&":"&D2)), "yyyy/mm")))}

enter image description here

if not use:

={"Date"; INDEX(UNIQUE(TEXT(ROW(INDIRECT(
 DATE(LEFT(C2, 4), MID(C2, 5, 2), 1)&":"&
 DATE(LEFT(D2, 4), MID(D2, 5, 2), 1))), "yyyy/mm")))}

enter image description here

0
votes

Another option:

=ArrayFormula(TEXT(EDATE(A2,SEQUENCE(DATEDIF(A2,B2,"M"),1,0,1)),"yyyy/MM"))

enter image description here