4 Replies Latest reply: Aug 14, 2017 6:53 AM by Petter Skjolden RSS

    Changing Date Format

    Alison Cooke

      Hello,

       

      I would like some help with date formatting.

       

      I am using this master calendar:

       

      Temp:

      Load num#([Date],'#') as NumericDate

      RESIDENT [Dates];

       

       

       

       

      MinMax:

      LOAD Min(NumericDate) AS MinDate,

         Max(NumericDate) AS MaxDate

      RESIDENT Temp;

       

       

      LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

       

       

      MasterCalendar:

      LOAD

      text(num(MonthStart(Date($(vMinDate) + RecNo() - 1)))) AS [Date],

      Year(Date($(vMinDate) + RecNo() - 1)) as [Year],

      Month(Date($(vMinDate) + RecNo() - 1)) as [Month],

          ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,

      Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear]

      AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

       

       

      DROP TABLE MinMax;

      DROP TABLE Temp;

       

      But my dates appear in the 40295, 42102 format.

       

      Is there any changes I can make to my master calendar in order to ensure that it is in the DD/MM/YYYY format?

       

      Best wishes,

      Alison