Workday of the month

[nkhanna]

Hi,

What will be the formula, If I need to have the current workday of the month. This means that if today is the 8th working day of August, then the result in the cell should be 8.

Similarly, I would need to calculate working days in a month, quarter, Quarter to date , year to date and full year


Thanks

 

[MarcelBeug]

Try

=NETWORKDAYS(TODAY()-DAY(TODAY())+1,TODAY())

or, with any holidays taken into account

=NETWORKDAYS(TODAY()-DAY(TODAY())+1,TODAY(),[holidays])

where [holidays] is an optional list of holidays.

Edit: Similarly, you can calculate working days in a month, quarter, Quarter to date , year to date and full year

 

[nkhanna]

Great, this worked for the first query.

Are we aware of the formulas to calculate working days in a month, quarter, Quarter to date , year to date and full year?

 

[Vidar]

Try these (holidays not included):

Current workday of month

=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY())

Workdays in current month

=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0))

Workdays quarter to date

=NETWORKDAYS(CHOOSE(INT((MONTH(TODAY())+2)/3),DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),4,1),DATE(YEAR(TODAY()),7,1),DATE(YEAR(TODAY()),10,1)),TODAY())

Workdays in current quarter

=NETWORKDAYS(CHOOSE(INT((MONTH(TODAY())+2)/3),DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),4,1),DATE(YEAR(TODAY()),7,1),DATE(YEAR(TODAY()),10,1)),CHOOSE(INT((MONTH(TODAY())+2)/3),DATE(YEAR(TODAY()),3,31),DATE(YEAR(TODAY()),6,30),DATE(YEAR(TODAY()),9,30),DATE(YEAR(TODAY()),12,31)))

Workdays YTD

=NETWORKDAYS(DATE(YEAR(TODAY()),1,1),TODAY())

Workdays in current year

=NETWORKDAYS(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31))

 

[MarcelBeug]

Yes we are aware.
Let A1 be the reference date and B1 the start of the quarter (Jan 1, Apr 1, Jul 1, Oct 1), calculated as

=DATE(YEAR(A1),1+3*INT((MONTH(A1)-1)/3),1)

Without holidays taken into account.

Month:

=NETWORKDAYS(A1-DAY(A1)+1,EOMONTH(A1,0))

Quarter:

=NETWORKDAYS(B1,EOMONTH(B1,2))

Quarter to date:

=NETWORKDAYS(B1,A1)

Year to date:

=NETWORKDAYS(DATE(YEAR(A1),1,1),A1)

Full year:

=NETWORKDAYS(DATE(YEAR(A1),1,1),DATE(YEAR(A1),12,31))

 

[nkhanna]

Great Marcel and Vidar. Thanks so much for your help.

 

[Vidar]

You're welcome nkhanna