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!

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

        You are welcome