I will explain how I get the last day of the month, to calculate the number of days and work days in a month with or without public holiday, to calculate the number of each day of the week (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday) and how many days without them, and to know the first and last date of each day of the week. Additionally, I will also explain how to detect a leap year (leap day of February 29).

 

When I use the formula ?

For instance, to calculate the number of day an office is open.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How are the formula ?

=MONTH()

=DATE()

=YEAR()

=EOMONTH()

=NETWORKDAYS()

=DAY()

=WEEKDAY()

=INT()

=WEEKDAY()

=NETWORKDAYS.INTL()

To know if the date is a leap year (February 29th) or not:

=MONTH(DATE(YEAR(A2),2,29))=2

NOTE: it will return TRUE or FALSE

I have only the start day of the month, and to know the last day, in B2:

=EOMONTH(A2,0)

NOTE: don’t forget to format the cell B2 as “short date”.

I want to calculate the number of days D2 and how many working days E2 (Monday to Friday):

In cell D2: =DAY(EOMONTH(A2,0))

In cell E2: =NETWORKDAYS(A2,B2)

Excluding bank holidays:

In cell D3: =DAY(EOMONTH(A2,0))-COUNTIFS(A9:A12,">="&A2,A9:A12,"<="&B2)

In cell E3: =NETWORKDAYS(A2,B2,A9:A12)

NOTE: don’t forget to format the cell D3 as “general”. A9:A12 is the range from cell A9 to A12 where there is a list of public holidays of the year:

I will count the number of days without Monday, Tuesday, Wednesday, Thursday, Friday, Saturday or Sunday:

In cell G2: =NETWORKDAYS.INTL(A2,B2,"1000000")

In cell M2: =NETWORKDAYS.INTL(A2,B2,"0000001")

NOTE: for the others, you just need to change the “0” (to count) and “1” (to not count). For instance, for Tuesday is “0100000”, etc.

I want to calculate how many Monday, Tuesday, etc.:

In cell G3: =INT((WEEKDAY($A2-2)+$B2-$A2)/7)

In cell M3: =INT((WEEKDAY($A2-1)+$B2-$A2)/7)

NOTE: for the others, you just need to change the “2” and “1” knowing that 1 is Sunday and 7 is Saturday.

I want to know the first date of Monday, Tuesday, etc.:

In cell G4: =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+6)

In cell M4: =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+7)

NOTE: for the others, you just need to change the “6” and “7” knowing that 7 is Sunday and 1 is Saturday.

Now the last day:

In cell G5: =INT((EOMONTH(A2,0)-2)/7)*7+2

In cell M5: =INT((EOMONTH(A2,0)-1)/7)*7+1

NOTE: for the others, you just need to change the “2” and “1” knowing that 1 is Sunday and 7 is Saturday.