Find the Date of the Next Friday

On Sat, 9 May 2009 17:46:13 -0600, "Thomas M."
wrote:

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



=A1+7-WEEKDAY(A1+1)
--ron

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

In article ,
says...
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

The general formula Ron posted is much better (he assumed the date you
wanted to reference was in A1 whereas you are interested in today's date
only); here is his formula restructured for the specific way you want to use
it...

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

--
Rick (MVP - Excel)


"Thomas M." wrote in message
...
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

In article ,
says...
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

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

On Thu, 07 May 2009 07:17:31 -0400, Ron Rosenfeld
wrote:

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

Obviously, that should have ended ...6=Fri, 7=Sat
--ron

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

On Thu, 7 May 2009 12:01:47 -0600, "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

Glad to help. Thanks for the feedback.
--ron

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

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

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