Forum Discussion
Formula works in one cell and not another
Thanks. Cell D1 on the Summary by Month sheet contains the text value Sep-22, not a date. Table headers are always text, you cannot change that.
When you force Excel to treat it as a date by using YEAR, MONTH or EOMONTH, it is interpreted as the 22nd of September, and since the year is not specified, the current year is assumed. So Sep-22 is interpreted as 22-Sep-2023.
If you enter 1-Sep-2022 in D1, the formula will work as intended, even though Excel will display 01-Sep-22.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- Hi Hans,
Here's a link to it in OneDrive. https://elevatenwo-my.sharepoint.com/:x:/g/personal/mhoier_elevatenwo_org/EVXKdrfsOkpPuvlau0B-1AQBWlPGn4Na6ZaC4OV-HeXSEQ?e=wrgaX6
thank you!Thanks. Cell D1 on the Summary by Month sheet contains the text value Sep-22, not a date. Table headers are always text, you cannot change that.
When you force Excel to treat it as a date by using YEAR, MONTH or EOMONTH, it is interpreted as the 22nd of September, and since the year is not specified, the current year is assumed. So Sep-22 is interpreted as 22-Sep-2023.
If you enter 1-Sep-2022 in D1, the formula will work as intended, even though Excel will display 01-Sep-22.
- Thank you so much Hans! I figured it was something to do with the year but I couldn't figure out exactly what was happening. I really appreciate your help with this issue!
