Check if the day is a bank holiday after converting day and month into one single cell with a formula in an excel report

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.

formula excel


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 ?






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:


Then just copying it to the below cells.

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

formula excel

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:


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.

formula excel

Interesting Management