Check to see if date is a holiday, apply holiday rate

[BenjaminHD]

I am preparing an invoice template which has different rates depending on whether the shift started on a weekday, weekend or a public holiday.

I have a list of Western Australian holidays set up:


| date | serial no of date | hourly rate |

| date | serial no of date | hourly rate |

| date | serial no of date | hourly rate |

| date | serial no of date | hourly rate |


etc


My input cell is formatted as a date and time cell. I've tried working out an IF formula that basically works along the lines of if start date is a weekday, apply rate 1 if start date is a weekend apply rate 2 and if start date is a public holiday apply rate 3. I can do the first two tests but I cannot workout how to get it to check the list of public holidays and give me a true or false answer.


Please help!

 

[BenjaminHD]

I've come up with a work around that solves my problem!

 

[Faseeh]

Hi BenjaminHD,


Welcome to the Forum!


A sample workbook will always be helpful. Please see this:


http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

 

[SirJB7]

Hi, BenjaminHD!

In this cases it'd be a nice gesture to post the founded solution so as other people who read this might have access to the solution.

Regards!