Help change Friday to following Monday
 

XL2000
I currently use the following formula to return the Friday before the 17th
of the month if the 17th falls on a weekend:

=DATE(YEAR(NOW()),MONTH(NOW()),17)-MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW
()),17),2)-5)
(Excuse the wrapping, please)

What I would like instead, is a formula to return the date of the following
Monday if the 17th falls on a weekend. I don't want to use the Workday()
function, because the file will often be viewed on machines that don't have
the Analysis Toolpak available.

Simple for someone who knows how(?), but not for me :(

--
David

Help change Friday to following Monday
 

Here's one way....

=DATE(YEAR(NOW()),MONTH(NOW()),17)+CHOOSE(WEEKDAY( DATE(YEAR(NOW()),MONTH(NOW()),17)),1,0,0,0,0,0,2)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=554353

Help change Friday to following Monday
 

Try:
=DATE(YEAR(NOW()),MONTH(NOW()),17)+8-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),17),2)

-Simon

"David" wrote:

XL2000
I currently use the following formula to return the Friday before the 17th
of the month if the 17th falls on a weekend:

=DATE(YEAR(NOW()),MONTH(NOW()),17)-MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW
()),17),2)-5)
(Excuse the wrapping, please)

What I would like instead, is a formula to return the date of the following
Monday if the 17th falls on a weekend. I don't want to use the Workday()
function, because the file will often be viewed on machines that don't have
the Analysis Toolpak available.

Simple for someone who knows how(?), but not for me :(

--
David

Help change Friday to following Monday
 

daddylonglegs wrote


Here's one way....

=DATE(YEAR(NOW()),MONTH(NOW()),17)+CHOOSE(WEEKDAY( DATE(YEAR(NOW()),MONT
H(NOW()),17)),1,0,0,0,0,0,2)



That works as desired. Many thanks.

--
David

Help change Friday to following Monday
 

This works under limited conditions, but fails to satisfy my requirements:
If the 17th falls outside the weekend (M-F), leave it as the 17th. It
instead *always* returns the following Monday.

Thanks for the effort anyway.

--
David

?B?U2ltb25DQw==?= wrote

Try:
=DATE(YEAR(NOW()),MONTH(NOW()),17)+8-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW
()),17),2)

-Simon

"David" wrote:

XL2000
I currently use the following formula to return the Friday before the
17th of the month if the 17th falls on a weekend:

=DATE(YEAR(NOW()),MONTH(NOW()),17)-MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONT
H(NOW ()),17),2)-5)
(Excuse the wrapping, please)

What I would like instead, is a formula to return the date of the
following Monday if the 17th falls on a weekend. I don't want to use
the Workday() function, because the file will often be viewed on
machines that don't have the Analysis Toolpak available.

Simple for someone who knows how(?), but not for me :(

--
David