Need Help With IF Formula Regarding to Conditions

Occasional Visitor

=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

@ILoveData94 

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?

@ILoveData94 

If I understood the statement correctly:

Col1Col2Col3Col4OpenedDateResolutionDateClosed/Open
    2/21/20222/28/2022Closed
    3/1/2022nullOpen
    2/21/20223/1/2022Open


=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!