Mar 24 2022 03:44 PM - edited Mar 24 2022 03:45 PM
=IF(OR(AND($F3<DATE(2019,1,1), $F3=”NULL”), AND($F3>=DATE(2019,1,1), $F3=”NULL”)), "Opened", "Closed") I am getting a #NAME ERROR.
What I am trying to do is count the number of open tickets (so, carryovers from the previous month). I’m thinking it would be easiest to just use the first of the month as the count date, so, for example, the count for June, 2021 would be all tickets opened before June 1, 2021 that have a null resolution date or a resolution date on/after June 1. (Note: the null values show as null in the data.)
Mar 24 2022 04:42 PM
F3 cannot be a date and NULL at the same time, so the formula won't work. Did you intend to refer to two different cells?
Mar 24 2022 06:38 PM
If I understood the statement correctly:
Col1 | Col2 | Col3 | Col4 | OpenedDate | ResolutionDate | Closed/Open |
2/21/2022 | 2/28/2022 | Closed | ||||
3/1/2022 | null | Open | ||||
2/21/2022 | 3/1/2022 | Open |
=IFS($F3<DATE(YEAR(TODAY()),MONTH(TODAY()),1),"Closed", $F3>=DATE(YEAR(TODAY()),MONTH(TODAY()),1), "Open", $F3=”NULL”, "Open")
where, 2019,1,1 is replaced with YEAR(TODAY()),MONTH(TODAY()),1
I assume You are trying to validate 2 columns "Opened Date" and "Resolution Date". Why? Anyway, you can add logic to check OpenedDate just the way You were trying in your original approach.
All tickets with Resolution Date are closed. Why do you need to check Opened Date?
If You need "Opened Tickets Count", "Closed Tickets Count" by Year, Quarter, Month etc. why not use date functions?
Please write back and I am sure someone should be able to pitch in soon :)
Thanks!