Those formulas will allow me first to combine a single date and month into one unique cell displaying the full day including the year then to check a specific day if it is a public holiday.

 

When I use the formula ?

In order to know if a particular day is a bank holiday after getting in one unique cell the full day based on single day and month.

 

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 ?

=IF()

=MONTH()

=DATEVALUE()

=YEAR()

=TODAY()

I have those data, only the date, the month name and the specific day. Before to check if the particular day is a public holiday, I will need to combine the date and month into 1 single cell with the full day. In the cell D2, I will put this formula:

=A2&"/"&MONTH(DATEVALUE(B2&"1"))&"/"&YEAR(TODAY())

Then just copying it to the below cells.

NOTE: MONTH(DATEVALUE(B2&"1")) allows to convert the month name to the month number.

If I want 2 digits instead of 1, I just need to include an “IF()” telling that number inferior to 10, put a 0 before the number so putting this formula in E2:

=IF(A2<10,0,"")&A2&"/"&IF(MONTH(DATEVALUE(B2&"1"))<10,0,"")&MONTH(DATEVALUE(B2&"1"))&"/"&YEAR(TODAY())

Now that I have all the bank holiday of the year, I need to check if my specific day is a bank holiday, the formula in cell F2:

=IF(COUNTIF($E$2:$E$13,C2),"yes bank holiday","not bank holiday")

NOTE: for this example, I use the column E but I can use the column D, the result will be the same.