Forum Discussion

limewire's avatar
limewire
Helper I
7 years ago
Solved

Irregular Custom Calendar

Greetings!   I'm having trouble creating a calendar that will mimic a Broadcast calendar.    So I am trying to create a custom calendar where: Each week starts on a Monday and ends on a Sunday ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    limewire
    Helper I
    7 years ago

    Anonymous 

     

    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?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

    • HarryT's avatar
      HarryT
      Helper I
      6 years ago

      Did you ever get a good working model yet?.

      I am looking for the same thing.

      Thanks