Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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:
16-mar
17-mar
20-mar (this is a friday, it should be red)
21-mar
21-mar

Thanks!!!

View Answers     

Similar Excel Tutorials

Get the Day of the Week (1 to 7) for a Date in Excel - WEEKDAY
Use a function in Excel to get the number of the day in a week, from 1 to 7.  This allows you to use your dates to ...
Get the Current Date in Excel
How to get the current date using Excel.  This method updates the date every day so that it is always accurate. To ...
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 ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...

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







Hi,

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,

Johnno


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.

Thanks.






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;

Example:

Saturday April 12, 2014 (Cell D29)

=D29+(6-(WEEKDAY(D29,1)))

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!!!!

Hello,

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.

i.e.
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:
Code:

=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

Thanks




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

Thanks,

Hi

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.

Thanks
Rajesh


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

Hi,

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!!

-John


I have a date in cell M3, I need cell N3 to return the following Friday date.

Here is the formula I used:

=M3-WEEKDAY(M3,3)+IF(AND(WEEKDAY(M3,3)>=5,WEEKDAY(M3,2)<=5),9,11)

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.

Examples:
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
Code:

=TODAY()-WEEKDAY(TODAY()+2, 2)


for next Friday
Code:

=TODAY()+7-WEEKDAY(TODAY()+7-6)


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


Cheers
Rain




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

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 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?


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?

TIA!!


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.

Need some help with a date formula, please! Payday is every other Friday. I need a formula that will tell me what date payday falls on for each of the 26 pay periods (sometimes 27) in any given year. Most years there are 26 paydays, but occasionally (such as 2010) there are 27 paydays.

My sheet looks like the one below. When I change the year (cell B1) the formulas in column B need to return the correct date for each pay period (every-other Friday) for that year, without having to enter the first payday for that year manually. In 2011 the first payday is 1/14 (the last payday of 2010 is 12/31.) I have spent a couple hours on this, but clearly don't understand date functions well enough. Maybe the every-other-Friday scenario is not possible here.

Any help with a formula for this would be greatly appreciated.

Thanks much!

Year

2011 Pay Period #



Pay Date

1 1/14/11 2 1/28/11 3 2/11/11 4 2/25/11 5 3/11/11 6 3/25/11




Okay, I have a spreadsheet. Cell A1 contains the month, Column B contains
all the weekdays of that month. Daily data is stored is Column C. I want to
do weekly totals of the data in Column C as well. However, if the user
inputs a different month in A1, the weekdays will all change, shift...etc.

Therefore, to total week 1, I need to locate the first instance of Friday in
Column B, and then add that to everything above it. Week 2 needs to find the
2nd instance of Friday and add everything from that Friday to the first
Friday...and so on.

How does one do this?

Thank you very much....

Arlen



Hi all

I have a validated list that includes the last Friday of each week whereby a user can select their desired date. Based on the user selected date I want to lookup data for 3,6,12,36 and 60 month up to the date selected.

Not a problem for month ends but because this list has weekly data up to the last Friday of each week it is often random dates such as 25/03/11 and 01/04/11. How do I get the last Friday from 3,6,12 months ago etc. for one of these random dates?

Sure there is a simple answer but I am having a blackout at this moment in time so any help appreciated.


So I have a excel sheet that I send out to field crews for them to fill out and send back to me every Friday. What I am looking for is to get the system date and place it in a cell then find the next Friday and populate the dates for the week. This is a hard one to explain but any help would be great.

Thanks