Using DATE Function

Adam Freeman

I have multiple similar sheets that I have working off dates to record data by month. At the end of the year, I have to go in and adjust the sheet to record data for the next year.

Currently the majority of my formulas use the DATE function inside SUMIFS.

Example: =SUMIFS({2021 Total Amount}, {2021 Complete Date}, >=DATE(2021, 12, 1), {2021 Complete Date}, <=DATE(2021, 12, 31)

Is there a way to use 2 other columns within the sheet to provide information for the formula for the beginning and end dates so that I am changing the dates of 2 columns versus the dates inside each formula for each cell throughout the sheet? I think the formula should look something like this but it keeps coming back 0

Example: =SUMIFS({2021 Filed Total Amount of Job}, {2021 Filed BM Date}, >=[Month Start]@row, {2021 Filed BM Date}, <=[Month Finish]@row) ...Month Start and Month Finish would be the 2 columns added with the beginning date and end date listed for each month.

Paul Newcome

Your second formula is exactly how it should look. Did you double check that all date columns are in fact set as date type columns and the data that is entered into them are in fact dates (and not just text strings that look like dates)?

Paul Newcome

Your second formula is exactly how it should look. Did you double check that all date columns are in fact set as date type columns and the data that is entered into them are in fact dates (and not just text strings that look like dates)?

Adam Freeman

That was it. I had one of the 3 columns not set as a date only. Thanks for the help

Paul Newcome

Happy to help. 👍️