WEEKDAY Formula not giving correct date - HELP PLS!

[MonkeyFace28]

hey y'all,

hope everyone is good.

I have a spreadsheet I am trying to get the week commencing date to auto populate at the top of each tab.

I have my first week commencing date in cell M1 on tab named "WK1" I want my next week commencing date to show in cell A1 in tab "WK2" (i think you can see where this is going )

I also have a formula in A1, tab 1 that works like this -
Formula: =$L$1& " " &TEXT(M1,"d mmmm yyyy")
output: W/C 2 January 2019 - p.s if anyone can get the little letters to work i.e the nd, th, st, that would be amazing!)

the formula I have to give me the w/c date is as follows

Formula: =WK1!M1-WEEKDAY(WK1!M1,2)+1
output: W/C 31 December 2018

I can't work out why it's going backwards not forwards?

any suggestions / help / money appreciated xx

 

[steve the fish]

Why not just add 7 to M1?

 

[MonkeyFace28]

Why not just add 7 to M1?

but that wouldn't give me the first working monday would it?

 

[steve the fish]

You said you already have that. Cant you not just add 7 days to it?

 

[MonkeyFace28]

You said you already have that. Cant you not just add 7 days to it?

I said I already have my "first" week commencing date - not that it's a Monday... because it isn't, it's a Wednesday because of the UK bank holidays. so you see, if I add 7 to my first date of 02/01/2019 - i will get the 09/01/2019 which is a Wednesday

 

[Tim_Excel_]

Maybe I am seeing this wrong but..

=WK1!M1 - WEEKDAY(WK1!M1,2)+1

You are subtracting and not adding

 

[MonkeyFace28]

Maybe I am seeing this wrong but..

=WK1!M1 - WEEKDAY(WK1!M1,2)+1

You are subtracting and not adding

Ohh wow... OK... ermm... soo yeah... I'm just going to go now... *hangs head in same* been one of those mornings teehee

Thanks Tim xx

 

[Tim_Excel_]

Can't say I've never had such days! Good luck.

 

[steve the fish]

To produce the next monday from any date you could use:

=M1-WEEKDAY(M1,2)+8

The subtraction of the weekday formula is intentional. It isnt really going to work when you reach the next bank holiday which is then going to produce the monday still. So use:

=WORKDAY(M1-WEEKDAY(M1,2)+7,1,holidays)

where holidays is a named range containing the holiday dates.

 

[MonkeyFace28]

To produce the next monday from any date you could use:

=M1-WEEKDAY(M1,2)+8

The subtraction of the weekday formula is intentional. It isnt really going to work when you reach the next bank holiday which is then going to produce the monday still. So use:

=WORKDAY(M1-WEEKDAY(M1,2)+7,1,holidays)

where holidays is a named range containing the holiday dates.

IT'S ALIIIIVVVVVEEEEEEE

yay thank you soo much, it works!! you're the best xx