Change Cell Color If Date Equals To Friday

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

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.

Hi.....

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

C1= 8
C2=8
c3=6
c4=9

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.

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.

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.

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

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.

Any help?

Hi,

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.

Conditions:

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.

Priyaa

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

Fred
Sam
Etc

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

Arts

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

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.

Hi,

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.

Regards,
Elio Fernandes

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?