Solved

10 working days before each Jan and July 15

Posted on 2014-12-25
18
112 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
  • +2
18 Comments
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40517904
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
ID: 40517906
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
ID: 40517914
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:pdvsa
ID: 40517981
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
ID: 40517982
Hit send accidentally.
However since I am needing 10 business days before maybe now it should be a 15.
0
 

Author Comment

by:pdvsa
ID: 40517985
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
ID: 40518179
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
ID: 40518590
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
ID: 40518959
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
 
LVL 50
ID: 40518993
>> 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
ID: 40518997
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
ID: 40518999
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
ID: 40519113
Thanks guys.  I am away from the computer but will respond shortly but possibly tomorrow.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40519679
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
ID: 40519682
@gustav, this was incorrectly tagged under MS Access.  He needs an Excel solution. :-)
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40519697
It is. VBA works in Excel too.

/gustav
0
 

Author Comment

by:pdvsa
ID: 40519794
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
ID: 40520106
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

737 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