Solved

Add Days - Not Workdays

Posted on 2014-11-02
4
74 Views
Last Modified: 2014-11-03
Hello, Is the below formula correct to add simply 45 days (not WORKDAYS)?   I think it is but not certain.  

=WORKDAY(EDATE(H67,CHOOSE(MATCH(D68,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,1,Holidays_US_Jap)+45

thank you
0
Comment
Question by:pdvsa
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 40418815
Yes. Excel stores dates as integers and times as decimals, so to add days to a date, simply add a whole number.
0
 

Author Comment

by:pdvsa
ID: 40419610
thank you.  Is it adding 45 days because this criteria is outside of the formula?  Meaning that if I wanted to add 45 WORKDAYS then I would need to put it inside the formula?  

thank you
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40419691
^Correct; this adds 45 calendar days to the result of the WORKDAY() function.  If you wanted to add 45 workdays to the result, you'd change the days argument from 1 to 45:
=WORKDAY(EDATE(H67,CHOOSE(MATCH(D68,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,45,Holidays_US_Jap)

Note the difference:  If the date in H67 is 11/1/2014 and the measure value in D68 is "monthly" then:
A) The formula without any 45-day change  = 12/1/2014
B) The formula with 45 calendar days = 1/15/2015
C) The formula with 45 workdays = 3/20/2015

Regards,
-Glenn
0
 

Author Comment

by:pdvsa
ID: 40419948
thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

821 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