Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATETABLE

Hi,   I have created a datetable using  Table = ADDCOLUMNS(CALENDARAUTO(),"MONTH",MONTH([Date]),"Year",YEAR([Date]))   How do I get last working day for each of the month?
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Does this work?

    ADDCOLUMNS(CALENDARAUTO(),"MONTH",MONTH([Date]),"Year",YEAR([Date]),"Last working day",EOMONTH([Date],0)-if(WEEKDAY(EOMONTH([Date],0),2)<=5,0,if(WEEKDAY(EOMONTH([Date],0),2)=6,1,2)))

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

Does this work?

Table = ADDCOLUMNS(CALENDARAUTO(),"MONTH",MONTH([Date]),"Year",YEAR([Date]),"Last date",EOMONTH([Date],0))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Ashish_Mathur , By using the formula, I get the last day of each month. But, what I'm after is the last working day for each month, For eg: In May 2020, the ;ast working day was 29/05 because 30 and 31st was a weekend.

     

    Hope this makes sense.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      Does this work?

      ADDCOLUMNS(CALENDARAUTO(),"MONTH",MONTH([Date]),"Year",YEAR([Date]),"Last working day",EOMONTH([Date],0)-if(WEEKDAY(EOMONTH([Date],0),2)<=5,0,if(WEEKDAY(EOMONTH([Date],0),2)=6,1,2)))

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Ashish_Mathur , That works. Thanks!