Date Table = VAR BaseCalendar = CALENDAR(DATE(2018,1,1),DATE(2029,12,31))
RETURN GENERATE(BaseCalendar, VAR BaseDate = [Date]
VAR YearDate = Year(BaseDate)
Var MonthNum = MONTH (BaseDate)
VAR MonthName = FORMAT(BaseDate, "mmmm")
VAR YearMonthNum = YearDate * 12 + MonthNum -1
return ROW( "Year", YearDate,
"Month Number", MonthNum,
"Month", MonthName,
"Year Month Number", YearMonthNum
)
)
在A1：H1中输入日期表的表头，然后在A2：H2中分别输入以下公式：
A2=2017-01-01
B2=YEAR(A2)
C2="Q"&LEN(2^MONTH(A2))
D2=RIGHT("0"&MONTH(A2),2)
E2=DAY(A2)
F2=B2&C2
G2=B2*100+D2
H2=WEEKDAY(A2,2)
然后选中A2：H2，向下公式填充到731行，就可得到从2017到2018年的日期表
