# Need Help With IF Formula Regarding to Conditions

Occasional Visitor

# 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

# Re: Need Help With IF Formula Regarding to Conditions

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?

# Re: Need Help With IF Formula Regarding to Conditions

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!