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
ADVERTISEMENT
Oct 18, 2013
I am trying to calculate the Date of the next occurring 3rd Friday of the month in relation to a Static Date in a cell. So if Static Date in cell is Tuesday of the second week of a month, then I am trying to return the date of the 3rd Friday of that month. If the static date in the cell is the third friday of the month, I just want to return that particular date. And if the static date is after the 3rd Friday of the month, I am trying to return the 3rd friday of the following month. So far here is the formula I am using:
Code:
=DATE(YEAR(A2),MONTH(A2),14+CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),4)),2,1,7,6,5,4,3))
The problem I have is that if the static date is after the third friday of the month, this continues to return the 3rd friday of the month of the static date and does not advance to the third friday of the following month. Looks like I cannot attached a sample spreadsheet but here is a screenshot of the sample sheet:
[URL]
View 4 Replies
View Related
Feb 12, 2014
For each of my staff I need to count if they worked 15 or more days in the 30 days preceding a statutory holiday. (As a follow on problem... If they have worked 15+ shifts then I'll need to calculate the sum of their hours over the 30 days / 30).
I tried the functions below but they didn't work. (the Name1 column records the number of hours worked that day's shift) The actual count in my test case is 5.
=COUNTIFS([stat],"=STAT",[Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0") result: "0"
=COUNTIFS([@stat],"=STAT",[Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0") result: #VALUE!
=IF([@stat]="STAT",COUNTIFS([Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0"),"NO") result: "0"
View 1 Replies
View Related
Oct 26, 2009
I have two date columns, say E & F. In these two columns I have entered date values. I need to do colour, font bold, Italic automatically when date values are entered in these columns - with a conditional formatting - i.e. every month should be in different color.
For example, values 23/05/2009, 15/10/2005, 07/12/2004 must be automatically coloured with different colors as its months are different.
View 2 Replies
View Related
Feb 7, 2007
Having trouble with a date format:
I am trying to get CurrentDate from a 02/07/07 to 02/07
My CurrentDate = Format(CurrentDate, "mm/yy") doesn't seem to work, no matter what CurrentDate = mm/dd/yy
My eyes are watering from stareing at the screen for so long....
Private Sub TextBox24_Change()
Dim MonthPlus, CurrentDate As Date
Dim userow, usercolumn As String
View 9 Replies
View Related
May 5, 2014
i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
Oct 9, 2012
I need contents in cell A5 to turn red based on 2 criteria occurring.
IFCell F5 is greater than 0 (zero) AND IF date in H5 is equal to or less than 30 from today's date. PS: if needed I have today's date in A1 for reference.
Can this be done and how?
View 2 Replies
View Related
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
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
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
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
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
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.
View 9 Replies
View Related
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
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
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
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
