Adding a Meal and Transport Allowances on Public Holidays

**gloom52** · 09-10-2009, 04:10 PM

I have this column that I wish to calculate the Meal and Transport allowances during holidays. Whenever the "Date" column reaches any of the PH(Public Holiday) dates, it will automatically add 1 unit of Meal and Transport allowance to the individual column. I tried to make the formula myself but I cant seem to make it work. Please provide assistance.

**NBVC** · 09-10-2009, 04:15 PM

Do you have the Holidays listed somewhere in the workbook?

If not, create the list and name it Holidays, through Insert|Name|Define

Then within your current formula, you would add something like

=Your_current_fourmula+If(isnumber(match(D2,holidays,0)),1,0)

where D2 contains the date to check....

If still not of use... post workbook detailing your requirement

**gloom52** · 09-10-2009, 04:18 PM

This is the file to my problem

**gloom52** · 09-10-2009, 04:24 PM

It worked thx very much

**NBVC** · 09-10-2009, 04:24 PM

so where am i looking? I see also two sets of holidays?

**NBVC** · 09-10-2009, 04:32 PM

Well i guess you are looking at columnsI and J of each sheet...

I am still unsure of which holiday list you use... but select one list and go to Insert|Name|Define and then name it Holidays...

Then in the formulas you have currently in I2 and J2, add

+IF(ISNUMBER(MATCH(A4,Holidays,0)),1,0)

and copy down