Re-open. Formula to generate current weeks Fridays date plus conditional formating

[rain4u]

I asked some help today in previous post to have a formula that uses TODAY() function to generate last week Fridays date regardless what week of the day it happens to be and I got answer:

Please Login or Register  to view this content.

This is working brilliantly. When I got the answer hoped I figure out rest of it. Which I did as long as I only needed to have any other weekdays date of previous week. But now I would also like to have a code that generates the current week Fridays date again regardless what the day and date is. I tried to mess about for the last hour and a half and cant figure out how to achieve this.


So lets say today is Monday the 31/01/2011, it would then generate the date 04/02/2011.
If today would be Thursday the 03/02/2011 it would still generate the date 04/02/2011.
If today would be Friday the 04/02/2011 it would still generate the date 04/02/2011.
But if today would be Saturday the 05/02/2011 it would now generate the date 11/02/2011.

Can anyone help me with this. Its driving me mad.


Cheers

[TMS]

One way:

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

Regards

[Cutter]

This was posted earlier today.

http://www.excelforum.com/excel-gene...ther-cell.html

[alansidman]

Try this:

=IF(WEEKDAY(B1)=1,B1+5,IF(WEEKDAY(B1)=2,B1+4,IF(WEEKDAY(B1)=3,B1+3,IF(WEEKDAY(B1)=4,B1+2,IF(WEEKDAY(B1)=5,B1+1,IF(WEEKDAY(B1)=6,B1,B1+6))))))

Assuming that B1 contains your date.

Alan

[rain4u]

One way:

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

Regards

Cheers. Looks to be working fine. Its brilliant!

[Cutter]

I know this is SOLVED but this just occurred to me:

=TODAY()+CHOOSE(MOD(TODAY(),7)+1,6,5,4,3,2,1,0)

[TMS]

@Cutter: neat approach but I on't think you need the MOD 7. Just reorder the values depending on the actual WEEKDAY value

Regards

[zbor]

Why not just add 7 at previous result?

=A1-WEEKDAY(A1+2, 2)+7

[Cutter]

I was going to suggest adding the 7 to his original formula because he said it was "working brilliantly" but in order to fully meet his new request it would also require an IF() to test for current date being a Friday so I was looking for as simple an alternative as possible.

I don't think there's any difference between using the WEEKDAY() vs MOD() performance-wise so I went with the latter as it gives a 'countdown' within the CHOOSE(). I thought maybe JBeaucaire might appreciate that!

[daddylonglegs]

Personally I'd avoid using MOD in place of WEEKDAY just because if you happen to be using 1904 date system you'd get the wrong result, with WEEKDAY it doesn't matter which date system you are using

I agree with Zbor, though, his suggested formula returns the current date if that happens to be a Friday, as required, you don't need an IF

To shorten the formula very slightly you could also use this version

=A1-WEEKDAY(A1+1)+7

assuming date in A1. Of course if you want today's date in the formula that becomes

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

[Cutter]

I must have mis-tested on that +7 of the original formula. I got a result of next Friday when current date a Friday. No longer have that file so can't look at it again.

Point(s) taken, DLL. Thanks

[snb]

in ISO-week: (first day of week is monday)

=A1-WEEKDAY(A1,2)+5  

in non-ISO-week (first day of week is sunday)

=A1-WEEKDAY(A1)+6  

[rain4u]

Looks like there are so many different methods to achieve this.
But some of my circumstances have changed. I was using this date feature in conjunction with conditional formating. I actually figured out how to generate some other weekdays as well. So I used them formulas to create a 2 cells with dates. Then i used them cells as a part of a conditional formating. But now would love to not have those 2 cells on my spreadsheet. So i would love to use some sort of formula in conditional formating.
I should told right in the beginning what I need to achieve other than the last weeks Friday date to be generated.

I have a list of dates. There are loads of past and loads of future ones. In the middle there is the current week.

I need them to be highlighted RAG style (Red, Amber and Green). Red - promlematic ones, amber - there is a threat, Green - not to worry so much.
So all dates that are earlier than last weeks friday (inclusive), should be highlighted RED, all the ones from previous Saturday (inclusive) till current weeks Friday (inclusive) should be highlighted Amber or Orange, All the ones that are further than this weeks Saturday (inclusive) should be highlighted green. What we normally do at work is arrange columns by date and then manually highlight them. Very dull job if you have lots of reports every day, plus more than 3 columns to look at. I will attach example. But bear in mind its a manually highlighted, I left two colums next to it so formating can be tested. Please ignore the problem of some dates are actually saved as text. Normally its not the case.


Would really appreciate if someone could teach me how to achieve this by conditional formating only. I would like to ditch them extra cells i.e. like AA1 for example to sort out the date first and then applying conditional formating.


Cheers
Rain

[zbor]

Check this out...

I don't know how you entered dates so it didn't convert them but if we ignore them I think rest of it should work...

[rain4u]

I think you miss understood what I tried to ask. But in all fairness I didn't explain it very well.

This is what I need to achieve. Automatic highlighting within specific date range that depends of current date and my chosen weekday (always last weeks Friday). And all that without any assisting allocated cells that would generate them dates. That I already have now. I have dedicated cells to calculate dates. Them dates are being used in conditional formating to highlight dates in certain way. Now I would like to achieve the same result by only using conditional formating only, and not use them extra cells. Look my attached xls. If you can replicate what is done in col A and do the same with dates in Col H without using C1,D1 or E1 but only usng conditional formatin then you have done it correctly.
Check that xls itwill give better idea.


Today it is 03/02/2011 - Thursday
Achieve the following
All the dates that are earlier than previous weeks Friday (inclusive (28/01/2011) they should be highlighted RED
All the dates from previous weeks Saturday 29/01/2011 (inclusive) till current weeks Friday 04/02/2011 (inclusive) should be highlighted Amber or Orange
All the ones that are further than this weeks Saturday 05/02/2011 (inclusive) should be highlighted green.
Like this
22/01/2011
23/01/2011
24/01/2011
25/01/2011
26/01/2011
27/01/2011
28/01/2011
29/01/2011
30/01/2011
31/01/2011
01/02/2011
02/02/2011
03/02/2011
04/02/2011
05/02/2011
06/02/2011
07/02/2011
08/02/2011
09/02/2011
10/02/2011

And all this without using other cells. Just highlight the column and insert conditional formats.

Can anyone help me?


Cheers

[rain4u]

Though I didn't receive anymore answers in this thread, thanks to some answers in this thread I managed to sort of figure out a working solution for my problem. Im just posting this if anyone ever would like to achieve the same.

Last weeks Friday
=TODAY()-WEEKDAY(TODAY()+2; 2)

This weeks Friday, or the closest Friday from today
=TODAY()+7-WEEKDAY(TODAY()+7-6)

And on conditional formating you could choose:
Less than or equal to =TODAY()-WEEKDAY(TODAY()+2; 2)
Between =TODAY()-WEEKDAY(TODAY()+2; 2) to =TODAY()+7-WEEKDAY(TODAY()+7-6)
Greater than =TODAY()+7-WEEKDAY(TODAY()+7-6)



Thanks everyone for the contribution. These forums are really great because people are so nice and helpful.

Cheers