How and where do I add the dateweekday Return_type 11

[ThomasBP]

Hi

I have this code where Sunday is the first day, and I now need Monday to be the first day in a week.

Please Login or Register  to view this content.

at /7)*7 I can add a +2 for Monday and so on, but I'm getting some error with Sunday because it's the first day in a week, I get wrong week numbers for that day/dates.
So I need to add the Return_Type 11 so Monday is the first day of a week.

But where do I add it and how?

[AliGW]

Welcome to the forum!

What is the formula intended to do?

[ThomasBP]

Hi and Thx.

It's color some cells on a sheet if I have a date that's Monday or another day, but here its Monday - the dates it's getting from the Year sheet.
Skærmbillede 2018-07-09 kl. 14.28.17.png

And as you can see it's color the Sunday 2/9/2018 in the week 36 (I know the formula is in Saturday, but it's was for testing the date), and not 35 - that's why I need it to look at Monday as the first day, and not Sunday :-).
The year sheet is like this:
Skærmbillede 2018-07-09 kl. 16.20.34.png

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[ThomasBP]

My yearplan soccer

[AliGW]

I'm not clear about what you want, sorry.

Can you please provide the grid with the colouring done manually (without any conditional formatting) to show what you want the result to be?

[NickNunes]

If I understood your problem correctly, you need your calendar to paint the dates you're going to play soccer based on the schedule posted on sheet #2, is that correct?

[ThomasBP]

Yes, that's right, I have manually painted the schedule so it's from the dates from sheet "Year".
And where the type is "Træning", so if it's "bit" then no color.

The Monday (mandag) is my first day of the week, and Sunday (Søndag) is my last day of a week.

[Glenn Kennedy]

I assume that the dates in B3 should have been 2018... as the data are (nearly) all 2018.

Does this do what you wanted??

[ThomasBP]

Hi Glenn

WOW, yes it's great - but, my soccer season is from week number 31 in 2018 for the first half and then from week number 1 to 30 for the second half 2019 - that's why I got 2 dates B2 and B3 on the Year/Året sheet.
if better I can switch 2018 and 2019 so I start with week 31 of 2018 and not week 1 so I go from 31-52 (2018) and then 1-30 (2019).

So the year plan is from 31/2018 till 30/2019, will that do anything with the code/formula, have tried to changes some of the values, but then it's not working..

[Glenn Kennedy]

Lots of changes to make it look more rational... first 2018 and then 2019.....

[ThomasBP]

Great work and THX. a lot Glenn.
It's working 100% - one thing I can see is that week 1 in 2019 is Week 53 in 2018, because of the date 31/12/2018 = Monday.
but I can live with that...

[Glenn Kennedy]

Good!!! Cos part of the week is week 53 of 2018 and part week 1 of 2019...
You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.