Formula works in one cell and not another

[MeganH84]

Hi Everyone,

I have an issue that I'm hoping to get some help with. I have a workbook with 4 sheets in it to track spending for a lump sum that is divided into different areas. The first sheet is a summary sheet. It's supposed to add expenses from the other sheets to sum up monthly spending per category. For example, we have a sheet to tracking spending for encampments, I'm trying to get the summary sheet to add the honoraria we have given out for the month of September 2022 as a whole. It's supposed to add all the honoraria for September 2022 and return the value to the summary sheet. This is the formula I'm using =SUMIFS(Table1353[Honoraria],Table1353[Date],">="&DATE(YEAR(D1),MONTH(D1),1),Table1353[Date],"<="&EOMONTH(D1,0)) 

It works in the cells for January 2023 onwards, but not for any of the months before. I've checked to make sure there aren't any weird spaces in any of the cells it's pulling the information in, and the formatting is correct (accounting number format for the dollar amounts and date format for the dates). I appreciate any help on this.

[#2]

@MeganH84 

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?

[#3]

Hi Hans,
Here's a link to it in OneDrive. https://elevatenwo-my.sharepoint.com/:x:/g/personal/mhoier_elevatenwo_org/EVXKdrfsOkpPuvlau0B-1AQBWl...

thank you!

[#4 · Accepted solution]

@MeganH84

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.

[#5]

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!