Excel Exp. Date Reminder

[cpmelia523]

I am managing a list of over 100 employee's visit requests and when those request are rountine 30 day requests I am trying to come up with a formula that will alert me when the visit request is 7 days away from expiring so I can remind the employee to resubmit the next request. I would like that column to turn red so it is even more visible. I have several column's already in my spreadsheet such as...
Start Date
End Date
If the column is a 30 day request
and the empty column waiting for the formula..
Any help would be greatly appreciated!!!
Thanks!

 

[Sthrncali]

You can add a helper column to your worksheet.

Just add 1 column directly to the right of the Date column that displays the expiration date.

Then put this formula in there:

=IF(A1 -TODAY()<7, 1, 0)

Where A1 is your Expiration Date column. Then you can just set conditional formatting to color it RED if your helper column value is 1. You can then hide your helper column if you dont want it visible.

 

[cpmelia523]

I would like it to show the date it is expiring and I do not want to hide the colum....I would like it to base off of if the one column has a "Y" letting me know it is a 30 day request then to run the formula letting me know when the expiration date is 7 days away...
Thanks!