IFS statement with a date from another cell

JayneB

Hi,

I am trying to create a formula with dates that will change each month to the start and end date of the new month, so I was hoping that I could link the start and end dates to a cell in the Smartsheet which have the correct dates and will be easy to update rather than changing each formula each month, but when trying to link to a cell, the formula keeps returning an "#UNPARSEABLE" error.

This is my current formula that I would like to automate - this formula currently works, but needs to be manually updated monthly:

=COUNTIFS({Parent}, 0, {End Date FY25}, <=DATE(2024, 10, 30), {End Date FY25}, >=DATE(2024, 10, 1), {Team Name FY25}, Team@row, {Status}, <>"Complete", {Status}, <>"On Hold")

I would like to make the formula something like:

=COUNTIFS({Parent}, 0, {End Date FY25}, <=DATE([end date]1), {End Date FY25}, >=DATE([Start date]1), {Team Name FY25}, Team@row, {Status}, <>"Complete", {Status}, <>"On Hold"))

OR

=COUNTIFS({Parent}, 0, {End Date FY25}, <=DATE(end date@row), {End Date FY25}, >=DATE(Start date@row), {Team Name FY25}, Team@row, {Status}, <>"Complete", {Status}, <>"On Hold"))

Paul Newcome

Try this:

……………………., {End Date}, @cell >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {End Date}, @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, ……………………..