Solved

10 working days before each Jan and July 15

Posted on 2014-12-25
18
108 Views
Last Modified: 2015-01-01
Experts, I want to confirm that the below will return 10 working days before each jan and July 15 of each year.  I also have a holiday tab as you can see but this is not part of my question.  
  I am dragging the formula down many rows.

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+6,16),-10,Holidays_US_Jap)

please see attached file.  It seems the formula does not return the correct dates.
10-working-days-before-Jan-and-Jul-15.xl
0
Comment
Question by:pdvsa
  • 6
  • 6
  • 3
  • +2
18 Comments
 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
What value are you expecting for 1/15/2016?   Would the correct value be 12/31/2015?  

If so, the problem the problem is that you are calculating the WORKDAY as 10 days before the 16th and not the 15th..  The original value in the spreadsheet that you have labelled as "not correct" is 1/4/2016.  Looking at the calendar for 2016, I see that to be correct if you count backwards from Saturday, 1/16/2016.  10 days before that would be Monday, 1/4/2016, no?
0
 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
By the way, this is incorrectly tagged under Microsoft Access.  Don't know if you have the ability to change that to excel or if an admin has to do it.
0
 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
What version of Excel are you using?  Here's the dates as they are being calculated for me in Excel 2010:

Thu, Jan 15, 2015	enter date
Wed, Jul 01, 2015	correct
Mon, Jan 04, 2016	not correct
Fri, Jul 01, 2016	correct
Fri, Dec 30, 2016	correct
Fri, Jun 02, 2017	not correct…its in early June
Mon, Dec 04, 2017	not correct…its in early december
Mon, Jun 04, 2018	not correct…its in early June
Tue, Dec 30, 2014	correct
Tue, Jun 02, 2015	not correct…its in early June
Wed, Dec 02, 2015	not correct…its in early december
Thu, Jun 02, 2016	not correct…its in early June
Fri, Dec 02, 2016	not correct…its in early december
Fri, Jun 02, 2017	not correct…its in early June

Open in new window

0
 

Author Comment

by:pdvsa
Comment Utility
Hi Robert,
Typing from phone...

This is a follow up from
http://mobile.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28565612.html

Please see the 5th from bottom answer from me.  This is in regards to the 15 or 16.  I thought it should be a 16.
0
 

Author Comment

by:pdvsa
Comment Utility
Hit send accidentally.
However since I am needing 10 business days before maybe now it should be a 15.
0
 

Author Comment

by:pdvsa
Comment Utility
What value are you expecting for 1/15/2016?   Would the correct value be 12/31
==>I blv that is correct considering the holidays too on the holidays tab for 2016.
0
 
LVL 7

Accepted Solution

by:
Robert Sherman earned 500 total points
Comment Utility
OK... I see a major part of the problem now.   If you look at the spreadsheet you uploaded, what you did was you dragged the formula down from A2 down to A14 to fill in those rows.   The problem is, when you get to row 5 where the correct date is Dec 30, 2016 the next row's formula uses Dec 30, 2016 (since it's using the date from the row above it) and it then adds 6 months (bringing us to June) and then sets the day as the 16th... and the result is 6/16/2017, to which it then applies the Workday() funtion and is why you noted "not correct...its in early june".  

So, the problem is the combined formula...  if you take the formula apart, and just have one column with either January or July 15, and then do the Workday function in the next column, you will avoid this problem.  

The other part of the problem is whether you are looking for 10 whole business days to have passed BEFORE the 15th, or whether the 15th would be included if it falls on a business day.  

See the attached file where I created examples using both 15th and 16th.
10-working-days-before-Jan-and-Jul-15.xl
0
 

Author Comment

by:pdvsa
Comment Utility
Robert, there might have been an error with the file name.  The .xl should be a .xls I believe.  The file will not download as excel.  I think you might need to repost the file.
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
If I recall correctly, from previous questions, you need a formula that will automatically calculate a Future Due Date from any Given Date.  In this instance, the Future Due Date will always be ten (10) business days before the calendar end of the six-month period in which the given date falls (a "Period End Date").  The key to solving this problem is in determining that Period End Date, which in your case here will either be July 15 of the given date's year or January 15 of the year after the given date.

Mapping out example results would surely help.  If the Given Date were April 2, 2015, then I presume that you want the future due date to be ten days before July 15, 2015, which is June 30, 2015.  If the Given Date were October 14, 2015, the future due date would be ten days before January 15, 2016, or December 31, 2015.  These are also incorporating your Holiday table.

Supposing the above is correct, what do you then do when the Given Date falls on or after the future due date but before the 15th of January or July?  For example, if the Given Date is January 5, 2015, it seems that the future due date should actually be June 30, 2015 (and not December 30, 2014).  This is why simply adding six months into the date doesn't work.

This explains why early versions of your formulas used the CHOOSE method to return a date result based on the month of the given date, like so:
=DATE(YEAR(A1),CHOOSE(MONTH(A1),7,7,7,7,7,7,13,13,13,13,13,13),15)
The 7's force a July date; the 13's force a January date in the following year.

And so you could incorporate that same logic here:
=WORKDAY(DATE(YEAR(A1),CHOOSE(MONTH(A1),7,7,7,7,7,7,13,13,13,13,13,13),15),-10,Holidays_US_Jap)

The problem with this formula is that if the date in cell A1 happens to also be a Future Due Date, it does not roll forward to the next six-month period, which I believe you intend.  Example:  a Given Date of June 30, 2015 returns June 30, 2015 also.  If I'm correct in your assumptions, you'd want that date to actually be December 31, 2015.  So, the formula is tweaked in the CHOOSE arguments (essentially phase-shifting by one month across periods):
=WORKDAY(DATE(YEAR(A1),CHOOSE(MONTH(A1),7,7,7,7,7,13,13,13,13,13,13,19),15),-10,Holidays_US_Jap)

This, unfortunately, introduces a new problem in that all June and December dates roll to the next period.

See the attached workbook with details and examples of all this.  I suspect it still needs revision once you can get the correct definitions of the Future Due Date from your customer.

Regards,
-Glenn
EE-10-working-days.xlsx
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 50

Expert Comment

by:teylyn
Comment Utility
>> Robert, there might have been an error with the file name.

EE truncates file names after the 40th character. If you rename the file to have the expected extension you should be able to open it. Since Robert mentions using XL 2010 earlier, you may want to try xlsx

cheers, teylyn
0
 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
pdvsa:  Not sure why, but if you look at your original upload you will see that Experts Exchange also uploaded your file with a .xl extension.   I simply saved the file to disk and then renamed it and gave it an xls extension.   I didn't even noticed when I uploaded it that it did the same thing to the file name..

The file, on my hard drive, has an .xls extension.  For some reason, uploading .xls is causing the extension to get truncated to .xl -- you should be able to just save the file (right click, save as.. if you need to do it that way) and then rename it to .xls

-Robert
0
 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
Thanks, teylyn, for that information... didn't know about the 40-character limit but good to know for future reference.
0
 

Author Comment

by:pdvsa
Comment Utility
Thanks guys.  I am away from the computer but will respond shortly but possibly tomorrow.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You can copy your entire basDate module from your Access application into or as a new module in Excel.

Then:
 - add a reference to Microsoft Office 15.0 Access database engine Object Library
 - modify the call to your Holiday table in function GetHolidays:

        Set dbs = DBEngine(0).OpenDatabase("d:\yourpath\ee.accdb", , True)

- modify this codeline in function DateAddWorkdays to include Sgn():

            ' Add one week to cover cases where a week contains multiple holidays.
            lngDiffMax = lngDiffMax + Sgn(lngDiffMax) * clngWeekdayCount

- compile and save
- insert 2015-01-01 as a Holiday in your tblHoliday

Now you can use your functions like this where D1 is the cell containing your date:

=DateAddWorkdays(-10,D1)

Result: 2014-12-31

/gustav
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
@gustav, this was incorrectly tagged under MS Access.  He needs an Excel solution. :-)
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
It is. VBA works in Excel too.

/gustav
0
 

Author Comment

by:pdvsa
Comment Utility
Robert:  I think I can use that.  I will add another column just as you did.  

Glenn:  I am not sure if cell A2 should be Dec 26, 2014.  I  am not sure why June 30, 2015 is in cell A2 but if that is the date that is suppose to be 10 working days before Jan 15, 2015 and taking note of the holidays.  I think it should be Dec 30, 2014 but maybe it was an example to show the inaccuracies of the formula.  I think in columns D and E it is basically the same as Roberts solution.  

Gustav: thank you for that.  I wil try to implement but it is a little out of my league.  I could probably implement but would need some time.  

If no objections, I shall award Robert.
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
pdvsa:

In my example file, if cell A1 is January 15, 2015, then cell A2 IS June 30, 2015.  I don't know why you are seeing otherwise.  I am doing the following:

The value in cell A1 is a "given date."  Calculate then next six-month ending date after this date (July 15, 2015), then find the date that is ten (10) business days before that - June 30, 2015.

If you problem is anything other than that, then I apologize for misunderstanding you.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 …
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.

763 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