How do I write an Excel formula that returns the date of this coming Friday?

Question

For example. I want the date in the cell to be June 1st. But, if I just do today()+2, then tomorrow it will show Saturday, June 2nd.
Essentially, I want the cell to determine the date of the very next Friday. So, on June 5th, it will return the date of June 8th (Friday).

That tutorial did not help. You didn't read the question did you? You just threw a link to a Microsoft page so that you could have your points? Your answer is of absolutely no value. What a waste of time.

Answer 1

It is just a little complicated, but I will explain.
=IF(WEEKDAY(TODAY())<7, TODAY()+6-WEEKDAY(TODAY()), TODAY()+6)

You can use the weekday function to determine the day of the week with Sunday = 1 and Saturday = 7

For all of the days, except Saturday, you could just add the number of days until Friday to the current day by using
Today() + 6 - Weekday(Today()).

Saturday is a different problem (using the above it will give you the last Friday). So we need an if statement.

if(Weekday(Today()) < 7, "its not a saturday", "it is a saturday")

Our special case for a Saturday is just add 6 days.

The result is:

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

Answer 2

There may be another way to do this, but this is what I come up with

=IF(WEEKDAY(NOW(),1)<=6,NOW()+(6-WEEKDAY(NOW(),1)),NOW()+6)

Let's dissect this beast.

=IF(WEEKDAY(NOW(),1)<=6

the IF statement tests to see what day of the if the week we are on based on the WEEKDAY function. This returns a number, 1-7, based on a Sun-Sat week (if you use the default, which I did).

If it is less than six, we go to the next part of the IF, which does the math to add the number of days to the next Friday based on todays date.

NOW()+(6-WEEKDAY(NOW(),1))

If it is greater than 6, that means it is Saturday, and it just adds six more days to get to the next Friday.

NOW()+6

Be sure that the cell you place the formula in is formatted as a date, or it will give you a decimal number.

Answer 3

Yes It is something like X-Weekday(X) that is, our week starts from Saturday, so We type Sat in one cell (B1) and fill in to the next 6 days until Friday in B7 in the cell that beside Sat we use this formula =B1-Weekday(B1) and here you have the date of the Saturday for that date Now in B2 type this =B1+1 and fill in to the next 6 days By this you will have the full Week Calendar for a given date XLMan

Answer 4

Here is a quick formula for you:

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

It compares today's weekday number, where 0-7 = Sunday-Saturday to the number 6 (Friday). If Today is Friday or Saturday, then it adds a week to get to next Friday. If Today is Sunday-Thursday, then it just figures out how many days until Friday and adds it today's date.

NOTE: If Today is a Friday and you want it to show today's date, then change this:
6-WEEKDAY(TODAY())<1
to
6-WEEKDAY(TODAY())<0

The first one will be true will be on Friday on Saturday, the second will be true only on Saturday.

This will return the date in serial number format, so you may need to apply a date style to the cell to get it to show the way that you want it.

Answer 5

We have a weekly scorecard on our projects every week. We have a spreadsheet that we need Mondays date for each project or row. So we have a Setup sheet (tab) that we populate Monday's date on.........which I think in your case would be Friday's date..........then I just reference that date on the worksheet where we are doing the reporting ='SET UP'!$B$3. With the $ sign on each side of the B you can drag down the formula referencing the same B3 cell (or whatever cell on the Setup tab) We then Hide the Setup tab...........hope that helps???

Answer 6

This tutorial will help you:

Learn how to figure out dates by using formulas in Microsoft Excel:

http://office.microsoft.com/training/training.aspx?AssetID=RC061771061033&pid=CR061831141033

Answer 7

Inexpensive Online Shop >>===> http://market3.4gw.pw/pmfhq

Answer 8

Type the following formula

=IF(WEEKDAY(TODAY())<>6,
TODAY()+(6-WEEKDAY(TODAY())),
TODAY())

One formula. I broke it down as it is in if command

Answer 9

try this formula in cell B3... assuming your start date is in cell A3

=IF((6 -(WEEKDAY(A3,1)))>0,((6-WEEKDAY(A3,1))+A3),((6-WEEKDAY(A3,1))+7+A3))