leezac
asked on
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*Ho
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
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*Ho
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
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"
ASKER
I am going to try this
Range("O10").FormulaR1C1 = "Day's Total Earned Inc"
Range("O11:O" & row - 1).FormulaR1C1 = "=IFERROR(VLOOKUP(TODAY(),
Range("O11:O" & row - 1).Style = "Comma"
Range("O10").FormulaR1C1 = "Day's Total Earned Inc"
Range("O11:O" & row - 1).FormulaR1C1 = "=IFERROR(VLOOKUP(TODAY(),
Range("O11:O" & row - 1).Style = "Comma"
ASKER