Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Change Cell Color If Date Equals To Friday

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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)


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m

Similar Topics


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.

i.e Weekly=Friday
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
following week.


I am trying to determine the Friday after a particular date in any given year, but the current formula I have is returning the Friday in the same week of the date it is referencing, rather than the Friday after the specific date;


Saturday April 12, 2014 (Cell D29)


The value it returns is Friday, April 11, 2014

I need it to return the Friday after Saturday April 12, 2014 or any given date.

Please Help!!!!


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!

Hi there,

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:

=TEXT(A1+60, "dd-mmm-YYYY")+11-(WEEKDAY(NOW()))

However, this returns the following friday if the date + 60 days is a friday.

Please help


Hi, I have a spreadsheet that have a series of dates for the whole year of 2012 and some in 2013. I need a formula to give me a current Friday date base on the date shown in the spreadsheet. Based on a seven day week Sat thru Fri. See example below, column 2 is the Friday date.

11/1/12 --> 11/2/12
11/12/12 --> 11/16/12
11/14/12 --> 11/16/12
5/21/12 --> 5/25/12
5/28/12 --> 6/1/12
7/1/12 --> 7/6/12



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.


Hi Guys,

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
Etc. etc.

B1 = a date (i.e. 3/1/06)
B2 = a date (i.e. 3/2/06)

C1= 8

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:

Set 1
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.

Set 2
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.

Thank you.

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.

Cape Town NDD Drops Cartons KM's Time Shipments Sprinter 315 Atego 8 t Merc 12 t Atego 1017-5t Sprinter 518 MONDAY WEDNESDAY 636 73545 4130.1 22346 32 0 0 0 0 0 TUESDAY THURSDAY 657 97894 3516.6 19472 29 WEDNESDAY FRIDAY 670 109157 4124.7 21519 31 THURSDAY MONDAY 615 77417 5260.9 23389 31 FRIDAY TUESDAY 668 106739 4464 22843 32

The data is extracted from this worksheet below. (only partial section of the worksheet)

Depot Name Route NOD Vehicle Type Cape Town Friday 10 FRIDAY Sprinter 315 Cape Town Friday 100 FRIDAY Sprinter 315 Cape Town Friday 109 FRIDAY Sprinter 315 Cape Town Friday 118 FRIDAY Sprinter 315 Cape Town Friday 127 FRIDAY Sprinter 315 Cape Town Friday 136 FRIDAY Sprinter 315 Cape Town Friday 145 FRIDAY Sprinter 315 Cape Town Friday 154 FRIDAY Sprinter 315 Cape Town Friday 163 FRIDAY Sprinter 315 Cape Town Friday 181 FRIDAY Sprinter 315 Cape Town Friday 19 FRIDAY Sprinter 315 Cape Town Friday 190 FRIDAY Sprinter 315 Cape Town Friday 199 FRIDAY Sprinter 315 Cape Town Friday 208 FRIDAY Atego 8 t Cape Town Friday 217 FRIDAY 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.

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.

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


Dear all,

I receive via e-mail Starting dates and End dates in all kind of forms:

- Friday, 12 September 2014
- Friday, September 12 2014

f.e. Friday, 12 September 2014 until Friday, 19 September 2014.

I copy these dates to excel, where I have a simple formula to calculate the number of days between the end date and the starting date.

I have 2 problems he
- first: my excel only calculates the days between if I would enter a short date first: f.e. 12/09/2014. Fact is that I have only long dates as input.
- second: due to the different forms of the long date I would have to change my regional settings every time.

Does anyone see a possibility here in working with the long dates that are provided in different formats?

Thanks in advance!

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.

Any help?

Hi All

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



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.


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?