cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate days late with +X days

Hi there!

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!

Cheers,

Lele

1 ACCEPTED SOLUTION
Frequent Visitor

Okay, figured it out!

Here's the updated formulas:

Delivery Status:

Days Late:

End result:

Much better! I guess coffee and pacing back and forth helped! Hope this helps someone. 😁

Frequent Visitor

Okay, figured it out!

Here's the updated formulas:

Delivery Status:

Days Late:

End result:

Much better! I guess coffee and pacing back and forth helped! Hope this helps someone. 😁

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors