Irregular Custom Calendar

[limewire]

Greetings!

I'm having trouble creating a calendar that will mimic a Broadcast calendar. 

So I am trying to create a custom calendar where:

1. Each week starts on a Monday and ends on a Sunday
2. Each month starts on the Monday of the week containg the 1st calendar day of the month (if the first of the month is Monday then the month starts off 'normal') 
3. Each day of the week is labled (Monday always = Day "1", Tuesday always = Day "2", Wednesday always = Day "3")
4. Each week in the broadcast year is labled (each month = 4 or 5 weeks, week 1 is in January)

I've done this semi-manually in excel and have attached the image to help visualize what this could look like.

Any help or gudience would be greatly appreciated! 

[v-shex-msft · Accepted solution]

HI @limewire ,

You can refer to following calculated column formulas to achieve your requirements.

#1:
Week Num = WEEKNUM([Date],2)

#2:
Start from monday =
IF (
    WEEKDAY ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ), 2 ) = 1,
    "Normal"
)

#3:
Day of week= WEEKDAY([Date],2)

#4:
Week Index =
WEEKNUM ( [Date], 2 )
    - WEEKNUM ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ), 2 ) + 1

Regards,

Xiaoxin Sheng

[limewire]

@v-shex-msft 

Thank you so much for your assistance and help!!!!!

#1 & #3 were immensely helpful and both work! #3 is almost perfect! Thanks!

As for the rest, this victory may be all I get as #2 & #4 did not work; I'm thinking this might just be impossible.

#4 displayed numbers 1 - 5 grouped in various ways I did not completely understand. 

#2 produced a strange column that had uneven groupings of the word "Normal"... by Start on Monday I meant the calendar itself needed to start on Monday and contain the 1st date of the week. 

For 2018, the first of the broadcast year (like the first of every broadcast year) occurs on a Monday.

For 2019, 2020 and 2017 however, it is not so lucky->

for 2017 the Broadcast years begins on Monday, December 26, 2016 since January 1st is a Sunday. Monday Dec 26-Sunday January 1st is the first week of the 2017 broacast year. 

For 2019, the first week of the broadcast year is Monday, December 31st, 2018 (the first of the year is Tuesday)

For 2020 the first week of the broadcast year is Monday, December 30th, 2019 (the first of the year is wednesday)

And this rule holds for every month as well- the first week of the month begins on Monday and contains the 1st of the regular calendar month.

... is creating this calendar possible?

[HarryT]

Did you ever get a good working model yet?.

I am looking for the same thing.

Thanks

[v-shex-msft]

HI @limewire ,

# 4 formula means the week(Monday to Sunday) sort order of each month.(1st ~5th)  I am used current week number with 'start of current month' week number to calculate correspond week sort order.

If you want to know the detail week number of each date based on rule 'Monday to Sunday', you can use weeknum function with option mode 2 to calculate:

Week Number= WEEKNUM ( [Date], 2 )

For # 2, I'm not so clear for this. Did you mean add tag to first Monday of each months? If not, please share some sample data and expected result to help us clarify your requirement.

Regards,

Xiaoxin Sheng