Free Microsoft Excel 2013 Quick Reference

Conditional formatting 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

Hello,

I have a list of expiration dates that i need to flag according to several criteria.

I'd like to use conditional formatting to highlight each of these a different color:
Dates that expire in two months
Dates that expire in three months
Dates that have already expired

I have the last one down but i do not know what formula to input for two or three months in advance.

Any help would be greatly appreciated, thanks.

Hello,

Can someone assist me in writing a formula that will allow me to change the font to red when the current date is 15 days beyond the expiration date, by using conditional formatting.

Thanks,
Saudia

Exp Date
30-Jun-05
8-Jul-05
5-Dec-05

I need a list of 'Coming Events' for a web-site. I have used Excel to create a list in date-order, and saved the result as an 'xml'-file. So far, so good.

However, ideally, I should like to arrange matters so that the web-page automatically deletes (or does not show) events occurring on a date earlier than the current date.

How can I achieve that ?

Hi to all,

I want to ask for help to set up an excel sheet with a formula. The scenario is like this:

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

If the expiration is

I have a chart that displays when tasks have been completed, when they are
due, and at what interval the task is required to be renewed by. My question
is i want the cell of the due date to change to red and for the text
"overdue" to be in yellow, when that due date goes past the interval of when
it was completed by.

If anyone can help me get out of this bind i would appreciate it

Dan

I have a spreadsheet that consist of dates when certifications expire. I am
trying to insert conditional formatting for two conditions. After much
headache I got one condition to work. However the second condition is giving
me a fit now.

This is the first condition which I got to work: I wanted to format the
cell with the date in it with a yellow fill color when I am between six
months and 1 month from expiration. I accomplished this with the following
formula for cell G1:

=AND(TODAY()>G1-184, TODAY()

I have an expiration date that I have conditioned to turn red after the date
with "cell value" "is less than or equal to" =TODAY()
It turns blue if "cell value" "is greater than" =TODAY().

I want it to turn green 90 days before it expires. I tried "cell value"
"between" =TODAY()-1 and =TODAY()

I want to set up CONDITIONAL FORMATTING with Dates.
I want to set it so that if the date value in a cell has passed (expired)
the it will be shaded RED,
If the date value is in the next 14days then it wil be shaded yellow,
If the date value in the cel is greater than 14 away yet less than 30 days
then the shade to be green.
If the date value is > than 30 days from today then the shading to remain
white.

How i have set it up is as follows:

sheet1=input sheet (to enter dates)
sheet2= frequecy values in coresponding cells to sheet1(1,2,3,5 years)
sheet3=shaded sheet. This sheet to display the date values that a

sheet1 input date + sheet2 frequency(yrs)= sheet3 cell value

How do i get the sheet3 dates to be calulated from sheet1 with sheet2 added
to them?

And then to shade them as above??

Regards

Corey

I am wanting to set several columns up to give warnings when a date is near expiration. What I am wanting is if a date is 14 days or less from expiring then I want the cell with the date shaded yellow. If a date is 7 days or less from expiring I want the cell with the date shaded red. If the dates are good for a longer period then these 2 then there will be no shading and if an expired/expiring date is replaced by a good one then that cell will no longer be shaded either. I figure I can achieve this by using conditional formating but am having difficulty in doing so. Can anybody help me out with this dilemma? Thanks in advance.

I have searched everywhere and cannot figure out what I'm missing. I have a column with contract expiration dates.

The neighboring column shows how many days' notice is needed if I want to extend the contract and inform the other party I'm doing so. In this column I would like to make conditional formatting to have a color fill if the current date is within the time period of the extension.

In the attached screenshot, I have the redilivery (expiration) date already formatted to be yellow if within a month of expiring, orange if within a week, red if past date.

I would like the days' notice in column K to also have a color notice. So If I need to send the notice for Row 5 10 days before the date in I5 (Oct 7) I would like a color fill to warn me if I'm within 7 days of that date (Sept 30 and later).

If needed as a reference, here is how I formatted column I. I am only going to show if the expiration date is within 7 days of today's date as the other formats use same code just different number.

Cell Value less than =NOW()+7, then formatted a fill of orange.

Here is what I tried to work in column K for the cell value, but I'm missing something, whether an absolute or relative reference, the order of the data, or what??

Cell Value less than =NOW()+$I1+7, format whatever fill color

I want to set up CONDITIONAL FORMATTING with Dates.
I want to set it so that if the date value in a cell has passed (expired)
the it will be shaded RED,
If the date value is in the next 14days then it wil be shaded yellow,
If the date value in the cel is greater than 14 away yet less than 30 days
then the shade to be green.
If the date value is > than 30 days from today then the shading to remain
white.

How i have set it up is as follows:

sheet1=input sheet (to enter dates)
sheet2= frequecy values in coresponding cells to sheet1(1,2,3,5 years)
sheet3=shaded sheet. This sheet to display the date values that are:

sheet1 input date + sheet2 frequency(yrs)= sheet3 cell value

How do i get the sheet3 dates to be calulated from sheet1 with sheet2 added
to them?

And then to shade them as above??

Regards

Corey

I have a training matrix which has the names of the trainees for the columns.
The rows are used for the subjects.
Some of the training competencies are due for refreshers annually, while some are due for refreshers 3 yearly.
The entries in the colums for each participant against the competencies are recorded on the day of the training.
I want to set up "Conditionally Formatting" as an indicator on who is due for refresher training (30 days - yellow) and who have expired (red).
Ideally, I do not want to set additonal rows or colums for expiry dates, I want to only keep the dates of issue.

Excel 2003

So what I'm currently trying to do now is have a gradual color shift as the Soldier gets closer to their flight physical expiring. As of now I am trying for the Name cell to turn yellow when they are 4 months (120 days out), then slowly turn to orange at 3 months (90 days), and finally hit dark red on the day it expires. Now I tried using the conditional formatting the allowed for a 3 color shift, but it kicks back an error because apparently you cant use relative data in that kind of formatting. the way I was original doing it was something like this:

	VB:
	
color 1:=(TODAY()+30)=$G2:$G80 
color 2:=(TODAY())=$G2:$G80 
color 3:=(TODAY())=$F2:$F80 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a workbook that tracks date of completion of classes. The dates section are conditional formatted to change colors to red if they are out of tolerance, orange if 30 days away from expiring, yellow if 60 days away and return to green if they are in tolerance. I am trying to add a auto count section that shows how many cells are green, red, yellow and red and have a percentage of below that reflecting the total against the total number. Any help would be greatly appreciated.

Probably a silly question but here it goes. I have a spreadsheet with 4 columns. 3 of the columns will have expiration dates (MM/DD/YY format). What I need to do is once an expiration date is entered in one of those cells, when it reaches that date, I would like for that specific cell to show up in red (making it easy to see what has expired and what is still active).

Any help would be appreciated and I know I have to do a conditional formatting formula, but im not quite sure what is included in the formula.

Thanks.

Hey everyone,

This is a two part question for a single project I am working on currently. I have been tasked with compiling a list of agreements which includes the expiry dates of those agreements. What I want to do is conditional format the expiry column so that those agreements which will expire in a month are colored in red (for example) and those agreements which expire in two months are colored in yellow (for example). I don't how to test the dates written in the cells to the current date and set it up as a condition. The other thing I want to do is to display a count of how many of the agreements are expiring in a month, how many in two months, etc. I have attached a sample which illustrates what I am talking about. Any help would be greatly appreciated.

Cheers

I have searched around for a solution to this but haven't got anywhere.

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.

Hope I have made this understandable.

Thank you in advance. :-)

I'm sure this must be fairly straight forward, but try as I might I haven't been able to suss it out.

I have data in columns A to D and a date for chase up in column E. The date may be different on each row.

I would like the cells in columns A1 to D1 to change colour if the date in E1 has expired. However, I only want that row to change colour as the date in E2 may not have expired. Once the date in E2 expires I want that row to change colour and so on.

I would also like it to change a different colour if the date has expired by more than 20 days.

I assume it must be fairly easy by using conditional formatting but I cannot get the formula right. How do I paste the formula to the other rows so that it refers to the cell in column E on that row.

I hopes that this makes sense

Hey all.

Trying to figure out a problem I've "bumped in to...

In B3 I have the =NOW() formula and format in that set to date YYYY-MM-DD.

In F13 I need to use a conditional formatting pending on several criterias:
1, if G13 contains "x" then set to red colored font
2, if H13 contains a date and the date is less then in B3 (has expired) then mark the whole cell red.

I've managed the first one (=G13="x"), but the problem starts with the second option. Cause XL changes the cell to red when the cell doesn't contain any value (date). I've used the formula =H13