Find the Date of the Next Friday

On Sat, 09 May 2009 06:39:49 -0700, Dana DeLouis wrote:

Hi. You may be thinking of this, where the '6 represents your "Friday"

=A1+MOD(6-WEEKDAY(A1),7)

If the question was about Thursday, change '6 to '5.
= = = =
Dana DeLouis

Combining MOD(n,7) with WEEKDAY, which also does a MOD(n,7) function seemed
illogical to me, even though it works.

In other words,

=MOD(6-WEEKDAY(A1),7)

and

=7-WEEKDAY(A1-6)

both return the same values

(And, if you are using the 1900 date system), so does:

=6-MOD(A1,7)

--ron

Ah! Yep, I think it was something like that. I seem to remember that the
solution I came up with a couple of years ago worked by using MOD, and then
figuring out the day based on the remainder returned. So it was probably
something very much along the lines of what you've suggested.

--Tom

"Dana DeLouis" wrote in message
...
... I also seem to remember that the solution
*may* include use of the MOD() function.

Hi. You may be thinking of this, where the '6 represents your "Friday"

=A1+MOD(6-WEEKDAY(A1),7)

If the question was about Thursday, change '6 to '5.
= = = =
Dana DeLouis



Thomas M. wrote:
Wow! My recollection on how to do that was way off! ;-)

Thanks for the help. I've added your formula to my file and it works
perfectly.

--Tom

"Ron Rosenfeld" wrote in message
...
On Wed, 6 May 2009 21:50:41 -0600, Thomas M.

wrote:

I found a formula on the Internet that works.

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

Anyone have a better way of doing this?

--Tom
As Rick wrote, merely substitute TODAY() for A1 in the formula I
previously
posted.

=A1+7-WEEKDAY(A1+1)

or put TODAY() in A1 or some other cell to be referenced.

The formula I posted can be generalized to:

=A1+7-WEEKDAY(A1+7-DOW)

where DOW is Day Of Week and 1=Sun,2=Mon...6=Fri,7=Sun

--ron

I had a formula that worked for six of the seven days of the week. It
failed on Saturdays so I reset my system clock to May 9 so that I could test
my formula. It looks like I forgot to reset to the correct date before
posting the message. My apologies.

--Tom

"Don Guillett" wrote in message
...

Either your date is way off by accident or you did it on purpose to stay
at the top of the list. By definition, I routinely delete those. Too bad
if you had a legitimate question.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Thomas M." wrote in message
...
Excel 2007

This is driving me nuts, primarily because I know that I've done this
before, but I just can't seem to zero in on the solution today, and I
haven't been able to find it in my other files. I need a formula that
does the following two things:

1) If the current day is a Friday, put the current date
2) In all other cases put the date of the *next* Friday

I seem to remember that this requires a combination of the DATE(), DAY(),
NOW(), and WEEKDAY() functions. I also seem to remember that the
solution *may* include use of the MOD() function. Anyone have a way to
do this?

--Tom