Print this page

Formula to Calculate the number of working days in the current month and show the current working day for that month

Knowledge Article Number 000212745
Description How to calculate the number of working days in the current month and also show the current working day for that month:

Example:
If today is 23rd of Feb

Then

Number of Working days = 20 days (excludes weekends)
Current working day = 16
Resolution This requirement can be achieved by creating 2 formula fields:
  • Create a Formula field called Working Days with the following formula
(
( FLOOR ( ( DATE (
YEAR (DATE (YEAR(TODAY()),MONTH(TODAY()),28)+5),
MONTH(DATE (YEAR(TODAY()),MONTH(TODAY()),28)+5),
1 ) - 1 - DATE (1900,01,01) ) / 7 ) *5 )
+ MIN ( 5, MOD ( DATE (
YEAR (DATE (YEAR(TODAY()),MONTH(TODAY()),28)+5),
MONTH(DATE (YEAR(TODAY()),MONTH(TODAY()),28)+5),
1 ) - 1 - DATE (1900,01,01), 7 ) )
) -
(
( FLOOR ( ( DATE(YEAR(TODAY()),MONTH(TODAY()),1) - DATE (1900,01,01) ) / 7 ) *5 )
+ MIN ( 5, MOD ( DATE(YEAR(TODAY()),MONTH(TODAY()),1) - DATE (1900,01,01), 7 ) )
)
  • Create another Formula field called Current Working Day with the following formula
(
( FLOOR ( ( TODAY() - DATE (1900,01,01) ) / 7 ) *5 )
+ MIN ( 5, MOD ( TODAY() - DATE (1900,01,01), 7 ) +1 )
) -
(
( FLOOR ( ( DATE(YEAR(TODAY()),MONTH(TODAY()),1) - DATE (1900,01,01) ) / 7 ) *5 )
+ MIN ( 5, MOD ( DATE(YEAR(TODAY()),MONTH(TODAY()),1) - DATE (1900,01,01), 7 ) )
)

Note: Only weekends are excluded. Holidays cannot be excluded using the above formula




promote demote