incorporating Bank Holiday into formula

[LearningEXL]

Hi all, i have posted this on another forum but i tried to view the website again and my pc said that it was infected by maleware so pc would not allow me to go there.

here is my issue, i just managed to get all my coding done on my workbook when i realized that i needed to incorporate bank holidays into it.

13266

there are 2 pairs of pages one set has formulas on and the others dont.

Col K on the Agency Daily Input looks for a rate of pay based on company


=IF(H9="",I9*R9,IF(H9,VLOOKUP(C9,Full_Time_Contracts!$C$9:$P$10000,12,FALSE)*R9))

then references col R which is a pay rate


=VLOOKUP(C9,Full_Time_Contracts!$C$9:$K$100000,IF(AND(WEEKDAY(B9)>1,WEEKDAY(B9)<7),7,IF(WEEKDAY(B9)=7,8,IF(WEEKDAY(B9)=1,9)+IF(F9>0.708333333333333,1))),FALSE)

but what i neglected to incorporate which i said at the beginning was bank holidays, which i have put into col S on the Agency Daily Input.

so if Col D says Bank Holiday i need col K to ignore the rate thats in col R and instead use the Value in col s

[p45cal]

=IF(H9="",I9*R9,IF(H9,VLOOKUP(C9,Full_Time_Contracts!$C$9:$P$10000,12,FALSE)*IF(D9="Bank Holiday",S9,R9)))?

[LearningEXL]

=IF(H9="",I9*R9,IF(H9,VLOOKUP(C9,Full_Time_Contracts!$C$9:$P$10000,12,FALSE)*IF(D9="Bank Holiday",S9,R9)))?
thank you sir but would the same be done for the over time as well


=IF(H9="","",IF(H9,VLOOKUP(C9,Full_Time_Contracts!$C$9:$P$10,13,FALSE)*R9*H9))

what this bit of code does it if the company pays overtime after 8 hours or what every they decided then the overtime bank holiday money would also be what ever is in O9 on the Full time contracts sheet

[mancubus]

hi.
add a worksheet. rename it as holidays.
insert holiday dates in year(s) starting from A1 to Ax. (you'd better define a named range)

below formula returns FALSE, if the date in question is in holiday list:
=ISERROR(MATCH(B9,holidays!$A$1:$A$x,0))

so you can define vlookup col_index_num (pay rate column) as (eg 10)
=IF(ISERROR(MATCH(B9,holidays!$A$1:$A$x,0))=FALSE,10)

you should add this as first condition; after IF(.

[mancubus]

it seems i forgot clicking Post Quick Reply button. :)

[LearningEXL]

hi.
add a worksheet. rename it as holidays.
insert holiday dates in year(s) starting from A1 to Ax. (you'd better define a named range)

below formula returns FALSE, if the date in question is in holiday list:
=ISERROR(MATCH(B9,holidays!$A$1:$A$x,0))

so you can define vlookup col_index_num (pay rate column) as (eg 10)
=IF(ISERROR(MATCH(B9,holidays!$A$1:$A$x,0))=FALSE,10)

you should add this as first condition; after IF(.
wow that sounds complicated surely but giving the user the option of entering Bank Holiday and day type is much easier rather than compiling a whole new page

[mancubus]

MS Excel's excellence is that it enables one to provide multiple solutions to a single requirement.

the formula i posted is just an addition to IF conditions in your complex(!) formula. :)

i am currently using a modified version of this formula to conditional format the days of month in a simple 12-month calendar with which i keep track of my routine tasks...

[LearningEXL]

MS Excel's excellence is that it enables one to provide multiple solutions to a single requirement.

the formula i posted is just an addition to IF conditions in your complex(!) formula. :)

i am currently using a modified version of this formula to conditional format the days of month in a simple 12-month calendar with which i keep track of my routine tasks...

this maybe something that i would probably look into at a later stage but for now i just need to get my present project completed and i now have a about 10 days to complete it before i need to finish off my userform problems.

[LearningEXL]

=IF(H9="","",IF(H9,VLOOKUP(C9,Full_Time_Contracts!$C$9:$P$10,13,FALSE)*R9*H9))

i need to incorporate the bank holiday overtime rate into this formula. the pay is in col S for bank holiday but the overtime rate eg 1.5 is on page Full_Time_Contracts

[LearningEXL]

bump