Function to set Date

[J]

Hi,

I would like to know how to set below equation:-

to set the 1-week (i.e Monday) prior to a specific date: e.g. 2009/11/24, i
would like to have the output = 2009/11/16

e.g. 2009/12/31, output = 2009/12/21

 

[Ashish Mathur]

Hi,

Try this. Cell B16 holds the specific date

=B16-7-CHOOSE(WEEKDAY(B16-7,2),0,1,2,3,4,5,6)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com