Solved

Work day formula

Posted on 2014-01-21
7
356 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:Danny Child
Danny Child 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

809 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