How to populate date wise table for all the working days in the month

[ThrottleWorks]

Hi,

I am trying to populate a table for all the working days in a month.
For example, today is 6th Feb. So I need to prepare a table for Jan 2018.

I have prepared a table manually. Please see attached table for reference.
Also, I need to allocate different color for each week. I will do it with VBA.

I am not able to understand how to get all the working days of previous month based of today's date.

Can anyone please help me in this.

 

[Chihiro]

You can't using formula alone. You will need list of holidays.

To get first business day of previous month...
In A2: =WORKDAY(EOMONTH(Today(),-2),1,[Holiday_List])

Then A3 onward.
=WORKDAY(A2,1,[Holiday_List])

To color each week, just use WeekNum() function to check which week each date falls in and color accordingly.

 

[Hui]

Copy this into any cell in Row 2
=WORKDAY.INTL(DATE(2018,1,1),ROWS($A$2:A2),1)
copy it down

If you want the previous month for any date
=WORKDAY.INTL(EOMONTH(TODAY(),-2)+1,ROWS($A$2:A2),1)

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help. I guess @Hui sir's formula will work for me. Thanks for suggesting easier way for getting week number. I had forgotten this. Have a nice day ahead.

 

[ThrottleWorks]

Hi @Hui sir, thanks a lot for the help. Your formula has worked for me. Have a nice day ahead.