Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Work day formula

Posted on 2014-01-21
Medium Priority
383 Views

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
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2

LVL 50

Assisted Solution

barry houdini earned 1332 total points
ID: 39798603
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

LVL 23

Assisted Solution

Danny Child earned 668 total points
ID: 39798615
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 Comment

ID: 39798617
I will try it.  Thanks
0

Author Comment

ID: 39798645
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

LVL 50

Accepted Solution

barry houdini earned 1332 total points
ID: 39798661
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 Comment

ID: 39798874
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 Comment

ID: 39798906
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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…