Count Down

[Phil Walters]

Hi All

I have a chemical log which has an expiry date column showing various dates. I have also added another column called expiry countdown as I would like to be able to count down the number of days remaining for this product based on its expiry date and the date when I open the spreadsheet. My plan then is to add conditional formatting to highlight in colour when a product is getting close to expire.

does anyone know a way of being able to show days remaining?

copy attached.

Thanks for your help.

Phil

[Blake 7]

iN j2

Please Login or Register  to view this content.

wHAT IS CLOSE TO EXPIRE... 1 DAY? 6 DAYS? 3 MONTHS..........

[Phil Walters]

Hi Blake

Thanks for the reply, You have done it very simply thanks. As you have shown all I wanted was the number of days remaining before the expiry runs out.

My idea with conditional formatting would be 45 days and over the cell would be green, 30-44 days the cell would be amber and 29 and below would be red.

Im assuming this is possible with this formula in place or would it not work like that.

Thanks again

Phil

[GlenQ]

Hi,

I also use a similar scenario on a spreadsheet that I use.

In your case it would be in Cell J2 place the following:

Please Login or Register  to view this content.

I also format the receiving cell (J2) in the number format and it seems to work okay.

Hope this helps!

[Phil Walters]

Thanks GlenQ

[Phil Walters]

Thanks guys

the formatting has worked but ideally I would like to show any blank cell as blank, So we don't have the drag the formula down everytime I add an entry.

Any thoughts?

I have re attached to highlight what I mean

and thanks again guys

Phil

[Blake 7]

hiya - see the attached - does that work for you?

[Phil Walters]

hi Blake

that's where I got with the formatting as per my last attachment. the problem I have is if I were to drag the formula down so any future log entries are added the countdown number and formatting would appear automatically, however if there were no entries on the row then I would still like the formula there but be blank as there is no information in G2, does that make sense??

Thanks Phil

[Bobsone]

Would this work,

=IF(G2="";"";ABS(TODAY()-G2))

[Blake 7]

oh yes - i see the problem - even with the formula in the cell it CF's Green (even using Bob's formula above) its totally possible, i just need to have a think!

[Phil Walters]

Hi Bob

Sorry it says there is an error!!

Thanks Phil

[Phil Walters]

its highlighting the first 2 quote marks!!!!

and iam totally clueless lol

[Bobsone]

the error is probably coming from the ;
Our Excel is German so we use ; instead of ,

try this

=IF(G2="","",ABS(TODAY()-G2))

Also, I have been playing with your formatting, do you realize that once the count has gone past the expiry date the count starts going back up again e.g. a delivery date of 28.10.2014 and an expiry date of 28.09.2014 gives a count of 31 and an orange cell.

[GlenQ]

the error is probably coming from the ;
Our Excel is German so we use ; instead of ,

try this

=IF(G2="","",ABS(TODAY()-G2))

Also, I have been playing with your formatting, do you realize that once the count has gone past the expiry date the count starts going back up again e.g. a delivery date of 28.10.2014 and an expiry date of 28.09.2014 gives a count of 31 and an orange cell.

The following can be fixed by removing the ABS and switching your subtraction elements.

Please Login or Register  to view this content.

[Blake 7]

Hi - im not so good with CF'ing but I think i have a workaround using a helper column and formatting that one .... may be ok!

[Bobsone]

the solution from GlenQ looks like a winner

I changed the formatting and with GlenQ's solution it looks like it works as intended.

I have set the conditional formatting as follows the red is set; cell Value>Between =1 and =30, orange is Cell Value>Between =31 and =40 and green is Cell Value>Greater than =40.
I added a fourth conditional format to reflect any value that is past its use by date; Cell Value>Less than =1

[Bobsone]

Hi
I have loaded the sheet with GlenQ's solution and my Conditional Formatting mods in J2:J497

Hope this helps.