I am trying to create a timesheet spreadsheet (in Excel 2013) and I need it to identify if a specific date is a bank holiday and input "bank holiday" for example I have tried inputting =if(C16=04/05/2015,"Bank Holiday","")

C16 is the starting date for the weekly timesheet.

It only returns the "" bit even though I know 4th may 2015 is a bank holiday.

Any help would be very welcome.

message edited by euromadman

Excel is evaluating the 4/5/2015 as a number, not a date. To prove this to yourself, click on the cell containing the formula. then click on the Formulas tab, then "Evaluate Formula". Each time you click the Evaluate button, you can watch Excel perform each evaluation step. Eventually you will see 04/05/2015 replaced with 0.000397022332506203, which is not a date.

Try this formula instead, so that Excel knows that you are looking for a date in C16:

=IF(C16=DATEVALUE("4/5/2015"),"Bank Holiday","")

Another option is to put a list of all of your holidays in a list then use ISNA and MATCH to see if the C16 Date is in that list.

=IF(ISNA(MATCH(C16,$B$2:$B$10,0)),"","Bank Holiday")

The ISNA function checks to see if the MATCH function found a match. If the MATCH function returns #N/A, it means that no match was found and ISNA will return TRUE. The IF function will use the TRUE to return an empty cell.

Once again, you can use Evaluate Formula to follow the steps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you so much for responding, its done the trick and what better i understood your answer. Jason

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History