Excel (IF) conditions with dates and changing status based on future dates-CodePudding

I have a question on excel, trying to use the below formula, if you can insert it in cell B4 and it will be referencing dates from column C. When testing this for date range 01-May-2022 till 30-Aug-2022, it displays a few FALSES, I am trying to fill these statuses without generating any issues with code, so it works flawlessly. At this stage the more I try to tweak it, the more it breaks.

The status I am requiring is:

If Today() is 31 days before Column C date range, then "Not Due"
If Today() is anywhere between 1 and 30 before column C date range then "Due Soon"
If Today() = column C date, then "Due"
If Today() is greater than column C date then "Overdue"

Can anyone please suggest a fix for date ranges 01-May-2022 till 30-Aug-2022, appreciate it! False is observed for 26 June 2022, 26 July 2022, 27 July 2022, and 08 April 2023.

There is another False I have observed which appeared sometime around June 2023 for this formula, not sure if it is an issue with Excel?

Thank you.

Formula =IF($C4="Not Required","Not Required",IF(ISNUMBER(VALUE(LEFT($C4,1))),IF(TODAY()>$C4,"Overdue",IF(AND(TODAY()<$C4,TODAY()>($C4-30)),"Due Soon",IF(TODAY()<($C4-31),"Not Due")))))

CodePudding user response：

Something like this should work for you:

=IF($C4="Not Required",$C4,IF(ISNUMBER($C4),CHOOSE(MATCH($C4-TODAY(),{-99999,0,1,31}),"Overdue","Due","Due Soon","Not Due"),"Not Due"))