WEEKDAY returning wrong day

Hi, I'm new to this so bear with me. I have created a new absence worksheet from the Employee Absence Schedule Template. One of the reasons for this is that I work for a school and our academic year starts in September and ends in July. In addition I wanted to localise the terminology and add additional absence codes. I have some Excel experience - worked for Microsoft many years ago - but don't hold that against me . I have searched this forum and found that others have experienced similar problems with the weekday formula returning the wrong day for the date of the month.

My attached worksheet will show that I am using the following formula =TEXT(WEEKDAY(DATE(CalendarYear,9,COLUMNS($C$5:AG5)),1),"ddd") which for September 2019 (the year in CalendarYear, returns the 1st as being a Tuesday, whereas it actually was a Sunday. The workbook at present only has September, however I will need this to work across the academic year Sept - July, a separate sheet for each month. Any help or suggestions to rectify the problem would be very much appreciated. Thank you!