WEEKDAY & TODAY function.

[bedgar]

Hi Everyone!

So I have an Excel doc that I print out for a hand written report. I have a week beginning cell that should always show the upcoming Monday date, UNLESS today is Monday, then it should show today's date. I usually print this out on Friday, but last week did not have time to get it done. So today I open it for the first time on a Monday and it is showing me next Monday's date.

This is the code I was using which worked great if not a opened on Monday:

=TODAY()+(7-WEEKDAY(TODAY(),2)+1)

So I attempted to modify it and at first thought I nailed it. To test I set my computer date to a Thursday and opened it up. It showed the date for that Thursday and not the upcoming Monday as expected. Here is that faulty code:

=IF(WEEKDAY(TODAY()=2),TODAY(),TODAY()+(7-WEEKDAY(TODAY(),2)+1))

Any help is appreciated.

 

[Fluff]

Re: Help with a WEEKDAY & TODAY function.

Hi & welcome to MrExcel
Try
=IF(WEEKDAY(TODAY(),2)=1,TODAY(),TODAY()+(7-WEEKDAY(TODAY(),2)+1))

 

[bedgar]

Re: Help with a WEEKDAY & TODAY function.

That was it. Thank you Fluff! You made my day.

 

[Fluff]

Re: Help with a WEEKDAY & TODAY function.

You're welcome & thanks for the feedback

 

[Scott Huish]

Re: Help with a WEEKDAY & TODAY function.

I know you asked for WEEKDAY and TODAY, but here is a shorter way:
=WORKDAY.INTL(TODAY()-1,1,"0111111")

Format as date.

 

[Tetra201]

Re: Help with a WEEKDAY & TODAY function.

Here is a shorter way with WEEKDAY and TODAY:

=TODAY()+7-WEEKDAY(TODAY(),12)

 

[bedgar]

Re: Help with a WEEKDAY & TODAY function.

Thanks Scott and Tetra. Simplified is definitely better. I have been humbled like always. You guys all rock!