days left until shipment

[noviceneil]

Hi all,

I am trying to solve a little issue here and am in need of some help. First let me explain the situation.

I have a customer list with a corresponding sign up date. The customer will receive a shipment every 2 months from the date of sign up. Basically I want to be aware 2 days prior to shipment date that Mr X's shipment needs to go out soon. A color change would do it, however I'm not sure how to change the cell color for only those 2 days and to repeat every 2 months.

Another, posiibly better, option would be to have a count down to no. of days left until shipment.

Any suggestions?

Thanks

Neil

[DP978]

In A1 thru A3 -

Today(); Date from your Table; =60 - (A1 - B1)

Edit: Use conditional formatting to color your cell when C1 <= 2

[noviceneil]

Thanks DP,

but is there a way to reset this value everytime a shipment goes out. At the moment I use something similar but I have to reset "Last date shipped" otherwise it goes negative and continues down.

[Marcol]

Hi try this sample sheet attached.

In column A (Initial Sign Up)

In column B (Days to Shipment)

=INT(C2-TODAY())
Conditional formatting > less than or equal to 2

In column C (Next Shipment)

=DATE(YEAR(TODAY()),MONTH(A2)+INT(MONTH(TODAY()-A2)),DAY(A2))
Conditional formatting > formula is =INT(C2-TODAY())<=2


Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.