cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: DATETABLE

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)))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Highlighted
Super User V
Super User V

Re: DATETABLE

Hi,

Does this work?

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper IV
Helper IV

Re: DATETABLE

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.

Highlighted
Super User V
Super User V

Re: DATETABLE

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)))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Helper IV
Helper IV

Re: DATETABLE

Hi @Ashish_Mathur , That works. Thanks!

Highlighted
Super User V
Super User V

Re: DATETABLE

You are welcome


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors