Date Formula Format Returns #VALUE Upon CELL Value Change

Question

I have a workbook with two spreadsheets.

Spreadsheet 1: "Settings"

Cell D6 contains the following value: 2023. It is named 'CurrentYear'.

Cells D8-D19 contain the following formula: =DATE(CurrentYear,1,1). One for each month. The cell has a custom number format to 'mmmm' so that the full month name is displayed.

Spreadsheet 2: "Attendance"

Cell E7 contains a Data Validation list containing the list of months from Cells D8-D19 on the previous spreadsheet.

Cells E11-AI11 contain the following formula: =UPPER(TEXT(IF(MONTH($E$7+COLUMNS($E11:E$12)-1)=MONTH($E$7),$E$7+COLUMNS($E11:E$12)-1,""),"ddd"))

Cells E12-AI12 contain the following formula: =IF(MONTH($E$7+COLUMNS($E$12:E12)-1)=MONTH($E$7),$E$7+COLUMNS($E$12:E12)-1,"")

If I change the value of E7; the formulas do not work ROW 11 and 12. It return a #VALUE. For some reason, it cannot return the numerical month from E7.

Is there a way to fix the issue as I have only encountered this issue while within TEAMS. If I open the document in the Desktop App, it work fine.

https://docs.google.com/spreadsheets/d/1EfoOH7Qp4jylVzxpZMomHAjz-1GQ3aTK/edit?usp=drive_link&ouid=101049455548997431862&rtpof=true&sd=true

Answer 1

Try a different approach. Clear D8-D19 of 'Settings' and use this formula in D8

=TEXT(DATE(CurrentYear,SEQUENCE(12),1),"mmmm")

Then clear "Attendance" E11:AI12 and in E11 use

=LET(d,DATEVALUE(E7 & " 1, " & CurrentYear),UPPER(TEXT(SEQUENCE(,DAY(EOMONTH(d,0)),d,1),"ddd")))

and in E12

=LET(d,DATEVALUE(E7 & " 1, " & CurrentYear),SEQUENCE(,DAY(EOMONTH(d,0)),d,1))

Answer 2

Bernie,

Thank you for your solution.

I typed your formulas into the spreadsheet I have on TEAMS and they work.

I've never used these formulas before so I'll have to research and learn about them.

Although, any idea why my formulas weren't work? It seems as though our formulas do the same thing.