Different ways to calculate a calendar with or without bank holiday with a formula in an excel report

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

formula excel

 

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 or not:

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

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

=EOMONTH(A2,0)

formula excel formula excel

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)

formula excel

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)

formula excel

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:

formula excel

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.

formula excel

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.

formula excel

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.

formula excel

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.

formula excel

Interesting Management