Change Cell Color If Date Equals To Friday
Change Cell Color If Date Equals To Friday - Excel
Hello is there anyway I can do a conditional formatting of something like that to color a cell if the date in the cell is a friday?
Dates will appear in a column like this:
20-mar (this is a friday, it should be red)
Similar Excel Video Tutorials
Gantt Chart with Weekends and Holidays
- See how to create a Gantt Chart that will include highlight the workdays one color and weekends and holidays another color. See the WORKDAY function t ...
What I'm trying to create is a list of week dates (Fridays) for 12 months, interspersed with four dates for quarter ends. In this case, the ends of quarters a
30 Apr, 31 Jul, 31 Oct and 31 Jan, for a financial year commencing 1 Feb.
For example, for 2008, the first Friday is 1 Feb, the following Friday is 8 Feb, and so on. At the end of the first quarter, I want to "slot in" the end of quarter date between the last Friday of Q1 and the first Friday of Q2.
These would be the required dates up to and past the first end of quarter:
11 Apr 08 Friday
18 Apr 08 Friday
25 Apr 08 Friday
30 Apr 08 Wednesday
2 May 08 Friday
9 May 08 Friday
I'm sure there's a formula that can be copied down the column from the start date, but I'm having trouble figuring it out.
(If it helps, the quarter end dates are named ranges, which I've called EOQ1, EOQ2, and so on.)
Thanks in advance,
I have dates listed in column A indicating the day an action was performed. In column B I need to have the date in column A changed to a Friday. If the date in column A is not a Friday, then the date in column B needs to fall back to the previous Friday. For example, if the date entered in column A is today, Monday August 7, then the date in column B that I would be Friday August 4. Can anyone think of code (or a formula) that can determine this and place the value in column B? Currently I use vlookup to go to another sheet where all the possible dates are listed with their respective Fridays, but this slows down the calculation worksheet as the file is quite large. Thanks for any help or suggestions.
I need to be able to return some values in a data sheet associated with a particular day. In column A we have the date.Column B,C and D have some associated data.In column F I have used =TEXT(A4,"dddd") to find the day of the date in column A. Since I want every Friday date, I use =IF(F4="Friday",A4,"") to return the date and then any other associated data which are shown in Columns H&I. In Column K i have manually returned the dates of every friday in Column A. I need to be able to generate the list of dates of every friday directly.
Also if I have a list of dates and I want to return a certain value at the end of every month,quarter of year how would I do that.
Monthly=Last Friday of every month
Quarterly=Last Friday of Every quarter
Yearly=Last friday of every year.
Looping through a series of cells with dates, need to compute from the
given date the date of the following Friday. If the given date *is*
Friday, I need that date returned, not the date of the Friday of the
I need a formula that is probably quite simple, but I can't figure out how to write it.
My formula should be located on cell B1.
I need to sum 1 workday to the date that appears on cell A1, if that date appearing on cell A1 is a friday, if its not a friday just copy the date on cell A1 to cell B1.
Thank you very much!
I have found several similar formula's on here but not quite what I need.
I need a date in column a to have 60 days added and then to find the nearest Friday to that date. If the date 60 days on is a friday I want to return that friday and not the next.
Date in column A is 25/01/2010 date 60 days is 26/03/2010 which is a frdiay so formula should return this date. It should return this date for anything between 19/01/2010 and 25/01/2010.
The formula I was using is:
However, this returns the following friday if the date + 60 days is a friday.
Is there a function that would givme date of last Friday? My requirement is to see date of last Friday. There is this progress report that I need to prepare based on data on every Friday. I usually prepare it on Monday or Tuesday. I was wondering if there was a way in which Friday's date is populated automatically that I can use in my other formulas.
I have a spreadsheet that I use to reference what is happening on a daily basis.
In cell AA1 I have the formula '=today()' and then I have in column 'B' a list of courses that start on certain days and if they match todays date then I change the font colour to Red and courses starting tomorrow are formatted to Orange using the condion 1: 'Cell Value eual to' and then '=$aa$1' and for condition 2 '=$aa$1+1'.
I have a formula using weekday and a lookup table to show the days of the week Sunday being 1 and Saturday being 6 the lookup table will return the day name respectively.
What I wanted to know is when the cell containing the vlookup data for the days of the week equals 'Friday'. Is there a way to get conditional format to check cell the cell 'aa3' to see if it equals friday and then have a 3rd condition where is would change the colour of Mondays courses that are starting.
So basically somewhere along the lines of: if cell aa3="Friday" then format '=$aa$1+3' to what ever colour font I require.
Is this possible?
Thanks in advance.
I've tried a few ideas but can't seem to make any progress myself, so some help would be very welcome.
In cell A1 of this worksheet I have used the 'TODAY' function. I need a formula in A2 that will always automatically display the date for the coming Friday. For example: Cell A1 is now showing 08/08/07 and I need A2 to return 10/08/07 (The date on Friday).
The second part of my problem is: In cells B1 to B26 I have a list of 26 dates. I need some kind of formula (maybe using 'IF') that will display a message if the Friday date in A2 = any one of the dates in the list.
Hope I have explained this clearly,
Many Thanks..... Peter
have cell A1(formatted to DDDD to display name of day): =B1
Have cell A2(formatted to DDDD to display name of day): =B2
B1 = a date (i.e. 3/1/06)
B2 = a date (i.e. 3/2/06)
Need a formula for column D to detect when the a cell in collumn A =friday, then it'll switch to column E until it detects the next Friday, then switch to collumn G. Basically add the total number up until it reaches Friday, which is the end of the pay week, then the next pay period starts on saturday until the next Friday, and give a total number of hours.
Can you help?
I've read a few posts and am confused as to how to get some conditional formatting to work with dates. Here is my situation:
I have a list of dates in different columns and rows and would like to have the cell color and/or type color to change based on the date within the cell. two sets of conditions:
When today is the same day as the date in the cell, it does not change
When the date is one day old, the cell changes to a color
When the date is 7 days old, the cell changes to a different color.
When today is the same day as the date in the cell or 1-4 days old, it does not change
When the date is five days old, the cell changes to a color
When the date is 14 days old, the cell changes to a different color.
I have a column that has dates going vertically down from 1/1/2009 - 1/7/2010. I need a formula that will return the date for every other Friday beginning with the second Friday in January 2009.
Thanks for any help you have!!
I have a date in cell M3, I need cell N3 to return the following Friday date.
Here is the formula I used:
HOWEVER, I if the date in cell M3 is on a Monday Tuesday or Wednesday, I need the Friday date to be of that week.
If the date in cell M3 is on a Thursday or Friday, I need the Friday date to be that of the following week.
M3 = 7/26/10 N3 needs to return 7/30/10
M3 = 7/29/10 N3 needs to return 8/6/10
Patiently waiting your excel mastery assistance.
Help with a formula please.
I have a spreadsheet containing information about what vehicles I use per route from a Monday through to a Friday. I'm looking for a formula that will count the number of vehicles used of a specific vehicle size on each particular day of the week.
In the example below I would like to know how many Sprinter 315 vehicles I used on a Monday, then how many Atego 8t also on Monday etc.
Atego 8 t
Merc 12 t
The data is extracted from this worksheet below. (only partial section of the worksheet)
Atego 8 t
Merc 12 t
Does this make sense?
hello, I've been through searching the forum and cant find what Im after.
Basically I have a date calculator spreadsheet that works out dates based on workdays. I need the final date to always round off to the friday after the date. ie if the date for completion was 10-Sep-07 it would change and display 14-Sep-07.
any help is appreciated.
At the moment I'm using two formulas to get the date for specific weekday:
for last Friday
for next Friday
How could I use these two days to count all dates In col A that fall between these two days (inclusive)
Also how could I count all dates in column A that older than last weeks Friday
Any help is appreciated
I stuck on using option button groups. I have created two option button groups and a date cell on a worksheet that I want to use working with 12 monthly calendars. Group 1 has 4 choices (Weekly, Bi-Weekly, Semi-Monthly, and Monthly), Group 2 has 7 choices (days of the week), and then there is the cell to enter a date. What I want to do is make select in Group 1 and Group 2 and enter a date, then have programming set calendar dates (identified in group 1 & 2) font a pre-defined color. For example, if select Weekly in group 1 and Sunday in group 2 and enter date of first Sunday of year, then Sunday of every week will be the pre-defined font color. Or, if I select Bi-Weekly and Friday and enter 1/8/2010 (second Friday of year), then Friday 1/8/2010 and every other Friday is the pre-defined font color. Each of the choices from group 1 seems to present different problems, and I have no idea how to begin programming for the desires results. Any help is so greatly appreciated.
I'm trying to differentiate rows based on a date in the row. I'm grouping dates together and that's where I'm running into difficulties. I'm breaking the month up into 5 segments which are as follows:
Segment 1: The 1st of the month through to the 1st thursday of the month
Segment 2: The 1st Friday through to the 2nd Thursday
Segment 3: The 2nd Friday through to the 3rd Thursday
Segment 4: The 3nd Friday through to the 4th Thursday
Segment 3: The 4th Friday through to the end of the month.
What I'm trying to accomplish is have rows with information. One of the cells in those rows will be a date. If the date fits into segment 1, the cell to the right of it will have a 1, if the date fits into segment 2 the cell to the right of that date will have a 2 and so on. Any thoughts?
I have a list of days of the week determined by the date that it corresponds to. So, for instance, if the date in cell B1 is 1/2/11, the value in cell A1 is Sunday (=WEEKDAY(B1,1).
As the day of the week in cell A1 can change depending on the value in B1, I'd like to be able to have Monday through Friday have a different background color than Saturday and Sunday. I'm not versed in conditional formatting, so I'm struggling a bit.
Could you please help me in calculating some dates,
for ex: today's date ( D ) - 10/06/2011
I want to calculate dates for D-1 , D-5 , D-9 , D-11 , D-30. with the help of a formula with following conditions,
Here D is today's date.
(-) is minus symbol.
D-1 is yesterday's date.
D-5 ---> Date before 5 days from today's date, calculating from yesterday's date.
Same way for D-11 and D-30.
1. Friday + Saturday + Sunday should be considered as a single day.
2. If D-1 , D-5 , D-9 , D-11 , D-30. is on Friday, i want Friday, Saturday and Sunday dates to be displayed.
3. If monday is a bank holiday, then Friday + Saturday + Sunday + Monday should be considered as one day and if D-1 , D-5 , D-9 , D-11 , D-30 is on friday, i want friday, saturday , sunday and monday dates to be displayed.
4. If friday is a holiday, that friday also has to be considered (counted) while calculating dates.
5. If friday and monday are bank holidays, then friday + saturday +sunday + monday should be considered as one day.
Please find 2011 and 2012 calender with bank holidays in sheet 2. ( yellow coloured cells are bank holidays)
I want these days to be calculated till 2012 end.
Please find the excel attached with one example.
Also could you please giude me how to protect formulas in excel, so that only 'D' field in attached excel should be able to type and no ammendment should be done in rest of the cells.
Thanks in advance.
I was wondering if anyone could help me count the number of times some one is working late on a Friday
I have a list of names in Column A
In row 8 i have the date in the format 09/07/2010
When ever there is an "L" in a friday is there a way I could count that L in the Friday to see how many times that person is working a friday late in the month ?
Thanks for all help provided
Ok, here's my problem:
I have three groups of employees:
1) employees that work every Friday
2) employees that work every other Friday
3) employees that work every other Friday, except they work the opposite Friday as group #2.
I'm trying to develop a spreadsheet that will list all employees that are scheduled to work for the upcoming Friday. Ideally, the spreadsheet would read from the current date, pick the Friday of that same week, and then report all the employees that will be working.
Anybody have any ideas on how to build a spreadsheet that will do this?
Sorry for such a long post, but can anyone help me workbook attached.
Thanks in Advance
I have a calendars worksheet on which I want to use a foreground color to mark paydays for various pay day frequencies and occurrences, using options in option groups.
The problem is how do I get foreground colors for paydays, depending on options selected in two option groups and a Start Date, where Option Group 1: Weekly, Bi-weekly, Semi-Monthly, Monthly; where Option Group 2: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday; and where a Start Date is entered in a cell.
For example, I select Weekly in OG1, select Thursday in OG2, and enter 1/7/2010 (first Thursday of 2010), then programming color Thursday of every week (1/7/2010 to end of year) a certain foreground color. In this case, regardless of the day of the week chosen, foreground color for this day, in every week of the year, is colored. If Wednesday was selected, date would be 1/6/2010.
In another example, I select Bi-Weekly in OG1, select Friday in OG2, and enter 1/1/2010 (first Friday of 2010), then programming cause every other Friday, beginning at 1/1/2010 to have a certain foreground color. In this case, first payday Friday could be 1/8/2010, with every other Friday, including this one, to have a certain foreground color. In this example only first or second Friday (or whatever day selected) of the year should be entered as Start Date, but any day can be selected
In yet another example, I select Semi-Monthly (paid twice every month) in OG1, no selection is necessary in OG2 or the day (Sun, Mon, etc) on which the 1st day of January fall could be selected, enter Start Date of 1/1/2010, then the programming cause Start Date and the day/date which is 14 days ahead (the 15th) to be foreground color coded. The only other Start Date could also be the 16th, with the 30th or last day of month (February).
In the final example, I select Monthly in OG1, no selection is necessary in OG2, but the Start Date entered is the pay date and the date where foreground is colored in every month. In the case of February, which only have 28 or 29 days, if the 30th or 31st is the Start Date or payday, then the default is last day of February.
I am using Excel 2007 jor just a few weeks and I need some help with Conditional Formtting. This is quite different from Excel 2003.
See the attached file.
1) cell A1 = Year 2010.
2) cell C1 (First Friday of Year)
3) Cell D1 to X1 (every other friday)
I need to create some conditional formatting that makes the cells colored as they are on row1 based on the MONTH.
I have looked everywhere and can't seem to find a simple way. First, I was looking for a formula to add into the following:
=TEXT(TODAY(),"mmmm d, yyyy")&" ["&TEXT(TODAY(),"dddd")&"]"
...where the day part would show up as a different color, whether it be red or dark grey... whatever.
Right now, the outcome accurately shows as July 30, 2010 [Friday]. I want [Friday] to be a different color. Is that possible?