Forum Discussion

ssvr's avatar
ssvr
Helper III
8 years ago

Date Difference excluding weekends

Hello, I have a starting date and an ending date and I need to count only the working days in between. In other words, the date difference (DATEIFF) between the ending and the starting without tak...
Need Help
ssvr's avatar
ssvr
Helper III
8 years ago

Thanks.

 

But here they suggested create a Date Table for Isweekday flag.

 

I worked on almost 10 years data so, cant possible to create Date Table with Isweekday flag ??

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi ssvr,

 

Yes. It is supported to create a calendar table with more than 10 years.

 

Regards,

Yuliana Gu

  • ssvr's avatar
    ssvr
    Helper III
    8 years ago

    Hi @Yuliana,

     

    Thanks.

     

    But its difficult to add 10 years canlender and we have to add for future dates also right ?

     

    can you suggest any alternative for this requirement

     

     

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      8 years ago

      Hi ssvr,

       

      You can create calendar table with CALENDAR() function like this:

      Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))

       

      Regards,

      Yuliana Gu

      • ssvr's avatar
        ssvr
        Helper III
        8 years ago

        Thanks v-yulgu-msft

         

        I created a Date table:

         

        Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))

         

        I created a new column with is working day or not

         

        is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

         

        Now I want to create "DateDifference" Column with Createddate & Closeddate (I want to know day diffarence b/w these two dates excluding weekends)

         

        CreatedDate   ClosedDate  DateDiffarence

         

         

        Please guide me how to get this done