Alerts About Approaching Due Dates

Salutations

I'm trying to get an active calendar to work to show the dates when to expect a package for our customers. The plan is for today's date(processing date) to highlight a cell in blue, the next day (shipping date) to be highlighted in yellow, for 3 weeks after the shipping date to be highlighted in red, and last for 6 weeks after the shipping date to be highlighted in green. The dates highlighted should automatically shift as a new day goes by to the next day on the calendar. Unfortunately for me, every fiscal year, the calendar would need to be manually updated with the new year, which is why I changed the text that was given to me in the file's template to day functions (i.e. =DAY(43397)) because I figured this would help me better when coming up with the conditional formatting. Below is what I have so far, and I can get one day, but not the range of dates. I run into the issue of when a date goes into a new month and keeping previous dates from being highlighted. I could use some advice on how to tackle this, as I'm not given a list of dates, rather dates to visually appear as if they are on a calendar. In the meantime, I've been manually updating the calendar by highlighting the appropriate cells the start of each workday. There is a way to automate this, you think?

(see Figure 1 below)

Formula in A2: =IF(TODAY()-A1<=90,"No","Yes")

Apply two format rules to A2

(see Figure 1 below)

I’m trying to set a service due sheet, i need the date in A1 to calculate that 90 days have passed as of the current date and highlight A2 RED YES and if not highlight GREEN NO, can someone suggest a formula, Cheers.

Hi. I'm trying to find out if it is at all possible to use a formula to pull data from a pivot table showing orders that are overdue and due today.
I have done the conditional formatting but they are saying that they still then see all the orders rather than the ones that need to be chased only.
(see Figure 1 below)

Is there a way to stop the conditional highlighting once a task has been completed? For example, I'm using this rule to show dates that reports are due. Right now, all passed dates are red, but I only want to highlight the ones that are outstanding and so are overdue. Is there a way to way to make the conditional formatting turn, say green, if a report has been received? I'm imagining added in a column with received yes/no and then a formula for the date cell with something like if other cell= yes, then format green?

Mar

It usually helps if you include more details, say a picture or text showing your data. The following shows a knocked up version that you might give you some ideas.

For some reason I could not upload the Pictures. So here it is in text. Select each data set into a blank sheet and use text to Columns to split it if necessary.

This is the summary sheet

Project Due Date "Incomplete
%" Days Left
Project 1 15/2/2018 50% 16
Project 2 16/2/2018 88% 30
Project 3 17/2/2018 0% 0
Project 4 18/2/2018 63% 28
Project 5 19/2/2018 83% 16
Project 6 20/2/2018 100% NS

Formula in D3 is
=IF(COUNT(ProjBreak!C3:J3)=0,"NS",B2-MAX(ProjBreak!C3:J3))

The details sheet show the date an operation is completed. If a project operation has not been started then it has to be shown by some text. NS shows the op has not been started and End shows the last op. So if the biggest project has 230 operations then this is the range for every project but the end might be after operation 5.
Details Dates Completed
Project % complete OP01 OP02 OP03 OP04 OP05 OP06 OP07 OP08
Project 1 50% 16/1/2018 23/1/2018 30/1/2018 NS NS End
Project 2 13% 17/1/2018 S S S NS NS NS End
Project 3 100% 18/1/2018 23/1/2018 28/1/2018 02/2/2018 07/2/2018 12/2/2018 17/2/2018
Project 4 38% 17/12/2017 07/1/2018 21/1/2018 ns ns ns ns End
Project 5 17% 03/2/2018 S NS NS NS End
Project 6 0% ns ns ns ns ns ns End

The formula in B3 is =IFERROR(COUNT(C3:J3)/COUNTA(C3:J3),"")

Hello,

I have two worksheets. First sheet has a list of projects and due dates. The second sheet has a break down of each project and a percentage of how much is completed.

what i would like to know is. is there any way to link the the due dates with the percentages?

i would like the due dates cleared once projects reach 100% complete.

SEArs

See Barry's tip below; when yu click the format button you have the option to change the cell formulas

Hi This was very Helpful but i was wondering if there is any way of actually making the whole cell red or blue??

Hi Allen,

I have a spread sheet (excel 2016) to track certificates. The expiry dates are input in columns C3 to C69. Please may you help me to to set up the following:

1. The dates to automatically order themselves soonest to latest when a new expiry date is input. I would like the soonest expiry at the top of the list in C3 through to the furthest away expiry which will end up in C69.

2. The dates to auto color code when the dates change - eg within 60 days of today = red fill, within 90 days = orange fill and any date more than 90 days away from todays date to be filled green.

3. If possible - an email to be sent out to two recipients when the date falls within 60 days and an email to be sent out to two recipients when an individual cell date has been changed to more than 90 days from todays date.

Any assistance to the above would be greatly appreciated.

Thanks, Bryce.

NEED YOUR HELP PLEASE.
How can I highlight the data that will be expire the next four months? I only have to enter the date once, and after four months, the data will automatically be highlighted into red. How can I do this? Looking forward for your response. Thank you.

Allen,

How would I accomplish this if I wanted the excel document to perform the alert in one and two year intervals across two columns? For example, column D (Starting at D5) would be an in-processing date and column E would be due date (which would need the alert). The in-processing date would change periodically. (see Figure 1 below)

I will enter data in every 10 days.I want to set upcoming specified date after no of day.if i enter data every day then this formula can be work=TODAY()+B1 where B1 is Project duration.


For an example:- Today date is 04/07/2017, data enter date also same 04/07/2017.

date Project duration in day
28/06/2017 17
30/06/2017 5
01/07/2017 3
04/07/2017 2

@David,

First of all set the fill colour of one of your cells to containing the lease expiry date to green (this the default colour if no conditions are met).

Then create the following conditional formatting rules as follows: (I've done this for cell D2)
(see Figure 1 below) (see Figure 2 below)

Check the rule in the "Manage Rules" dialogue that they are in the right order and the "Stop if Ture" flag is checked.
(see Figure 3 below)

Then use the Format Painter to copy the formatting to the other cells containing Lease Expiry Dates.

I have a similar issue.
I would like to use conditional formatting to do the following:
Lease expiration date is in a cell. I would like any date that more than 90 days away to be green. Any date that is between 61and 90 to be yellow and anything that is 60 days or less including any date past the expiration to be red.
I have tried conditional but regardless of order I can't get them to work.
Could really use assistance. We have about 20 leases all with varying expiration dates.
Thank you,
David.

@C, @Debra
I suggest you replace the "Today()" in the formula with a cell reference "A1", say. Then in that cell (A1), you can put whatever kind of value that you want (preferably a date) - if you just put it as =TODAY() then you get the same functionality. If you use" =Today()-30" then the formula will be TRUE 30 days before the due date, "=TODAY()-7" will be TRUE a week before the due date.

Getting an exact number of months is slightly trickier but solved using the "EDATE" function so 2 months early would be "=EDATE(TODAY(),-2). Note: EDATE will return a value that is the highest in that month if the day is 29/30/31 and there are not that numbers of days in the target month. Also, some older versions of Excel do not have the EDATE function available (or only in an add-in)

Hello, I would like to set up a spreadsheet that will tell me when to notify my client when a training session is coming up such as specific dates (maybe 1 year or 2 years from today). for instance I want to notify them on 7/1/2018 (or whatever date I choose) that we have the upcoming class in 3 months. What formula would I use?

Thank you,
Debra Gill

How do I do this for a specific date and not necessarily TODAY? My employees take training on different dates and I want to format a cell to fill in red if they're overdue.

@Heath
As an example, let's say you have a date in cell Z2 and you want the row to highlight when this date has past.
1. Select the whole row
2. open the conditional formatting dialogue box (as shown above)
3. select "Use a formula to determine which cells to format"
4. in the formula box enter "=Today()>$Z2" (without the quote marks)
5. set the format you want when this is TRUE
6. Click OK

The critical entry is the "$" in step 4 - this is an absolute reference so that all cells in the row will reference that value in order to determine whether to action the conditional format or not.
I've left the row reference as relative so that if you copy the format down Excel will adjust the conditional formatting accordingly so that the reference in row 3 will be $Z3, row 4 will be $Z4, etc. etc.

how can I get the entire row to be highlighted instead of just the column with the dates in them?

@Kenneth

The instructions in this tips and the comments below will do this for you.

am dealing on vehicle licencing, i want to create a spreed sheet that will alert me one month to the expiration date. thanks

@Enid

See my posted dated 15 April 2016 just change the colour to suit.

Hello!
I work with policies and I am trying to set up some color indicators to show the status for their next scheduled review date. We are supposed to review these policies every 3 years.

Green color indicator - for up-to-date policies; 90 days or more away from the next scheduled review date.
Yellow color indicator - for policies that are 60 days or less away from the next scheduled review date.
Red color indicator - for past due policies or at least 30 days or less away from the next scheduled review date.

What are the formulas/conditions that I need to incorporate in my spreadsheet in order to accomplish this?

For example:
The next scheduled review date for "X" policy was 2/20/17, so its past due already. The color indicator that needs to be shown is red in this case. (Note: The previous approval date was 2/20/14) What formula should I use? I am assuming that I need at least 3 formulas to cover the 3 conditions above. Any help would be appreciate your help. Thank you.

I also want to know that expired dated and dates withen 7 days appears on top of the spreedsheet.
can anyone help.

@Khaled,

See the spreadsheet that I sent to you.

Thanks Mr. Barry

i created excel sheet to follow up the work

column B for date of receiving the submittal
column C the due date for this submittal
column D the finished date for this submittal from my side


i want set conditions on Colum E as per the following

white color if (still I have time to reach the deadline) or (i have finished the submittal before the due date as per column D)

Yellow color if (the deadline will be within 2 days )
Red color if (if I missed the deadline)

Can you help me to set the conditions

Regards

I am using 2013 excel spreadsheet and want to put alerts in a column for up coming due dates. Red for 15 days out and yellow for 15-30 days out. Is this possible?

Hi h r u please help me, i am working in a housing society which sell plots on installment. for example a customer who pay installment on 11-12-16 and his due date is 11-1-17 so what formula i enter to get a alerts before 5 days to due date. please help me in this.

Try this, Brittany:

http://excelribbon.tips.net/T000474

-Allen

I would like to know if there is a way to receive an email when the sheet has a due date that is approaching. I am trying to make an excel sheet of all employee documents that need to be updated for our business. If I could receive an email rather than try to remember to check the sheet regularly that would be amazing!

I hope someone can help me with this!

@Jason

In the example below just use an absolute reference in the formula for the conditional format i.e. change J2 to $J$2 then copy the format to the rst of the column.

Thank you, this has been of great help. Does anyone know if there is away to make the effected area "The cells that are highlighted" different.

Currently with the formula above only the date has the formatting that I need. I need all of the cells in the column to be affected, if the date is within the 7 day due date.

Any help would be much appreciated.

Best Regards,

Jason

@David,

In my macros for flashing you simply set the colour you want when setting up the conditional format, as you would normally do when setting a conditional format.

Thank you just I wanted.
Question, is there an easy way to change the colour?

@Farhan

Use the formula "=J2-TODAY()" to get rid of the "-".
Note: that once the number of days gets to zero it will then show a negative number of days again.

Excel doesn't have an inbuilt way of flashing a cell colour or any other format. This can be done easily with a simple macro or three.

In a code "Module":

Public HighlightTime As Date

Sub RefreshHighlight()

If ThisWorkbook.Names("Flash") = "=1" Then
ThisWorkbook.Names("Flash").Value = "=0"
Else
ThisWorkbook.Names("Flash").Value = "=1"
End If

HighlightTime = Now + TimeValue("00:00:01") 'SetTime for +1 secs
Application.OnTime HighlightTime, "RefreshHighLight"
End Sub

and on the "ThisWorkbook" codepage:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime HighlightTime, "RefreshHighLight", , False
End Sub

Private Sub Workbook_Open()
ThisWorkbook.Names.Add "Flash","0"
RefreshHighlight
End Sub

Then set up your conditional formatting for cell J2 using the formula:
"=AND(Flash=1, J2<=30)" and set your fill colour to Red.
"=AND(Flash=1, J2<=60)" and set your fill colour to Yellow.
Note the order of the conditions is important the test for red must the first in the list.

Can you help me to get flash alert with red color when the days remain less than 30, yellow color when the days remain less than 60.

With flash.

@Barry the formula you ask me to put in cell that is already in my sheet but the problem is, it is showing the remaining days but it's shows in -40 days remaining.
e.g I need remaining days from this two dates as Start date 1-Apr-16 End Date 31-Mar-17 & it showing -148 days remaining.
So my question is why it's showing in -

Please help

Thanks

@Farhan,

This doesn't require any conditional formatting it is just a simple formula.

Say your Expiry Date is in cell I2 then in cell I2 put the formula =Today()-J2 format the cell as a number with no decimal places.

Note the value of "TODAY()" will only be updated when the worksheet recalculates so if left overnight the result in J2 will not have decremented. It will recalculate whenever the workbook is re-opened or you can press F9. If this is still not satisfactory a small macro can do this for you.

@Mark,

Assuming you have column headers, select cell H2 and then under Conditional Formating choose "Use a formula...." put =AND(H2=TODAY(),B2=0) then select the formatting you want.

Select cell I2 and then under Conditional Formating choose "Use a formula...." put =AND(I2=TODAY(),B2<1) then select the formatting you want.

Use the format painter to copy this formatting down to all the cells you require.

You specifically said the highlighting to be visible on the specific day only so the "start" cells will not be highlighted if B2 is still 0% the day after it was due to start, this can be easily accommodated by changing the formula from equal to today, =TODAY(), to less than or equal to today, <=TODAY(). You can do a similar thing for the conditional formatting in cell I2. This would be better in the real in case some doesn't for whatever reason check this on the designated days.You could also introduce further conditional formatting if, say, a project is more than 7 days late starting the cell fill colour turns RED, and likewise, if a project isn't completed 7 days after the designated completion date. Just create a new format use the same formula except substitute TODAY() with TODAY()+7.

I have a sheet in which I want to set a formula which turn into alert & start showing the days remaining.

e.g - I have a start date in column H & Expire date in column I so I need the formula in column J to show the remaining days & number of remaining days should also reduce automatically everyday.

Please help

Hi,
I have start dates in column H and end dates in column I, and in column B %value.
I would like to format column H dates to orange text if column B % value is 0% when column H date is the same as todays date.
And column I dates to red text if column B % value is not 100% when column I date is the same as todays date.
Is this possible in conditional formatting?

@ridzuan

Change: =TODAY()
by: =DATE(year,month,day)

This tip very helpful, but I have one question, in the formula we are using "today" do I have any choice to set or change "today" to specific date? and based on that specific date I want to monitor the due date for 3,2 and 1 months?

Hi all,

I am currently working on composing a spreadsheet for work that will provide me with alerts for approaching deadlines - 10 days after an input date, and 48 hours before the deadline (the 10 days). While I have attempted several different formulas, none of them are quite giving me what I want, which is:


>>To provide me with a color coded alert (yellow) when the 48 hour (before deadline is up) is met. This would need to be Mon-Fri only (workdays)

>>To provide me with a color coded alert (red) when the 10 day deadline (this would need to count the day of the date - so, for instance, if I issued a notice to a client on 1/1/2016, I would also include 1/1/2016 in my count of 10 days.. so the deadline would be 1/10/2016).

Can someone please assist me with this issue? All suggestions are definitely welcome.

Thanks much!

I have a column "E" which is the current Calibratin dates and need to get Column "F" Dates to change color when the date is within 10 daysof due and change again within 5 days of being due. Can you please help.

I have a problem in a work sheet that records competency dates for workers
I would like individual cells in two separate columns to change colour if a date is entered in to a third cell (C1)either green for somebody that is in date, for example either to turn green (B2) for somebody that is in date within 6 months plus or minus of an entered date or to turn red (A2) if they are out of date if over 6 months plus or minus from that same entered date - this probably very simple but its got me baffled. Can you help please.

I am trying to do this but I want the color coding system to work only if there is "Open" in the same row in column "A"

@Kam

See my comments on 18th May 2016 and 15th April 2016

hi, i want to make alert in excel for the date, like if i start working today, next year two months prior the colour of that cell where i put todays start date will change to amber so that i will come to know that my training is due in two months and after two months its colour change into red that training is overdue now.
could you please help me how can i do in excel.

Hi there,

I have a large Google Sheet doc that details my inventory and my sales (two seperate tabs, amongst many others). Is there a way to code a "red flag" if I haven't received a sale within 4 weeks of that particular inventory line v's today's date, an "orange flag" if not within 2 weeks, and a green flag if i've detailed a sale within the last week?

thanks in advance!

Alastair

@Jefferi A.

This is trickier than it first seems as Excel will parse your entry and try to "fit" it into one of its data types depending upon the formatting of the cell. How it tries to interpret the input is also dependent on your regional settings. "3/17" with regional settings set to "DMY" will correctly enter this as "Mar-17" (actually 1 Mar 2017) but if set to "MDY" the date will be "17-Mar" (actually 17 Mar 2016) if the cell is formatted as General or as a Date. In both cases the inputted data is converted into a date i.e. the cell no longer contains the entry "3/17". This happens before a macro can intercept the entry.

I would suggest that the cells that have these entries are pre-formatted as "text" (or precede the entry with an apostrophe), which stops Excel from parsing the entry, then use a macro to validate the input, interpret the input into a full date, format the cell as a Date using "mmm yy" (so that Excel will parse the revised entry and convert it to a date serial number) and then write the revised entry back to the cell.

Thank you for the tip - it was very helpful. I am dealing with month and year only and would like to know how I can get excel to accept an entry of 3/17 as March 2017 and NOT March 17, 2017

@ Jacob,

The first (and highest priority) condition is that which turns the cell RED is given when this equation is TRUE "=EDATE($C5,D$2)<=TODAY()", the second (and lower priority) condition is, when the cell turns YELLOW, occurs when this equation is TRUE "=EDATE($C5,D$2)<=TODAY()"

The test file which you can download from http://bit.ly/1U8Gdde should do what you want.

Hi,
I am trying to set conditional formatting on cells c5 to c13. Each of these cells will have different dates in the format of DD/MM/YYYY.

I want to set the period of expiry (in months - RED) in cell D2 (i.e. 24) and set the reminder period (i.e. when it turns YELLOW) in cell E2 (i.e. 2).

I have tried different ways suggested on other sites and also some from here and none are really working for me.

Could you please help.

@faz

See my response to Stefan on 15th April 2016, but just omit condition 3.

im trying to make those coming due in a month to show in red and those that are coming due in 2months time to be shown in orange. what do i do?

@Joy,

To send emails you will have to resort to macros/VBA, this not difficult but the actual macro depends on the email client you have/want to use.

However, this is only part of the solution. You then have to decide how often you want emails sent e.g. every time the spreadsheet is opened? once only?, weekly?, daily?, hourly?. And then what action stops them being sent.

Then what happens if no one opens the spreadsheet for a few days?

I have several spreadsheets which use a dedicated Gmail account to sent out emails automatically. I use the Windows task scheduler to automatically run them at night some on a daily basis others alternate days, and another fortnight. The Task Scheduler is configured to determine periodicity. After running the spreadsheet then closes itself automatically. I use command line switches to indicate to the macro whether it is just me opening the spreadsheet to work on it or whether it is a scheduled running of the sheet. Even this is not perfect as the macro won't run if the particular spreadsheet is already open at the time (I've a solution to this but as yet not implemented).

Some elements like the actual sending of emails is generic, but much of the rest of the current code is customised to the applications themselves.My new solution when I get round to coding it will be a bit more generic.

As you'll gather it's a non-trivial task, another contributor to this forum suggests an app called AlladinPRO (http://aladdinpro.com) but I've not looked at this myself but it is a paid for service.

Ok I figured out the examples above but how do a manage a rule that then emails me or email address in B3 to alert them to site spreadsheet?

@Stefan,

You need to set-up three conditional formats in this priority order and with the "Stop if True" flag set. The normal background of the cell should be set to Green.

1. Formula "=Edate(C2,12)-1-Today()<30" format background as Red
2. Formula "=Edate(C2,12)-1-Today()<60" format background as Orange
3. Formula "=Edate(C2,12)-1-Today()<90" format background as White

@Vince

I've assumed a reference date is held in cell A1. Set the normal background to white or None, then create a conditional format with the formula which requires two conditions the first condition is that the reference date is less than 7 days away, and a second conditional which is that the reference date hasn't past

"=AND(A1-Today()<=7,A1-Today()>=0)" and set the format to be a Blue background.

I hope this helps

what if you want the second one "blue" to be today-7 so if today was 4/14 the blue would show up from 4/8-4/14

I am trying to color-code reports in excel.

i.e. Report date in CELL C2 is 10 Nov. 2015, the report is due again 364 days later(09 Nov. 2016). How do I turn the background of cell C2 (Green) until todays date reaches <90 days till the due date, Orange at <60 days and Red at <30 days?

Thanks in advance for any help.

Very helpful, thank-you

@Colin,

The way to achieve what you want is to add another condition to the formula to test if the cell is blank or not.

For example, the formula given earlier "=A1<=EOMONTH(TODAY(),-1)" would become

"=AND(A1<=EOMONTH(TODAY(),-1),A1<>"")"
this requires the original condition to be met AND for the cell not to be blank.

Thank you Allen, it turned out very well for me, much appreciated.

Guys, can someone help me with the alert formula for tracking the registration of vehicles eg its expiring on the 5th of may 2016

If you are not entering a text into the field how do you stop this highlighting as overdue (I.E Red), would like this to remain blank, would you have to input another rule to override this until you put the date in?

any help would be appreciated.

@Felicia

If you have a date in a cell and you want to test if it is before the current month then you have to test if it is before the first day of the current month. For example, assuming the date you want to test is in Cell A1:
"=A1<=EOMONTH(TODAY(),-1)"

@Lids,

What you are asking for is quite complex, and you do not specify the location of the "empty" cells relative to the cells with the dates in.

The way I suggest you tackle this, as editing conditional formatting formulas can be tricky, is just to write the formula in an used adjacent cell. I would also use an unused cell to contain the current date (using the "Today()" function), which can be overwritten for testing purposes, and then hidden in the final version.

The results of the formulas (you'll need one formula for each required formatting outcome) should be TRUE or FALSE. Once these formulas are working they can then be copied into the formula box in the Conditional Formatting with the appropriate formatting.

For instance for cell C33: for it to turn green the Year and Month must be the same as todays date so the formula "=AND(MONTH($B$1)=MONTH(C33),YEAR($B$1)=YEAR(C33))" assuming todays date was located in cell B1.

If the "empty cell" related to cell C33 is cell C34 (the one below it) then for it to turn yellow you need the following condition (formula): "=AND(C34="",DAY($B$1)>=$D$1,MONTH($B$1)=MONTH(C33),YEAR($B$1)=YEAR(C33))".
Note it includes the same condition for cell C33 as it must be green also plus a check if cell 34 is blank, and the days of the current month is greater than or equal to 25. Cell D1 needs a formula to calculate the day in the month which is 7 days before the end of the month (as each month can be different lengths).
Note that the conditions that you've listed have to be expanded to a full set of conditions which include those related to the corresponding date cell.

Good evening,

We have training due every month during the year. Lets say assuming the begin date is 1/1/2016

I am having a hard time getting the CF to work with the following rules:

The cell ranges I am attempting to format for January 2016 are as follows:

C33-E33
C40-L40 & N40-R40
F32-L32 & N32-R32

If any dates entered are between 1/1/2016 0 1/31/2016 the cell will automatically turn green indicating the training is current for the desired month.

On 1/25/2016 all empty cells should automatically turn yellow indicating a 7 day window to expiration

on 2/1/2016 all empty cells including the previously turned yellow ones will automatically turn red indicating the training for the previous month (in this case January) was not completed.

Now if a date is entered into the empty cells during the 7 day window (yellow cells), it will turn green for completion.

If a date is entered after the cell turns red, it should turn orange indicating the training was completed but late.

This is needed for each month training is due in order to track who is completed, a 7 day warning indicator, expired, and always late.

I don't have a clue about CF and I have reading up on the MS site and looking at other sites to get a clear understanding. Sadly, I am still lost with the complex formats.

Thanking all in advance for your timely responses into this matter.

How can i writie this conditional formatting based on 'CURRENTMONTH' instead of TODAY, and how do i exclude blank cells from formatting selected for <CURRENTMONTH

plz send me fees due date excel file with formula

I have a slightly more complicated formula I need to find.
I need to change the colour of a column containing the date dependant on the priority of the job requested. There are four priorities.
How would I do this>?

=IF(F3<(TODAY()+7),"<<<","")
The date format of the today() inside the function is in the format d/mm/yyyy in my excel 2013
I have noticed that the function fails if the date format in F3 is different to the date format inside the function.

I need the due date in F3 is of the format dd.mm.yyyy

I have spent a lot of time. Please assist. Thanks to all

Hi,

Thank you so much for information you have already provided on the page.

We currently have a spread sheet which contains all jobs we have. At the moment we have a colour code but we fill this individually across the row (Yellow = Open Job, No colour = Completed job, Blue = Quotes).
We are currently trying to track if a advisor is running out of time or has missed deadline.

I have set up for when they have 7 days left the Deadline Column turns Orange and if they have missed the deadline it turns red.

My problem is that when the job is close we need all colour removing from the Deadline Column.

Looking at other comments on the page I know this could be possible as when the job is closed we add the date to the next column (Date Closed).

How do I add a formula which when the Date Closer column is filled it removes the colour from the Deadline column.

Thank you so much for any help.

Jane

@MR

You can make a condition as simple or as complex as you wish, when you select the "Use a formula to determine which cells to format" option when creating a conditional format.

You can use the same rules regarding absolute vs relative addressing as you would in a normal formula.

Here's a formula that I use in a spreadsheet that tracks overdue invoices:

=AND(NOW()>=$B1+PaymentLate,$L1="",$B1<>"")

The critical element is the AND function which has three terms the first actually determines if the invoice is overdue, the second if the invoice has been paid (column "L" has the date the invoice was paid), and the 3rd term just tests if there is an entry in that row at all (column B is the Invoice number), as the formatting is applied to the whole worksheet.The "PaymentLate" is just a named cell with the number of days overdue that I want the row to be highlighted, its value is typically 14. When all the terms are True the row turns orange.

I also use a virtually identical formula for Invoice that are very overdue (PaymentLate becomes PaymentOverdue), PaymentOverdue has a value of 30. In this case the formatting turns the row red.

Hi, this formula is working only for 1 cell, how can I get for all the required cells

You mentioned preferring to use an additional cell to record the completion date and use the presence of a date in this additional cell to conditionally format the original cell.

How exactly do you accomplish this?

@Jonna

What you want to do is quite easy but it depends on how you indicate when an item is complete. I use this a lot to track when invoices which are about to become due, or are overdue, or have been paid. But I do use a different column to show the date paid (you can use the value in another cell to determine conditionally the formatting of my target cell it doesn't have to be solely dependent on that cells own contents - I use this to highlight a whole row of data based on the values of two different cells in that row).

The tip itself shows how you can track upcoming and overdue dates, to show a completed item you need to create another condition as outlined in steps 4-12 above. Depending on how you indicate an item is complete may mean you lose the original date information or not record the completion date (which may or may not be important to your application). Whichever way you chose you need to make it the first rule (by using the up arrows in the rules manager), and ensure the "Stop if True" check box is checked.

For instance
a). if you overwrote the date with the word "Completed" the condition would be ="Completed" but you would lose the original date information.
b). another way which preserves the date information is to add 0.5 to the date and to detect this condition (this relies on the fact that dates are stored by Excel as whole integer numbers and times the decimal portion adding 0.5 adds 12 hours to the date so doesn't effect the date as such assuming you are not using the time). To use this method at step 6 select "Use a formula to determine which cells to format" and use the formula =Mod(A1,1)>0.25 (substitute your actual cell address for A1), skip step 6 and set your formatting as required.
c). as mentioned above I, myself, prefer to use an additional cell to record the completion date and use the presence of a date in this additional cell to conditionally format the original cell.

Whichever way you chose you need to make it the first rule (by using the up arrows in the rules manager), and ensure the "Stop if True" check box is checked.

I am also new to Excel and am trying to create a spreadsheet that will allow me to track upcoming, overdue and completed dates all within one cell, is that possible?? I was hoping to have the cell show yellow when due within 14 days, red when overdue and green when the item had been completed. I am able to get the yellow/red alerts but how can I get it to turn green when i enter a date in the cell when I complete it??

@mctaggart

Just follow the steps in the Tip but in step 8 use "=Today()-730" and in step 17 use "=Today()-700".

The 730 is 2 years hence (2 x 365) and the 700 is 30 days before.

NB this doesn't take into account leap years although the formulas can be made a little more complex to take this into account.

I would, for flexibility, always recommend have an additional column with the actual expiry date of the certification, and use that as the basis of the calculation.

I am very new to Microsoft excel and I was wondering what the formula date is for renewal dates. I'm making a spreadsheet for employees that need updated certifications. So if an employee had a certification done on 1/1/2015 and that certification needs to be updated in 2 years how do I set the cells to a different color (RED) showing that their certification soon needs to be updated?

@Yian
Just implement this tip!!

Hi, I am desperately trying to set up a spreadsheet which will alert me/ flag up when a customers invoice is 30 days overdue. Any help will be greatly (GREATLY) appreciated!!

Many Thanks

hi,

we keep a list of computers we have in the company which includes the software that is installed on it.

i was wondering if there was a way to set up alerts when the user or status of a computer has changed so that the software of that computer can be reactivated on a different computer.

@Abdullah

Not sure what you want, will this do?

Date DueDate Prepare
01/04/2014 01/03/2015 Alert
06/04/2014 06/03/2015 Alert
11/04/2014 11/03/2015 Alert
16/04/2014 16/03/2015 Alert
21/04/2014 21/03/2015 Alert
26/04/2014 26/03/2015 Alert
01/05/2014 01/04/2015 Alert
06/05/2014 06/04/2015 Alert
11/05/2014 11/04/2015 Alert
16/05/2014 16/04/2015 Alert
21/05/2014 21/04/2015
26/05/2014 26/04/2015
31/05/2014 30/04/2015

Formula in B2 =EDATE(A2,11)
Formula in C2 =IF(TODAY()>B2,"Alert","")

Thanks for help...
I have three columns of data..
I want to create new list from A column with conditional of column B due date with a month of 2015 year. I,e. I want a list of all items in column A which will due with a month in column B from 1/1/2015 to 31/1/2/2015.

Which formulas I can use to generate the new list of items

Trey, it would appear to me, by looking at the formula, you can make multiple rules per column. For example, you said you had certificates coming up in two years, adjust the "7" that is used in the formula to show "730"...as that will be the countdown date from that date (adjust accordingly). Also,keep in mind, the formula uses the date derived from the PC you are on so, if the date is not accurate on the PC, then the formula/spreadsheet will also be incorrect.

As per the original instructions, create rules for a "Green" date (1 year away), a "Yellow" date for 8 months away and a "Red" rule for 6 months away. Obviously you will need to find the amount of days each of those values would represent. I.e. 6-months is 180 days, 12 months is 365 days,, etc.

Thanks for the tip. Of course now I would like to set up outloook alerts for these new rules

same question as Carlos can also be integrated with outlook, so that it actually sends email alerts?

I am keen interested in excel

Hi, I just wanted to know if once the approaching alert has been written in Excel, it can also be integrated with outlook, so that it actually sends email alerts?
thanks.

I am very new to Microsoft excel and I was wondering what the formula date is for renewal dates. I'm making a spreadsheet for employees that need updated certifications. So if an employee had a certification done on 1/1/2015 and that certification needs to be updated in 2 years how do I set the cells to a different color (RED) showing that their certification soon needs to be updated?

I sincerely appreciate your support.
You don't believe how you make my job easy.

Great ....Great....Great!

I am trying to set up an excel sheet for my work with annual appraisal, how can I get an alert when it has been 12 months since the persons last appraisal?

Thanks

@Barry @PinkFog

One more thing... when setting the new criteria, not only should it be at the top of the list but the "Stop If true" should be checked.

@Pinkfog

Change the conditional formatting on the drop down list to be "between" and set the first value to be "=Today()" and the second value to be "=Today()+7".
To exclude blanks add a new criteria under the "Format only cells that contain", select "Blanks" but do not set a format.Use the Up arrow to make this criteria the top one in the list.

I have a workbook with a tab for each day of the month in which the Rows contain the information (acount#, person assigned to and date due).How could I get the first sheet to reflect all the accounts that are due for the current day?

Can you please let me know what will be the formula if i have to use it in a weekdays scenarios

How would I enter a formula for:
-change the color if due date is today or within the next seven days
BUT
-leave blank cells formatted 'no color'
Thanks.