Get first date of following weeknumber

[mucah!t]

Hello all,

The following formula returns the weeknumber of any given date:
[ISO standard]

=IF(AP12="","",INT((AP12-WEEKDAY(AP12,2)-DATE(YEAR(AP12+4-WEEKDAY(AP12,2)),1,4))/7)+2)

I'd like cell B1 to check the date in A1 [lets say 02-jan 2011] and return the first date of the next weeknumber [mon 03-jan 2011]
Offcourse only if not already the first date of a week is given.

Ideas?

 

[Marcelo Branco]

Hi,

Assuming the first weekday as monday, maybe this
B1
=A1+(8-WEEKDAY(A1,2))*(WEEKDAY(A1,2)<>1)

HTH

M.

 

[mucah!t]

Thanks Marcelo, it works!

 

[Marcelo Branco]

Thanks Marcelo, it works!

You are welcome and tks for providing feedback

M.

 

[barry houdini]

This formula will give the same result

=A1+7-WEEKDAY(A1-2)

 

[Marcelo Branco]

This formula will give the same result

=A1+7-WEEKDAY(A1-2)

Hi Barry,

Your formula is much more simpler, but i'm not fully understanding the logic behind.

I think you used A1-2 because the first day of week, in this case, is monday. Am i right?

M.

 

[barry houdini]

That's right Marcelo, you can adjust the -2 depending on the day of the week, if you change to -1 you'll get the next Sunday....if you make it -3 you'll get the next Tuesday etc....