Forum Discussion

Navaneetharaju_'s avatar
Navaneetharaju_
Icon for Helper II rankHelper II
2 years ago
Solved

Need help in date table creation

Hi All, I have creating the date table for my business purpose -    Date table =  Date = CALENDAR(DATE(2014,01,01), DATE(YEAR(TODAY()),12, 31)) I have created the end date year as a year of t...
Need Help
Show and Tell
johnbasha33's avatar
johnbasha33
Icon for Super User rankSuper User
2 years ago

Navaneetharaju_ 
Date table =
ADDCOLUMNS(
CALENDAR(DATE(2014, 01, 01), DATE(YEAR(TODAY()), 12, 31)),
"LastDayOfMonth", EOMONTH([Date], 0),
"FirstMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) <= 7, 1, 0),
"SecondMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastMondayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 2, 1, 0),
"FirstTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) <= 7, 1, 0),
"SecondTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastTuesdayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 3, 1, 0),
"FirstWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) <= 7, 1, 0),
"SecondWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastWednesdayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 4, 1, 0),
"FirstThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) <= 7, 1, 0),
"SecondThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastThursdayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 5, 1, 0),
"FirstFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) <= 7, 1, 0),
"SecondFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastFridayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 6, 1, 0),
"FirstSaturdayOfMonth", IF(WEEKDAY([Date]) = 7 && DAY([Date]) <= 7, 1, 0),
"SecondSaturdayOfMonth", IF(WEEKDAY([Date]) = 7 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdSaturdayOfMonth", IF(WEEKDAY([Date]) = 7 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthSaturdayOfMonth", IF(WEEKDAY([Date]) = 7 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),

  • Navaneetharaju_'s avatar
    Navaneetharaju_
    Icon for Helper II rankHelper II
    2 years ago

    johnbasha33 , Joe_Barry ,
    Thanks for your response, 

     

    Responses that provided from you both works fine. 

     

    Let keep in touch.

    • johnbasha33's avatar
      johnbasha33
      Icon for Super User rankSuper User
      2 years ago

      Navaneetharaju_  glad it helped you. 

      can you please give Kudos and mark it as solution..

      Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!