Work day formula

Can someone please help with this Holiday formula?



I have added detail below. but have what I think will work for Holidays if someone can help me.  
Example:
If Date in F4 of Sheet1 matches date in column B on Sheet2  then multiply value in column 0 by value in column D on Sheet2, else if date does  not match then do nothing.

_____________________________________________________________________________
Basically what I need is
a formula where ONLY if there is a Holiday  then the
 if the Holiday is On a Monday, the value in Column “O” would multiple by 3days.

On a Tuesday after a Monday Holiday Column O would be *4.
On a mid-week business day after a holiday it would be *2
If Holiday on Friday then *4

else the cells in column O are not multiplied by a number.
-----------------------------------------------------------------------------------------------------

I added a Holiday table and added a cell for Today()

Using a formula that I show on the first tab
=IF(O19=Holidays!B2,O13*Holidays!D2) - it works but I need help with adding Index and Match or just a better way to do this.

This is what I came up with for doing Holidays - There may be a better way and that is why I am posting.


Thanks in advance

I have not worked with Holidays and not sure how to work with Excel.
Workdays--2-.zip
leezacAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
If you want the formula in O13 itself then just alter my version like this:

=IFERROR(VLOOKUP(O19,Holidays!B$2:D$9,3,0),1)*E13*J13

where O19 has your date

If you always want today's date you could use that directly in the formula

=IFERROR(VLOOKUP(TODAY(),Holidays!B$2:D$9,3,0),1)*E13*J13

regards, barry
0
 
barry houdiniConnect With a Mentor Commented:
Hello leezac,

This version will pull the relevant multiplier from your table using VLOOKUP - if the date isn't in the table there's an error and IFERROR will give you 1 so keeping O13 as is

=IFERROR(VLOOKUP(O19,Holidays!B$2:D$9,3,0),1)*O13

Is that what you need?

regards, barry
0
 
Danny ChildConnect With a Mentor IT ManagerCommented:
Hi,
here's a version that seems pretty close, if my understanding is right...

It looks at your specified date, compares it to the list of holidays, and if it matches (ie ISNUMBER test is True), then it finds the right multiplier to use, and applies that to cell 019.

For the multiplier part, I've used the TEXT function to strip out the Day name of each HolidayDate, and then I run that through a VLOOKUP to find the multiplier to use.
 - this seems to work OK but I have a different value on the multiplier than you do for Mondays, but I may be confusing your text above with some of the data in the sheet itself - The sheet lists Mondays as a 4 multiplier, but the text says 3?
ee---workdays-multipliers.xls
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
leezacAuthor Commented:
I will try it.  Thanks
0
 
leezacAuthor Commented:
Thank you so much  - I think either one will work - am working on more this evening.

The ONLY thing is that the Cells in O have formulas already like =E13*J13

so would I add another column or is there a way to use the existing formula with the new one.

Not a bad way to handle Holidays - this was my first attempt.
0
 
leezacAuthor Commented:
I will use todays date

I just discovered that the formula is being added using code,

  Range("O10").FormulaR1C1 = "Day's Total Earned Inc"
        Range("O11:O" & row - 1).FormulaR1C1 = "=RC[-10]*RC[-5]"
        Range("O11:O" & row - 1).Style = "Comma"
0
 
leezacAuthor Commented:
I am going to try this
  Range("O10").FormulaR1C1 = "Day's Total Earned Inc"
        Range("O11:O" & row - 1).FormulaR1C1 = "=IFERROR(VLOOKUP(TODAY(),Holidays!B$2:D$9,3,0),1)*E13*J13"
        Range("O11:O" & row - 1).Style = "Comma"
0
All Courses

From novice to tech pro — start learning today.