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