Improve company productivity with a Business Account.Sign Up

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

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
0
leezac
Asked:
leezac
  • 4
  • 2
3 Solutions
 
barry houdiniCommented:
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 ChildIT 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
 
leezacAuthor Commented:
I will try it.  Thanks
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
barry houdiniCommented:
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
 
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
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now