Cell Formatting Based On Date

Mar 12, 2014

I work as a full-time lifeguard and I have been asked to modify an existing spreadsheet that we use to keep track of everyone's certifications. The criteria is to adjust the cell color based on whether the certification is current, 45 days from expiration, or expired. I am unsure how to set up the rules in the conditional formatting to differentiate between the three categories. I have the certification dates as well as the duration of the certification so I need to be able to have the cell color change accordingly based on the previously mentioned criteria. This is the formula I am currently using just to tell if the certification has expired:

=IF(ISNUMBER(A49),IF((DATE(YEAR(A49)+2,MONTH(A49),DAY(A49)))

View 3 Replies


ADVERTISEMENT

Conditional Formatting - Change Cell Color Based On Date In Another Cell

Oct 9, 2013

I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.

View 1 Replies View Related

Conditional Formatting Blank Cell Based On Another Cell Date

Apr 1, 2014

I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.

As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow

Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)

KO_04.01.14.xlsx

View 3 Replies View Related

Conditional Formatting Date Cells Based On Entry In Adjacent Cell

Jul 22, 2008

I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.

I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()

All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.

In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.

View 14 Replies View Related

Excel 2002 :: Conditional Formatting Based On Date In Adjacent Cell

May 22, 2012

I have columns of figures with times like 5:52:54 and next to them cells with values that i wish to format based on the time.
that is if the time on the adjacent cell is before 8AM then make the value cell have a red border it does not have to be conditional formatting - Excel 2002 in win7/64

View 9 Replies View Related

Conditional Formatting Based On Date

Feb 12, 2008

I am trying to use conditional formatting to highlight items that are either past due or coming due soon. The data to be evualuated is in a report that I extract from another system and run bi-weekly or monthly. The dates included in the report represent the date in which something was last updated, certified, tested, etc. All dates expire one-year from when they were last completed. The colors that I want are as follows:

If something is past due (date shown is more than 1 year old) or due within 30 days = RED.

If something is due within 31-60 days = YELLOW

If something is due within 61-90 days = GREEN

So, if an item on a report is showing it was "LAST UPDATED" on 2/21/2007 it should show up RED because it is due within the next 30 days.

If an item is not expired (less than 1-year old) and falls outside of the defined 90 day window then it should just remain as is with no formatting.

View 9 Replies View Related

Formatting Range Of Cells Based On A Date

Sep 6, 2013

I have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.

View 4 Replies View Related

Conditional Formatting To A Column Based On Date

Mar 7, 2012

I have a column of cells containing dates. I need to change the colour of any cell which is 7 days older than todays date. I would like to leave the header and any blank cells unchanged. Every formula I've tried has changed the enire column, or if I selected a range, the entire range.

View 5 Replies View Related

Formatting Based On Percentage Complete And Target Date?

Nov 20, 2013

I have a sheet (example is attached) that lists multiple jobs and their related tasks. I want to know if it's possible to create a conditional formatting that says if the percentage complete under the "task" is under 100% and within X number of days of the target date, it's red/yellow/green.

So, if a target date is 70-100 days out; 0%-10% - RED, 11%-45% - YELLOW, 46%-100% - GREEN

If a target date is 40-69 days out; 0%-45% - RED, 46%-75% - YELLOW, 75%-100% - GREEN

If a target date is 20-40 days out; 0%-60% - RED, 61-90% - YELLOW, 91%-100% - GREEN

If a target date is 0-20 days out; 0%-85% - RED, 86%-95% - YELLOW, 96-100% - GREEN

If a target date has passed, Anything under 100% is RED.

View 4 Replies View Related

Conditional Formatting - Colouring Percentage Of Multiple Cells Based On Date Range

Mar 12, 2014

I am trying to get a row of cells to highlight a percentage based on a date range

Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.

In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.

Name Start Date End Date Jan Feb Mar

Dale 11/01/14 26/02/14 21 days 26 days

I have attached the spreadsheet for an example : Book1.xlsx‎

View 3 Replies View Related

Cell Date Formatting: Cell To Display Mm/dd/yyyy And Enter Mm/dd/yy

Aug 13, 2008

I have Excel 2000, recently when I format a cell to display mm/dd/yyyy and enter mm/dd/yy, it is displaying dd/mm/yyyy. Or it starts out correctly and during a future opening of file it displays incorrectly.

View 2 Replies View Related

Cell Formatting - Red Text If Cell Matches Certain Condition Based On Another Cell

Sep 24, 2013

I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)

When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.

I need the text in column AQ (the monetary amount) to become red when the the opposite is true.

i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.

View 2 Replies View Related

Question About Formatting A Cell With A Date

Oct 13, 2008

I have a workbook with 8 sheets within it. I want to be able to change the date on the 1st sheet and have the other 7 just automatically change so I don't have to go to each one. I know it can be done, I just can't get it working.

View 5 Replies View Related

Formatting Date And Time Together In One Cell?

Sep 5, 2013

formatting the date and time together in one cell. I copy the date and time from another program and I'm pasting it into a single cell within Excell. This is was the date and time looks like from the other program. 2013-09-03 03:20:30.0 I need it to look like this in Excel in a single cell. September 3, 2013 3:20 AM

View 4 Replies View Related

Conditional Formatting - Date In Cell

May 13, 2014

I want set a condition in a cell that contains a date , to change to a colour when this date has passed another date by 3 days. E.g. order date received date if the received date has gone over 3 days past the order date i want it to flag up and change colour.

View 3 Replies View Related

VBA To Change Cell Date Formatting

Apr 11, 2007

I got a calendar macro running, the thing is that when the date is selected (and it gets entered into the cell connected to the macro), it needs to format it according to whether the date selected is today's date or not.

If the date selected in the calendar is today's - then it needs to enter dd/mm/yyyy - h:mm AM/PM, etc...

If it is it not today's date, then I only need dd/mm/yyyy to come up on the output cell.

View 9 Replies View Related

Date In Cell - Calculation AND Conditional Formatting

Sep 24, 2011

Short Version: I need to find a way to have the date in a cell be used for both a calculation (one date subtracted from another) AND a conditional format (Cell changes color based on the month). Cell format is mm/dd/yy

Explanation: We enter information for each work order on a row in our Master Work Order List. For each new work order we start a new row at the bottom of the sheet.

We calculate each work order's throughput time by having a formula in column F that subtracts the Start Date in column D from the Complete Date in column E.

F#=E#-D#

All date values are entered as mm/dd/yy and the cell format is mm/dd/yy.

At the end of the month, we put an AVERAGE formula into a cell in column H and, for the sources, use all cells in column F that represent work orders that were completed for THAT month. The problem is not all work orders complete in the order they started and some can start in one month but not complete until 2 months later, so we can't just click-drag a bunch of cells in a column. Sorting doesn't work because after we click and drag a bunch of cells and get the average, when we re-sort back to Sort by Work Order Number, the clicked-cells don't move, but the data does.

We end up just looking up and down column E looking for dates from this month and hope we got them all.

So I thought, "Hey, I know, I'll put conditional formats into column F so that if the date in column E starts with 01 (January), the cell in column F will turn Red, and if it's 02 (February), the cell will turn Orange and so on through the end of the year! That way we could just look for a given color each month and are MUCH less likely to get it wrong!

I added conditional formatting using the LEFT(E#,2) formula, hit [Enter] and... nothing.

I thought, "I"ll set the cell formats to TEXT so the LEFT formula 'sees' the month number!"

So I set the cell formats to TEXT and then the conditional format 'sees' the '01' for January and changes color!!

But now the Throughput Time formula (=E#-D#) doesn't work.

I think it's because Excel can DISPLAY a date as mm/dd/yy (due to Date Format), but it actually calculates date information based on a number that REPRESENTS the mm/dd/yy. I read that Excel 'sees' each date as a number representing time passing, not as "mm/dd/yy" and uses that number for calculations.

SO, is there a way to have a cell's date be 'calculatable' and still have it change color based on the month?

View 5 Replies View Related

Formatting Cell Currencies Based On A Selection In A Single Cell

Jul 28, 2009

I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.

View 9 Replies View Related

Macro To Save File - Save Name From Cell Containing Date Using Different Date Formatting

Aug 15, 2014

I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.

The format of B5 looks like - 13/08/2014 16:39

The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".

The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14

I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.

[Code] ......

View 3 Replies View Related

Formatting Based On Another Cell

Dec 6, 2007

I basically have a spreadsheet with several columns A-E. Column A contains a number from 1-6. I want to format the contents of the cells in column B based upon the number in column A.

An example:

If A3 = 1

Then I want B3 to be - Font size 10, bold, underlined, indented twice.

View 9 Replies View Related

Conditional Formatting - Cell To Highlight Red When Date Expired

Aug 4, 2014

I have a spreadsheet that keeps track of staff training. in colum A is the date they completed the training. this training expires in 5 years time. i would like the cell to highlight red when the date has expired. and when i enter the new date in 5 yrs time the cell to return to no fill color.

View 8 Replies View Related

Conditional Formatting On Cell Based On Value

Jul 29, 2014

In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.

View 8 Replies View Related

Conditional Formatting Based On A Value In A Different Cell?

Apr 9, 2014

I am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.

View 13 Replies View Related

Conditional Formatting Of A Row Based On A Value In A Cell Of That Row

Feb 17, 2010

I want to create a conditional formatting based on a cell in a row. If the value of that cell is greater than or equal to $20 the entire row would be highlighted with a color.

the main thing is that the cells in the rows have if formulas that supple the data for the cells. The cells will be blank if nothing is to be displayed but they will still contain the formula.

View 12 Replies View Related

Conditional Formatting Based On Value Of Another Cell

Jun 4, 2012

I want to format a cell conditional on the value of another cell. I have read numerous threads on the topic and no matter what I do, it doesn't seem to work for me. I've made it a simple as possible by creating a new workbook and entering:

A1: 5
B1: 2
C1: Some random text - this cell has the conditional formula: ="A1>B1"

However, the cell doesn't get formatted. Here is a screenshot and here is the simple workbook.

View 7 Replies View Related

Conditional Formatting Based On % In Other Cell

Feb 15, 2013

I want to color format a cell based on a percentage value in another cell but I just can't get my head around how to solve it. I've attached a file that explains it better.

E.g.
The formatting for A1 should be something like:
If A2 is
> +/- 10% RED
+/- 6-10% YELLOW
+/- 5% GREEN

Attachment 214464

View 6 Replies View Related

Conditional Formatting - One Cell Based On The Value Of Another

Jan 6, 2014

I am attempting to apply conditional formatting such that cell B3 is green if it is greater than or equal to cell B2, and red if less than cell B2. Then to apply this conditioning across the row of data (i.e. C3 >= C2 green, C3 < C2 red; D3 >= D2 green, D3 < D2 red; E3 >= E2 green, E3 < E2 red ... etc.). Needs to be formatted such that if I change the value in cell B2, the colour of B3 is automatically updated.

View 4 Replies View Related

Formatting Based On Cell In Another Sheet?

Nov 7, 2013

I have a worksheet with a list of four digit codes that are colored. On a separate worksheet (in the same workbook,) I would like to be able to type the four digit code in, and have it return the color I have the cell turned. E.g., I have a cell with 8810 colored green on one sheet, and I need to be able to type that code on another sheet and have the cell turn green. Is this possible? The codes are all random, with three different colors to choose from, so conditional formatting is probably not the best solution.

View 5 Replies View Related

Conditional Formatting Based On Cell Value

Apr 17, 2014

I am manually formatting column J depending on what data is entered into column K. If the number entered into the cells in column K are equal to or less than what is in column J, I format the data in the cells in column J with the green you see above. If the data entered in the column K cells are greater than column J, I format it in the red shown above.

So, how can I have Excel do this for me, format column J cells depending upon what is entered into column K cells? The caveat here is that I also don't want any formatting to occur until data is entered into column K.

View 5 Replies View Related

Formatting Range Based On First Cell

Jan 26, 2007

I'm trying to format a range of cells based on a single cell. Below is the code that
I am using:

Private Sub Worksheet_Change(ByVal Target As Range) ...

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved