I'm trying to calculate if an order was shipped before its due date, then it's "On Time". However, if an order was shipped after its due date, then it is "Late", with a +X day(s) after its due date. For an example, a package was ordered 4/6, and due date is 4/10 - if package was shipped 4/7, it's on time. If it's shipped on 4/11, it's late with a +1 day. If it's shipped on 4/12, it's +2 days and so on.
I have a column called Current Date whose formula is "TODAY()".
This is my formula to calculate Delivery Status (On Time or Late):
Formula for Days Late:
I can't seem to get the formula correct because I'll either end up with a negative days late or positive days late, or the calculation is off:
There's no reason for an order that shipped 11/23/22 whose due date is also 11/23/22 to be +134 days late... Some of the calculation is correct, such as due date 11/23/2022 and ship date is 4/5/23 with a +134 days.
My goal is if an order was shipped before due date, then Delivery Status is On Time, and Days Late column is empty. If order was shipped after due date, then Delivery Status is Late, and a +X day(s) in Days Late. I think the problem is I'm using [Current Date] to substract [Due Date]? Any insight is greatly appreciated! Thank you!