Excellent. LEFT(TEXT(date,“ddd”),1) now works when date is a manually entered date.
However, if A1 = date, and A2 is a formula like =date+5, then =LEFT(TEXT(A2,“ddd”),1) is returning the first character displayed in A2 (rather than the first letter of the day of the week as it does in Excel and GS). For example, if A2 is formatted as “July 4, 2020”, then the formula is returning “J”, or if it is formatted as “7/4/2020”, it is returning “7”.
Then, I realized that LEFT(date,N) returns the first N displayed characters after formatting is applied. In Excel, if cell A1 is a date, LEFT(A1,3) returns the first 3 digits of the numeric value. However, in Google Sheets, if A1 is a date, LEFT(A1,3) returns the first 3 displayed characters.
This is actually kind of interesting, because until now I wasn’t aware of any formulas that could return values based on the number formatting of a cell. This means that for functions like LEFT, RIGHT, SUBSTITUTE, MID, etc., the format of the cell is included in the calculation chain. I’ve always thought that cell formatting was applied after or independent of the calculation chain, but it turns out that in Google Sheets and Spreadsheet.com, you can use these functions to determine the number formatting. Interesting.
Perhaps this is why Google Sheets has not made it possible to use conditional formatting to modify the number formatting of a cell (an annoyance I have run up against many times). In Excel, conditional formatting can be used to change the number format of a cell. This fundamental difference between GS and Excel may continue to be a problem with compatibility of some spreadsheets.