Forum Discussion
Need Help With IF Formula Regarding to Conditions
=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.)
2 Replies
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!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?
