Conditional Formatting - Expiration Dates

Mar 23, 2007

I need to set up a conditional formatting formula to show when a certificate of approval is within 90 days of expiring and when it actually has expired.

The conditional formatting is in column A and the expiration date is in column I.

ADVERTISEMENT

Excel 2010 :: Conditional Formatting On Expiration Dates?

Jun 21, 2011

I have a spreadsheet where we track our contractor's Worker's Comp and General Liability insurance certificate expiration dates. I want the expiration date to highlight in red if it is expired and to highlight in orange if it will expire within 30 days or less.

I have attached a testing sheet similar to what I'm working on. The F column has the dates that need to highlight. I have MS Excel 2010.

View 6 Replies View Related

Excel 2011 :: Conditional Formatting For Training Expiration Dates

Mar 28, 2014

I manage a number if individuals in the Army and as such, track their annual training requirements. Some requirements are semi-annual, annual, quadrennial, etc...

I have a spreadsheet to track the dates that the requirement was last completed (attached). I would like to use conditional formatting to turn dates that are within 30 days of being due (or blank) RED and dates that are between 31 days and 60 days of being due YELLOW.

I am using Excel MAC 2011.

View 3 Replies View Related

Excel 2010 :: Conditional Formatting Based On Expiration Dates?

Feb 3, 2014

I am trying to use conditional formatting to calculate when the date in the top column gets within 60 days from expiring to turn yellow, 30 days from expiring to turn orange and when it expires to turn red. The dates that are listed from row 5 down are when the classes were taken. the expiration date will be based on row 3. Some are annual,3 years and 5 years, etc. I am using Excel 2010....

View 10 Replies View Related

VBA Code For Multiple Expiration Dates

Jun 11, 2014

I have a vba code for an excel spreadsheet that lets me know when a particular persons' certification is about to expire (within 31 days). However what I cannot figure out is how to make this work with more than one expiration date ( additional columns containing other certification dates). I keep getting errors when trying to add additional columns to check ( I do admit that my VBA coding is atrocious at best).

Ultimately what I would like to achieve is a list of names with expiration dates alongside popping up when opening, but maybe I am asking too much?

View 9 Replies View Related

Generating Monthly Report From Expiration Dates

Jun 3, 2013

I am new at excel and have very limited experience. I have a employee training database, with employees (in column A2:A54) and the certification names (in row A1:AS1) that I must generate a monthly report on employees that are expiring within the current month. The cells are already conditionally formatted to change colors for one month prior to expiration date, current month of expiration date, and past the expiration date.

I am manually gathering the data then adding it to another sheet. In the mist of trying to gather data, I am missing and/or overlooking information. I wanted to know if there is a way to automatically generate a report based on the expiration dates that would pull the employees name and which certification is expiring or expired, preferable both. I have tried reading threads and applying advanced filters but due to the number of certifications it is not functional to do it this way.

I have attached a sample of the spreadsheet : ETD.xlsx‎

View 3 Replies View Related

Conditional Formatting With Dates?

Apr 18, 2014

I am trying to do conditional formatting to make a cell say something for 3 different scenarios. If K6 > G6, then I need M6 to say "Early." If K6 < G6, then I need M6 to say "Late." If K6 = G6, then I need M6 to say "On Time."

View 7 Replies View Related

Conditional Formatting With Dates

Feb 15, 2014

In my attached file,

In Column Q, Please drive a formula that shows.... after 7 days of column (M)reminder III these words must appear '' File Pending'' in 'COLUMN Q' and at the same, Indicator column (O) will also become RED.

Test.xlsx‎

View 14 Replies View Related

Dates And Conditional Formatting.

Oct 3, 2008

The scenario. Column A has a set (due) date all of the same. Column B is where review dates are typed in. Column C is where the status is, Due = still as yet to be done, but still yet to pass the Due date with no date in Column B (formatted yellow with the text "Due"). Complete = for when a date has been entered in Column B (formatted green with the text "Complete"). Urgent = the review date has passed and with no date in Column B (formatted red with the text "Urgent"). I have tried by editing previous similar formulas but to no avail

View 2 Replies View Related

Conditional Formatting Dates, Nth Value

Oct 24, 2008

In A1 Downwards i have a list of dates for an entire year
I have highlighted these cells and wish to conditional format them.
I want every 3rd date from a specific date say in cell b1 to be highlighted up and down within the selected area. For example

____A _________________B
1 1/1/08 ____________ 5/1/08
2 2/1/08
3 3/1/08
4 4/1/08
5 5/1/08
6 6/1/08
7 7/1/08
8 8/1/08
9 9/1/08

Is there also a conditional format formula that i can use to highlight b1's day every month.
in this example highlight the 5th of every month again going up and down within the selected dates.

View 6 Replies View Related

Conditional Formatting - Using Dates

May 28, 2009

Im trying to get conditional formatting to look at a completion date in cell A5 and getting cell D5 to change colour depending on the length of time to cell A5. What I would like is for D5 to change to red if there is 4 months or less to the completion date in A5. D5 to change to orange if there is still 4 - 8 months from the completion date A5. D5 to change to green if there is still 8 - 12 months from the completion date A5. Hopefully the formula will also work if cell D6 is looking at completion date in A6 with the same traffic light system and so on.

View 3 Replies View Related

Conditional Formatting For Dates

Oct 23, 2013

date conditional formating issue

Valid to DatesConditional Formating

30.10.2013RED
2.01.2014RED
30.08.2014YELLOW
30.09.2013no formattingExpired
31.12.9999no formatting

The dates are in different format with dots in between as shown above Also with respect to date which has already expired, no formatting needed but i need a formula in the next column stating "Expired"

If valid to date is within 90 days from today- RED
If valid to date is within 365 days from today- YELLOW

View 3 Replies View Related

Conditional Formatting For Dates?

Mar 2, 2014

I have a sheet where K5:K26 contains names of months. I would like to conditionally format that column so that any month that is less than TODAY appears red. So, if that column has a cell value of February, the cell would appear red since we are already in March. I would guess this involves a formula using TODAY, but I can't figure it out myself.

I know how to apply conditional formatting; I just lack the proper formula.

View 14 Replies View Related

Conditional Formatting And Dates

Oct 23, 2008

I have a list of dates that I want to flag as red if the date passes and the task is not complete.

So I would have 2 columns

Estimated completion date & Completion date.

If the completion date cell is null AND the Estimated completion date has passed then the Estimated completion date turns red (or a red block appears in a new column if that is easier!).

View 2 Replies View Related

How To Add Conditional Formatting To Dates

Feb 23, 2012

I have a large spreadsheet with dates. I want blank cells to be conditionally formatted to turn red and cells with dates older than 1 year to turn yellow. I have cell C1 set up as the current date (Today()) and the others reference the other cells with dates.

View 6 Replies View Related

Conditional Formatting With Dates?

Nov 12, 2013

Column C has dates in... and I have formatting that turns the cells in Column G Red if the date in Column C is 14 days or more old.

my problem is that if a cell in Column C does not have a date in it, the corresponding cell in Column G still gets formatted and I want it to not turn red if there is no date in Column C.

View 8 Replies View Related

Conditional Formatting For Three Different Dates

Jul 11, 2014

I am trying to use conditional formatting and its not working for me.

If I put the date 1 Jan 2014 in the cell of E2 I need a conditional format that will turn the cell yellow when it is two month from being a year old, so on 1 Nov 2014 it should turn yellow. Then I need it to turn orange when it is one month away from being a year old, so Dec 1 2014 it turns orange. And then when it is over a year old it turns red, So 1 Jan 2015 the cell turns red.

The formatting I have now turns them yellow, orange, and red but at the wrong times, not sure how to fix that.

View 5 Replies View Related

Conditional Formatting Between Dates

Jul 7, 2008

We need to track the batteries if it is expired or not.Expiration is 3 months. once you received the battery, you need to put the date in the sheet (A1) and then the expiration will be automatically calculated in B1. another also, in B1, will change its color according to how may days left before the expiration date.

black color if the expiration is < 15 days
red color if the expiration is 45 days
white color if B1 is blank

View 9 Replies View Related

Conditional Formatting With Dates

Aug 7, 2007

I want to be able to format a whole row with a particluar text colour when a date is entered into column A. I have managed this no problems using conditional formatting. The problem I am having is that I need to change that row's colour again if both colomn A's date is complete and a date is also entered into colomn B.

I have tried conditional formatting without success but I have the niggling feeling that I have read this it is not possible to use this method.

View 4 Replies View Related

Conditional Formatting For Passing Specified Dates

Aug 24, 2009

I'm having trouble putting the correct conditional formatting on a column of dates.
Basically, in column 'A' I have a list of appointment dates. Column 'B' contains breach dates for a 10week target. And Column 'C' contains breach dates for an 18 week target. Looking something like this:......

I need to format column 'A' so that the cells will turn Amber if the appointment was after the 10 wk breach date and turn Red if it was after the 18 wk breach date.

View 5 Replies View Related

Conditional Formatting For Dates (x3 Criteria)

Jan 31, 2013

I'm desperately trying to work out conditional formatting for different dates.

I have =TODAY() in F3

In the remainder of column F (from F19 to F625) I have either blank cells or varying dates (dating back to 2011 and dates going forward into 2014).

I need to Red/Amber/Green the following:

Green = any dates which are 45 days or more in the future from today's date
Amber = any dates which are between +1 and +45 days in the future from today's date
Red = any dates which are today's date or a date in the past

I can get one or two of the criteria working i.e. Green and Red, but not the Amber. Is one overriding or ignoring another?

View 2 Replies View Related

Conditional Formatting For Expiry Dates?

Jul 30, 2013

I have a training sheet where the training could expire in either 1 2 or 3 years, i am trying to get the conditional formatting for each to change color when the training is due to expire in 6 months 3 months and 1 month also to show red when expired

View 14 Replies View Related

Conditional Formatting With Dates And Blanks?

Oct 2, 2013

I am currently setting up a project timeline, and at each stage i need the cell to change color depending on input. so for instance stage one happen -50 days. i work the date out need for completing and then the actual date is then put in the cell below which i want to go either

Green because it was done early or on time Amber if it has been done but was late Red if has not been done and the current date is now past the date of completion Stay Blank if it is Blank and their is still time to do it

for the green i am using a A2 <=A1
Amber i am using A2 > A1
for the red i'm using =And(A2<Today(),A1="")

the red one conditioning does not seem to be working and if their is nothing in the cell an its within the time its going green !

View 1 Replies View Related

Conditional Formatting Based Upon Various Dates

Oct 7, 2008

I have a spreadsheet I'm trying to make which will have various cells change color based upon the date entered compared to today's date. Here are the details of it, and what I've tried to do so far:

Various safety tickets that employees acquire have a 3 year expiry date on them. What I want to do is be able to enter in the issue date, and then have the conditional formatting color the cell that contains the date based on the following criteria:

Green = More than 6 months remaining
Yellow = Between 6 months and 30 days remaining
Red = 30 days until expiry

Black = Expired (I wanted to eventually add this one, but Excel 2003 was only allowing me to add 3 conditional formatting conditions, so I just went with the 3 colors. I'm getting Excel 2007 later today, so I might be able to add more with that)

Now here is what I have so far:

3 years = 1095 days, 2.5 years = 913 days, 2 years and 11 months = 1065 days

N3 = 913
N4 = 1065

Conditional Formatting on cell C21

Condition 1 - color Green

=$C21+$N$3>=TODAY()

Condition 2 - color Red

=$C21+$N$4<TODAY()

Condition 3 - color Yellow

=AND($C21+$N$3<TODAY(),$C21+$N$4>TODAY())

View 3 Replies View Related

Conditional Formatting Based On Dates

Jul 25, 2008

I am trying to use conditional formatting to color a cell based on a date. I have a date in cell "a1" as my base date (no formatting needed). I have dates in cells "a5:a10".

I need those dates (a5:a10) to change color based on the time from "a1".

Less than 1 year = no formatting.
Between 1 year and two years = yellow.
Over two years = red.

I have tried different settings / formulas in the conditional formatting option, but I can't make it work. Do I need to use visual basic to do this?

View 9 Replies View Related

Conditional Formatting To Make Dues Dates

May 9, 2014

I look everywhere to find a formula for a conditional formatting to make dues dates.

I would like to know if it's possible to do conditional formatting to make due dates turn green and yellow 90 and 180 days before their dues dates? The reason I am asking this question, it's because the column that where dates are, they are coming from a VLOOKUP formula and it's coming from another workbook.

View 3 Replies View Related

How To Change Conditional Formatting With Formulas Concerning Dates

Jan 19, 2014

In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.

I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again.

Book0.5 template.xlsx

View 3 Replies View Related

Conditional Formatting: Dates Show When On Holiday

Nov 4, 2008

I am creating a register and have got a worksheet with everyone name and dates across the top, the dates are automatically created by entering one date, the dates are weekly. But i want to show when we are on holiday, could i do this by using conditional formatting and if yes, how and if no how else can i do this.

View 3 Replies View Related

Conditional Formatting With Month And 'Dates Occurring'

Dec 9, 2009

I'm trying to keep track of training dates for employees.

I have my spreadsheet conditionally formatted to highlight dates according to months, based on if the training date is due: next month, this month, last month, or 2 months ago; all different colors.

I used "Format only cells that contain" and then used "Dates occurring." The spreadsheet worked perfectly until this month; evidently it doesn't recognize Jan of 2010 being "Next Month," so those cells aren't highlighted. Current formatting is custom, mmm-yy. I tried different date formats but it didn't change anything.

Is there a better way to do this?

View 8 Replies View Related

Conditional Formatting With Forecasts And Actual Dates

Oct 17, 2013

I am trying to create three different conditional formats for my date tracker. I have attached an example of what I am trying to accomplish and tried to write out what the rule's are, just struggling on how to write the formulas.

Condition 1 - If the cell in column C's date is past due, change the cell red only if there isnt a date in the "Actual" column B

Condition 2 - If the cell in column C's date is coming up in the next 30s change the cell to yellow only if there isnt a date in the "Actual" column B.

Condition 3 - If there is a date in column A and Column B turn column C's cell green.

I am using this formula "=+IF(B3="",A3,B3)" to populate Column C.

Conditional Formatting.xlsx

View 1 Replies View Related

Conditional Formatting :: Change Colors As Per Dates

Oct 23, 2009

Im sure conditional formatting is the way forward with this but cant quite get the formula right. I need 2 columns: Column A will have dates that an event happened on. Column B will be blank until we have received back in a letter that went out on the date in column A. At which point the date the letter was received back in will be entered into column B

What I need is for the Colour of Column B to change from green (before and on the date of Column A) to Amber (for the next 3 days) and then red (from Day 5 onwards. This would be until a date was entered in column B (the date the letter is received back in) at which point it would just go back to white. This would keep a check on how long the letter was out for and whether we need to chase it up or not and with an increasing urgency.

View 2 Replies View Related

Conditional Formatting To Show When Two Or More Dates In A Row Of Cells Are The Same

Dec 12, 2008

I want to use conditional formatting to show when to or more dates in a row of cells are the same.

i.e. A B C D E
16 Dec 29 Jun 16 Dec 20 Apr 15 Dec Etc

How do i do it anyone so that it automaticaly changes as the dates change when new data is added?

View 9 Replies View Related

Conditional Formatting More Than 3 Conditions Based On Dates

Feb 8, 2009

I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it).

My conditions are:

1. Turn the cell background colour red (action overdue) if:

Formula Is:=IF(AND(RC[-1]"",RC="",RC[-1]

2. Turn the cell background colour orange (action required) if:

Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]

View 9 Replies View Related

Conditional Formatting Of Dates - Highlighting Of Columns

Jul 11, 2006

Well I spent some time yesterday trying to figure this one out. It was only last week that I have taught myself how to use VBA in excel. However this looks like a though one.

Simply what i would like to do is highlight a column of a certain date (till a certain row number) based on a list of dates.

eg. Across the top I have a set of dates and on another sheet i have a list of dates.

I understand that there is a VBA code for such conditional formatting but i can't get my head around it.

View 9 Replies View Related

Track Annual Inspection Due Dates Using Conditional Formatting?

Dec 7, 2005

I am trying to make a worksheet that will track the annual inspection dates for my equipment and basically change color when the inspections are comming due or are overdue. Im using conditional foramtting what would the formula be if i did an inspection on 6-sep-05 and i want excel to tell me when it is 5 days before the due date which would be 6-sep-06.

View 5 Replies View Related

Conditional Formatting Based On Publication And Review Dates

Feb 23, 2010

I have a spreadsheet that tracks publication dates and review dates. I would like the publication date to change color based upon its relationship with todays date. If the publication date is equal to or older than two years from today turn red, if it is equal to or older than a year and a half from today turn yellow. This seems like a pretty simple formula but I have tried several times and I can't seem to get it to work.

View 3 Replies View Related

Conditional Formatting Colour Change Based On Dates?

Jan 8, 2012

I have a simple due date spread sheet for tasks that need completing at work. I am able to make the 'Due date' cell turn red when it is equal to or less than todays date, using conditional formatting. No problem.

I also have a 'Date closed' cell which is populated with a date when the task is complete.

What I am struggling to do however is change the red 'Due Date' cell to Grey once the 'Date Closed' cell is populated with a date. In fact I would like the whole row to be greyed out once the 'Date Closed' cell is populated.

I also have an 'Open / Closed' cell, which tells me if a task is open or closed, being chosen from a drop down I installed. Could this be used as an alternative to the 'Date Closed' cell. ie If someone selects 'Closed' in cell A4 the whole A row goes grey.

View 4 Replies View Related

Conditional Formatting Based On Dates And Priority Levels

Feb 9, 2013

Each item (row) in my sheet contains a logged date in column A, and a priority Level in Column G - which is via a data validation drop down (1 to 3).

What I would like to do is create a conditional format based on what the priority level is, and whether it has expired its allowed resolution time based on the logged date.

For example a Level 1 priority requires a 1 day fix, a level 2 requires a 5 day fix and a level 3 requires a 10 day fix.

If the priority level is a 1 and the logged date is over 1 day old i need the whole row to change colour. If the priority level is a 2 and the logged date is over 5 days old I need it to change colour, and if the Priority level is a 3 and the logged date is over 10 days old I need it to change colour. The colour can be the same each time, but I just need to know how to get it to trigger.

I think I have used an IF(AND( formula in the past, but cannot remember how to do it.

View 4 Replies View Related

Conditional Formatting- Spreadsheet That Tracks Auditing Dates

Jul 22, 2009

I have a spreadsheet that tracks Auditing Dates. Cell A1 has today's date =Today()

Column B2 has the first Audit Date (hard keyed), cell C2 has the second Audit Date (formula =+B2+182), cell D2 has the third Audit date (formula =+C2+182), etc. . . I would like the format of the Audit dates to flag the last audit date in the row red if it is prior to today's date (cell A1).

View 9 Replies View Related

Conditional Formatting For Relative Range Of Expired Dates

Apr 30, 2008

I am using Excel 2007.

I am using the coditional formating to highlight whether a date contained in particular cell is within 12 months of todays date or over 12 months in the past.

This is done by placing the =TODAY() in a cell at the top of my sheet and then in another cell, doing a DATEDIF calculation between the two and this works fine. I can then specify in my conditional formating to turn the cell containing the date a funny colour if the cell doing the DATEDIF calc is > or < 12. Simple!

What I can't do is copy this formatting for the other 30 rows of dates I have. When I try and copy them, it references the original cell, not the one for that row. Example: If my date I want to test is in B2 and my DATEDIF clac is in U2, when I copy the coditional format to the new row, say B3, the new rule still references U2, not U3. I understand about dymamic and static references and I have removed the '$' signs from the formula.

View 8 Replies View Related

Get Some Specific Dates Highlighted In Excel Columns With Conditional Formatting?

May 20, 2014

How to get "some specific holidays" highlighted by doing conditional formatting in series of "date columns"?? I got to know how to get "sunday" highlighted...but again problem with specific holidays...

View 9 Replies View Related

ADVERTISEMENT