Trying to create a formula and not winning. I have to return a numeric value, based on a cell containing "Ongoing" or "Closed"

Essentially, if A1 = "ongoing" the formula needs to subtrackt the log date (A2) from today's date to return a value, however, if A1 = "Closed" the formula has to subtract A2 from A3

It's essentially to track incidents, by using the values to determine the escalation level, but we need to keep the historical data, hence the formula subtrackting A2 from A3 if A1 is "closed"

I hope someone can help, as my brain is about to explode.

Will A1 ever contain anything other than "Ongoing" or "Closed"? If not, then you only need to check for one or the other:

=IF(A1="Ongoing",TODAY()-A2,A3-A2)

or=IF(A1="Closed",A3-A2,TODAY()-A2)If A1 could contain something other than those 2 values, then you need to create a formula that tells it what to do in that case:

=IF(A1="Closed",A3-A2,IF(A1="Ongoing",TODAY()-A2,"A1 Is Neither Closed Nor Ongoing"))

i have colomn "A" number

0810001 to 0811000 =ASHOK

0850001 to 0850500 =BIREN

0850501 to 0851000 =MAHESH

0870001 to 0871000 = NIRAV

in colomn "B" which formula i use if any

nomber between above cell is marked.

thakor patel

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History