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 Tutorials

Join a Date and Time into a Single Cell using VBA Macros in Excel
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
Quickly Enter the System Date in Excel - Keyboard Shortcut
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...

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

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.

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


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?


I am having problems solving this:

Friday, May 3, 2013
Friday, May 3, 2013
Friday, May 03, 2013
Friday, May 03, 2013
Friday, May 03, 2013
Friday, May 03, 2013
Friday, May 03, 2013

I use the function remove duplicates in excel but it does not recognize that May 3 and May 03 is the same!
How can I solve this?

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?

I need to check if a date, passed as a parameter, falls on the last Friday of the month. How can I do this in VBA? The function should return a boolean if the date passed is the last Friday of the month.

I have a model that runs daily but on the last Friday of the month some additional functions must be run.

Any ideas how I can do this?

p.s. the check must be performed irrespective of which day is the FirstDayOfWeek.