Forum Discussion

Jliersenvlst's avatar
Jliersenvlst
Copper Contributor
Apr 19, 2022

Help with an excel formula

Hello, 

 

I am writing a formula for table from column A to Column U.  (In the dutch version so I translated the formula from dutch to english). 

 

The Formula is located in A4 Column A is called "Status" and at certain points within the table when you enter data the status needs to change. I got six status effects. 

 

  1. Not Assigned (when there is no date set in Column J)
  2. Assigned  (when there is a date set in Column J)
  3. In Process (when the date is reach in Column J)
  4. Process Finished (When the checkbox in Column K is checked)
  5. Complete (When the Checkbox in Column Q is checked)
  6. Canceled (when the checkbox in column S is activated this must overrule everything)

Now in column K/Q/S I made checkboxes and in Column L/R/T I assigned the True and False statements so I could use the checkboxes in the Formula. 

 

This is the formula I came up with but it ain't working

 

But it appears to not have been working, can somebody help me fixing the formula.

 

Excel
Formulas and Functions
office 365
  • OliverScheurich's avatar
    OliverScheurich
    Apr 19, 2022

    Jliersenvlst 

    =IF(T4=TRUE,"canceled",IF(R4=TRUE,"complete",IF(L4=TRUE,"process finished",IF(AND(J4<>"",J4<=TODAY()),"in process",IF(AND(J4<>"",J4>TODAY()),"assigned",IF(J4="","not assigned",""))))))

    You can try this formula which seems to work in my sheet if i correctly understand what you want to do. 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 19, 2022

    Jliersenvlst You include a part ALS(J4=NU........

     

    When the IF statements evaluate this part it breaks as NU is not recognized. So enter "NU" if you want to test for the word NU or, in case you intend to use the NU function, use NU() (NOW()  in English).

    But perhaps VANDAAG() (TODAY() in English) will have a better result.

    • Jliersenvlst's avatar
      Jliersenvlst
      Copper Contributor
      Apr 19, 2022

      Riny_van_Eekelen I tried both the NOW() and the TODAY() both doesn't seem to work. The formula works fine after putting a date in, after that it doesn't work anymore. When i check the boxes nothing is happening. 😞 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 19, 2022

    Jliersenvlst 

    =IF(T4=TRUE,"canceled",IF(R4=TRUE,"complete",IF(L4=TRUE,"process finished",IF(AND(J4<>"",J4<=TODAY()),"in process",IF(AND(J4<>"",J4>TODAY()),"assigned",IF(J4="","not assigned",""))))))

    You can try this formula which seems to work in my sheet if i correctly understand what you want to do. 

    • Jliersenvlst's avatar
      Jliersenvlst
      Copper Contributor
      Apr 19, 2022
      It is almost what I want I translated it backl to dutch and needed to change two things the <Today and the >Today they needed to be reversed.

      =IF(T4=TRUE,"canceled",IF(R4=TRUE,"complete",IF(L4=TRUE,"process finished",IF(AND(J4<>"",J4>=TODAY()),"in process",IF(AND(J4<>"",J4<TODAY()),"assigned",IF(J4="","not assigned",""))))))

      Now I got one more challenge, when I put in a date beyond the current dat in J4, so for example Today it is the 19-04-2022 when i entered that it says "In Process" which is good but when a project is planned on the 20-04-2022 after the current date it also states "In process" but it must have the "Assigned" status and tommorow when I open the excel it is changed to "In process" because of the date change. Is there a way that could be implemented?
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Apr 19, 2022

        Jliersenvlst 

        I don't understand why you have changed the suggested formula.

         

        The formula returns "Assigned" if the date is in the future. In the attached file the date in cell J5 is "19.05.2022" and the formula returns "Assigned" as intended. On the 19th of may 2022 the formula returns "in process" in cell A5. In cell J9 the date is "11.03.2022" and the formula returns "in process" in cell A9 as intended.

         

        If you want these results you can simply implement the suggested formula.

Resources