A weekly equivalent of EOMONTH
 

Hi,

I use the EOMONTH function to return the dates 1 month, 3 months etc
back from a base date. Can someone please tell me how to do the same
using a specified number of weeks rather than months.

For example, if the base date is 23/02/06, what is the date at the end
of the previous week if one defines the end of a week as a Friday?

Any help would be much appreciated.

Regards,
Andrew


--
andrewc
------------------------------------------------------------------------
andrewc's Profile: http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=515738

A weekly equivalent of EOMONTH
 

=A1-CHOOSE(WEEKDAY(A1),1,2,3,4,5,0,1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"andrewc" wrote in
message ...

Hi,

I use the EOMONTH function to return the dates 1 month, 3 months etc
back from a base date. Can someone please tell me how to do the same
using a specified number of weeks rather than months.

For example, if the base date is 23/02/06, what is the date at the end
of the previous week if one defines the end of a week as a Friday?

Any help would be much appreciated.

Regards,
Andrew


--
andrewc
------------------------------------------------------------------------
andrewc's Profile:
http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=515738

A weekly equivalent of EOMONTH
 

Assume date is in A1, in B1 you put -1 for previous week, 0 for this week 1
for next week and so on

so if you put -1 in B1

=A1+(6-WEEKDAY(A1))+B1*7

formatted as date returns Feb 17 2006


now if you put the Saturday Feb 25 in A1 and -1 it will return Feb 24 2006
so if your dates are weekends you have to adjust the formula a bit and text
for weekend dates but as long as the dates are weekdays it will work as in
the example, put 4 in B1 and it will return Mar 24th 2006 (next Friday 4
weeks from Feb 23 2006)
--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"andrewc" wrote in
message ...

Hi,

I use the EOMONTH function to return the dates 1 month, 3 months etc
back from a base date. Can someone please tell me how to do the same
using a specified number of weeks rather than months.

For example, if the base date is 23/02/06, what is the date at the end
of the previous week if one defines the end of a week as a Friday?

Any help would be much appreciated.

Regards,
Andrew


--
andrewc
------------------------------------------------------------------------
andrewc's Profile:
http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=515738

A weekly equivalent of EOMONTH
 

Thank you all!!!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile: http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=515738