Solved

I need to update an excel formula

Posted on 2014-01-21
4
187 Views
Last Modified: 2014-01-22
Just like last question, except instead of adding to the date in coumn V, I need it to subtract the total days from it i.e., 14 + X number of days

Here is the original post:

I need an excel formula to look at the value of certain cells.

1. If the Length of the value in column V is less than or equal to 1500, then the length needs to be divided by 175. In the attached example this would be V2/175=5.7 days (6 days).

2. Take the answer from number 1 (6 days)and add 14 more days to the value in column I. These only need to be Network Days, not counting Saturday or Sundays

3. If the Length of the value in column V is greater than 1500, then the length needs to be divided by 300. In the attached example this would be V3/300=5 days (5 days).

4. Take the answer from number 3 (4 days) and add 14 more days to the value in column I. These only need to be Network Days, not counting Saturday or Sundays

5. If day ends up being a fraction, 4.32 days, round up to 5 days.
0
Comment
Question by:wrt1mea
  • 2
4 Comments
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 250 total points
ID: 39798351
Try this update:

=WORKDAY(I2,-14-ROUNDUP(V2/IF(V2<=1500,175,300),0))

Thomas
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 39798445
Hey Ted!

Thomas' version works great, I think, but just for some variety I'll stick with CEILING, i.e.

=WORKDAY(I2,-CEILING(14+V2/IF(V2>1500,300,175),1))

regards, barry
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39798485
OK guys....I will test in the morning...Thanks!
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 39800403
WORKS GREAT!!!!!!!!!
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VLOOKUP. How to return "Not Found" if Item or Qty is missing in Lookup table? 11 38
Dropbox in Windows Server 2008 4 32
VBA Help 18 45
Problem to file 5 17
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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