Week commencing

[Noz2k]

OK, I currently use this formula to work out the week commencing date

Date - Weekday(Date, 3)

This looked to be working fine when I tested it last week, however now that it is a Monday I get a problem that the code gives me the date for last Monday rather than today.

How can I correct this?

 

[Sektor]

You seem to use function. What is this function?

 

[Noz2k]

I'm not sure I understand the question?

 

[JB_Scotland]

Your formula works okay for me. 23/05/11 returns 23/05/11.

 

[Noz2k]

For me it returns the 16/05/2011


Anyway, I have changed the formula now to

(Date +1) - Weekday(Date, 2)

which gives me the 23rd. Just unsure if this will work on every other day of the week?

 

[jasonb75]

Weekday in VBA works differently to WEEKDAY() in a formula.

http://www.techonthenet.com/excel/formulas/weekdayname.php

Try

Date -(Weekday(Date ,2)-1)

 

[Noz2k]

Is that not essentially the same as what I've already got?

Just subtracting 1 less from a number that's 1 less?

 

[jasonb75]

It looks like it,

which gives me the 23rd. Just unsure if this will work on every other day of the week?

I haven't tested your version so was just giving you one that I know works.

 

[Noz2k]

oh right.

Well, seems to do the trick so thank you