Count days past from negative given start date

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
hi all,

bit of an obscure one this, and maybe why i'm struggling with it, but here goes.

We run a 'days outstanding' report that allows us to track customer quotes and special offers, they have a 14-day period to return, plus a 14 day grace. I'm trying to create a formula that gives us almost a countdown to expiry based on the date of issue.

Let me give an example:
  • The date of quotation/offer will be day -14 (negative 14) - actual date is entered in Col. E
  • date of quote/offer +14 days is day 0 (zero). i.e. they should have returned the quote to us by now
  • date of expiry is 28 days from date of quote issue (i.e. 14 days + 14 days grace) is day 14 (positive 14) - and therefore 14 days PAST expiry..
Real-world example: I give customer A a quote today, 7th October. Therefore in 14-days his 'return by date' expires; so we start at Day -14. In 14 days (21st Oct) we reach Day 0 and in 28 days, (4th Nov) we are at Day 14, and the days continue to count UP from there until we get a return.

with this set up i intend to use a conditional formatting rule to colour all negative numbers green, days 0 to 14 amber and 15+ red, giving a quick overview of who's late returning.

i've tried a few things already, but my formula always results in the count going DOWN from -14... e.g yesterday it was -14, today -15 tomorrow -16 etc etc

here's one of my most recent attempts to fail:
Excel Formula:
=IFS(C5<>"",(IF(TODAY()-E5>14,14-E5+TODAY(),E5-14-TODAY())),(IF(Q5<>"CANX",TODAY()-Q5,"CANX")),IF(C5="",""))



Thanks in advance - and i welcome any assistance you can offer.

Si3PO
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,127,822
Messages
5,627,096
Members
416,220
Latest member
Sykotik7S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top