Conditional formatting on expiration dates Results

Hi, I'm new to this, so bare with me.
I have a bunch of laptops that regularily get signed out, anywhere between 1-14days.
I have a start date (B2) and return date (C2)... for example, Sign out is March 13, Return date is March 25

All I want is to have the sign out date go red when its due on that day.
So far I cannot find any google examples that meet my specific needs.
I'm trying conditional formatting in excel 2007 but I cannot figure out the string.

Thanks for help
Shane

I'm looking for a conditional formatting formula to keep track of follow-on
contract due dates. I've found many helpful formulas for calculating due
dates from a starting date...but, not working backwards from an end date.
Any ideas?

Some contracts are on a fiscal year schedule (30 Sep) and some are
off-cycle (any date.) How do I set up a formula that will color a cell red
one month before contract expiration and turn the cell yellow 180 days before
contract expiration. The formula must take into account that the cell
includes the expiration date.

Thanks in advance!

Hello,

I have excel 2003, and am trying to implement some conditional programing. There is a date in the cell, and Im trying to say, "If date is later than today, (expired), then red. It seems simple but the only thing that I have gotten to do anything is If cell is less than or equal to Today() then red. The only thing is is that no matter what date you put in the cell, whether it be before todays date or after, it stays red. Any suggestions?

I have a terrible Excel spread sheet that holds just some simple date data. It is a list of all the drivers that I work with drivers License. expiration dates and other documents that expire. I am the only one in my office that has any knowledge of how to get Excel to do anything and I have only basic know how of using simple formulas, simple logical operators, and simple conditional formatting. I opened my mouth and said I could get this mess of a spread sheet to basically keep its self up to date, because they are doing everything manually. Then I started working on it and well its beyond what I can do I think.

I need the sheet to subtract Today’s date [ =TODAY() ] from Exp. Date and at 90 days till Expiration turn the background of the cell yellow and display "90 Days". Then at 60 days same thing, again at 30 but change background to red display " 30 Days!" in yellow or black font, and stay with these colors but change message at "2 Weeks!", "1 Week!", "5 Days!", "3 Days!", "2 Days!", "1 Day!", "EXPIRED!".

I thought this could be done with just nesting multiple If Statements with some conditional formatting. So I started out just trying to get (1) If Function to work with conditional formatting changing the background to yellow. I then figured it would just be a matter of nesting then. I can write a If Function that subtracts the dates and returns "90 Days" for the true part. I can write a conditional statement that changes the background to Yellow but I can't seem to put the two together. That’s when I saw that this is probably going to require some programming and that is beyond me.

I am not sure how to approach this but this appears to me that it is a lot of programming and maybe I need to contact an individual programmer privately. If that is the case then all I need is someone to please point me in direction of how to go about doing that. I realize there is plenty of available material offered here that I can teach myself how to do this type of thing but right now I am just a truck driver without much time. I need this type of Excel Spreadsheet pretty much now. Also I do not claim at work that anything I learn here is my work, I tell people here if can't do it I know where to go ask. Getting free help with simple stuff is one thing but complicated programming that requires people who have taken years to learn something is quite another.

If you want to know the best way to drive a truck from Florida to BC I will be happy to tell you, but if you want me to do it I will probably charge you 1.65 a mile plus the cost of fuel and insurance. I am currently working in Iraq with a bunch of people who really have no idea how to keep and use the spread sheets they are working with so I try to help out. If I wrote too much here I apologize, just thought it might help to understand where I am coming from.

Thank You
Ken

Hi guys. I am struggling a bit trying to get my conditional formatting to work. I am using Excel 07. I was attempting to use the Conditional Formatting tool with little success.

I have 2 columns I need to consider.

First Column (V) - Contains Target Date
Second column (K) - contains Percent Complete

I need to turn Column V cells a certain color based on 2 criteria. Anything that has a percentage of 95% (column K) or above should just be green and the rule should stop there.

However, anything that is less then 95% (column K) I want to assign the following criteria for the Cell Color (column V).

RED = 5 days from todays date or expired
Yellow = 6 to 10 days from todays date
Green = +10 days

First priority is just to get color formatting correct based on the date. If I have to I can just filter by the other column that contains the percent manually that is fine. The auto shading everything based on the other column (%) is just a nice to have but not essential.

Any help would be greatly appreciated. I have been really struggling with this.

Hello, I rarely use excel and i am organizing a large list with expiration dates.
What i am trying to do is have the dates that are today and older turn a certain color, so that they stand out among the others. I know I need to use conditional formatting but i don't know where to start with the formula.

I looked on the microsoft website and a couple other forums but they were no help.

Thanks a lot!

OK, hoping some out there can solve this one for me!

I have got a excel file i use to keep track of my companies training records and currently I have set the conditional formating to have dates in GREEN when the employees training is valid and RED when it has expired.

What I would like it to do is go AMBER (or somethhing similar) when it is 3 months from the date in the cell.

Im sure this is dead easy to do, but i just cant figure the damn thing out!

Hope someone out there can help on this.

I thank you in advance

Steve Man

So I might be completely crazy and this might be beyond excel's capabilities but here's what I am trying to do.

In Column A I want to have a drop down menu (using data validation) and once a particular item is selected from the drop down menu have a specified countdown timer start and once it expires using conditional formatting it makes the row highlighted in red.

Is this possible at all or does anyone have any recommendations on how I can achieve what I am looking for?

I run a dealership and trying to make and excel sheet for my used vehicle department so that when we get a car in on trade we enter it into the spread sheet so that once a designated time expires, it highlights that vehicle. The reason I am doing this is because I am trying to find an efficient way to keep track of the time it takes from the date the car comes in until our service department safety inspects it and then also from that point until our detail department details it and it is ready for the lot. I figured if it is possible on excel I can put it as a shared file on mine and our service managers computers and that way it will highlight the vehicle in red once the timer is expired showing it has taken too long in one of the processes.

Any help is greatly appreciated!

I've got a spreadsheet I created where it highlights dates based on the fact that if a date exceeds a certain period, given a certain cell value, it highlights the value.

Attached is a sample of the spreadsheet. You'll notice there is conditional formatting involved as well.

I was wondering if there was a way to format the rows to have a fill color of red. In my example, all the rows that have an expired date before the Todays date (Rows where there is a yellow cell in the expiration date), and also has a value of "N" in the "Active" column, I'd like to have red. So for example, for Supplier 3, I'd like to have cells A-G be filled with the color red, since it has an expiration date that is less than the value of todays date, and because it also has a value of "N" in the Active column?

If this is confusing, let me know. I'll try and make it sound less complicated.

Thank You!

I have a tracker listing multiple personnel in Column A and multiple certifications they are required to hold in Row 1. The table is populated with the individual expiration dates of each certification. I have utilized conditional formatting to highlight expired certifications but would like to take it a step further and create by name lists on additional sheets listing just the expired personnel for each certification.

I am building a spreadsheet that will track due dates for renewal of licenses & preventative maintenance etc.
I am trying to get warnings that due dates are approaching or are expired to allow the folks who have to perform the work enough time to complete them.

Is it possible to have the following actions based on today's date?:

1) cell turns red if expiration date has arrived or passed

2) cell turns yellow if date approaching...say in 10 Days

3) cell is green from issue date to 10 day warning.

cell a1 is the issue date......cell b1 is the expiration date

I have been close but can't seem to get it right

Hello all,

I have been searching the forums for this, and I think i know what i need to do, but i have a couple of questions and some refinement help to ask.

in the attached sample spreadsheet I have rows of people and a date associated with that particular person. When that date is more then 9 years 6 months from today, i want the entire row to turn yellow bold ect. then when it goes to 9 years 9 months I want it red.

i think i got one row to sorta work with =TODAY()-G3>(365*6)

thats a rough guess on how to use conditional format (and yes i know the years arent right im just testing at a 6 year mark for now).

My questions are this
1. If i use the above method, how do i copy that to all cells?
2. is there a more elegant way to do the time span? obviously (365*9=30*6) is hardly accurate, and I really would like it pretty accurate if i can be.

thanks all.. your the best.

I created spreedsheet where number cell useing conditonal formating will
change color if a less than condition takes place. What I need to know is how
do I once that conditional formating takes place (color change) do I send
this infor to a new spread sheet only if the condtion is met and to a thrid
spreedsheet within the same workbook that has a condition for dates i.e. like
expiration date.

My condition on the cell is that if the number entered is less than 2 (for
instance) the number in the cell will turn red and be bold. if this happen
(it turns red) I would like for it to then be sent to second spreadsheet as a
red bolded number two. I am trying to build a shopping list for my wife so as
she updates it (inventory list) with actual on hand numbers. If a number for
instance cans of soups fall to a present number like 2 ea then it will turn
red and send this information to a second spreadsheet to build an actual
shopping list. Then I would like to also set up the original list (inventory
list) so not only will i attached the conditionl formating toi turn red and
bold if it drops to this number, but also to date stamp the entry and
condition formate it to feed a thrid sheet for expiration dates for instasnce
if the original inventory entry is a given date then 180 days it will
automatically populate a third spreasheet to say throw it away. thank you for
you efforts

I had asked another question before based on this same file so some of you might recognize this.

We have certificates that need to be renewed annually. We keep track of when the last renewal date was for each customer on an excel file.

I have a worker that references these certificates in another file. The customers name is inputed in a cell on this seperate file and the worker would like for the fill color to change if the certficate needs to be renewed in 90 days and also change depending on if it is also expired.

I think this can be done by combining conditional formatting with vlookup but I'm not completely sure how to go about doing this. I tried looking around through conditional formatting threads to look for a similar problem but I didnt find anythign quite the same. I apologize if this is a repeat thread, please point me to that thread. I can make a dummy workbook if need be, please let me know. Thank you guys (and gals) for your help so far.

problem not clear
if you have already conditionally formatted some cell or cells then if you
just copy and paste into another sheet is not the conditional fomat
carried to the second sheet.

try.
if you dont want the values to be pasted then copy pastespecial formats

James M <JamesM@discussions.microsoft.com> wrote in message
news:33FADCB9-23DA-448F-A87A-92801CCC9084@microsoft.com...
> I created spreedsheet where number cell useing conditonal formating will
> change color if a less than condition takes place. What I need to know is
how
> do I once that conditional formating takes place (color change) do I send
> this infor to a new spread sheet only if the condtion is met and to a
thrid
> spreedsheet within the same workbook that has a condition for dates i.e.
like
> expiration date.
>
> My condition on the cell is that if the number entered is less than 2 (for
> instance) the number in the cell will turn red and be bold. if this happen
> (it turns red) I would like for it to then be sent to second spreadsheet
as a
> red bolded number two. I am trying to build a shopping list for my wife so
as
> she updates it (inventory list) with actual on hand numbers. If a number
for
> instance cans of soups fall to a present number like 2 ea then it will
turn
> red and send this information to a second spreadsheet to build an actual
> shopping list. Then I would like to also set up the original list
(inventory
> list) so not only will i attached the conditionl formating toi turn red
and
> bold if it drops to this number, but also to date stamp the entry and
> condition formate it to feed a thrid sheet for expiration dates for
instasnce
> if the original inventory entry is a given date then 180 days it will
> automatically populate a third spreasheet to say throw it away. thank you
for
> you efforts
>
>

I have a spreadsheet with recert dates on certain things. I need a way for excel to alert my staff when the due date is 60 days and 30 days before the certiffication will expire in their email. I am not savy with vba or macros so please use laymans terms if helping me.
I am already using conditional formating to highlight the dates in certain colors.

Please and thank you to anyone that can help!

Hi,

i am working in a pharmacy and i have extensive list of drugs that expire at a certain date. The excel sheet contains the name and the expiration date of the drug, so i was wondering if i can use conditional formatting for automatic chance of cell color when, let's say the drug is 60, 30 and 10 days before expiring.

Thank you very much!

Hello All...

I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?

Eg. John has first aid and expires on 11/15/2009. I'd like the cell that has this date to highlight red on 10/15/2009.

I have messed around with the conditional formatting and know how to format the cells color and font et., but I can not figure out the formula to use to get my results.

I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)

I would appreciate any help.

Thanks for checking out my post

I have a spread sheet in which I need to set up at least two conditional formats based on date. It is a list of contracts with expiry date in one column, followed by 52 other columns, 1 for each week of the year. Say for example a contract expires on 31/01/2011, I would want all cells in the row from week 4 onwards to automatically turn red, as I cannot use this contract in those weeks. The cell that contains the date itself will automatically turn red using a NOW formula, however after a lot of messing about and time wasting, I just can;t get the 52 remaining columns to link to the expired date. Can anyone advise? Example attached.


attached is an example of the "leave tracker" i am working on. once you open you will see that the following is already conditionally formatted. if any cell is empty it will highlight orange. in the "from" column, if the date is current or past it will turn green. in the "to" column if the date has reached or past it will go red. if the from or to date has not been met then it will not be colored. what i would like to do is the following. if the from date has been reached then highlight the entire row green. if the to date has been reached or past then turn the entire row red.

also as a bonus question i would like to move all expired rows to a second sheet, so i can show a history. if i can do this then i would like that to happen the day after the "to" date has been met, moved onto the leave history tab, and delete the row from the leave tracker tab. id prefer to do this with conditional formatting or formulas, and not macros. its sometimes difficult to run macros in military computers.

if your in the army, or military in general, i will pass this along to you after i have it completed. i also have a pretty gnarly alpha roster that gives you some pretty awesome breakdowns, and makes battle roster numbers for you. my profile tracker also does some pretty good breakdowns.