x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 389

# Work day 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.
-----------------------------------------------------------------------------------------------------

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.

I have not worked with Holidays and not sure how to work with Excel.
Workdays--2-.zip
0
leezac
• 4
• 2
3 Solutions

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

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

Author Commented:
I will try it.  Thanks
0

Author 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

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

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

Author 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

Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.