Solved

Work day formula

Posted on 2014-01-21
7
328 Views
Last Modified: 2014-01-21
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
Comment
Question by:leezac
  • 4
  • 2
7 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 333 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

by:DanCh99
DanCh99 earned 167 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

by:leezac
ID: 39798617
I will try it.  Thanks
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:leezac
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

by:
barry houdini earned 333 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

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
 

Author Comment

by:leezac
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

by:leezac
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now