Forum Discussion
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.
- Not Assigned (when there is no date set in Column J)
- Assigned (when there is a date set in Column J)
- In Process (when the date is reach in Column J)
- Process Finished (When the checkbox in Column K is checked)
- Complete (When the Checkbox in Column Q is checked)
- 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.
=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 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.
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. 😞
=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.
- 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?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.
