I am in process of transferring a large excel document into Smartsheet (over 2000 lines). The excel version is tracking 2007 projects, some of which are complete, some no longer active, and some active. I need to count how many active projects will be completed in year 2018, year 2019, and year 2020. Currently, our excel document has a COUNTIF formula to count the active projects within a certain time frame. For example, projects with an estimated completion date of 2018 have this formula in Excel: =COUNTIFS('DEX MASTER'!H:H,"Yes",'DEX MASTER'!U:U,"<=12/31/2018").
From what I have found, Smartsheet does not have the ability to count text and dates within one formula. As a workraround, I have created 3 check columns, one for each completion year, so I can then create a separate formula to count the check boxes. The formula I have is: =IF(AND([Yr3]997 < DATE(2018, 12, 31)), 1)
This has been working, with two exceptions: some projects that are no longer active have the dates removed, and the checkbox is still being checked when it shouldn't be (see photo). Also, we put a "c" in the date column once a project has been completed, which then gives me an error in the checkbox formula (see second photo). Any help with these two issues would be appreciated!
