10 working days before each Jan and July 15

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.


please see attached file.  It seems the formula does not return the correct dates.
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
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?
Robert ShermanOwnerCommented:
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.
Robert ShermanOwnerCommented:
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

Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

pdvsaProject financeAuthor Commented:
Hi Robert,
Typing from phone...

This is a follow up from

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.
pdvsaProject financeAuthor Commented:
Hit send accidentally.
However since I am needing 10 business days before maybe now it should be a 15.
pdvsaProject financeAuthor Commented:
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.
Robert ShermanOwnerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
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.
Glenn RayExcel VBA DeveloperCommented:
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:
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:

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):

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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>> 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
Robert ShermanOwnerCommented:
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 ShermanOwnerCommented:
Thanks, teylyn, for that information... didn't know about the 40-character limit but good to know for future reference.
pdvsaProject financeAuthor Commented:
Thanks guys.  I am away from the computer but will respond shortly but possibly tomorrow.
Gustav BrockCIOCommented:
You can copy your entire basDate module from your Access application into or as a new module in Excel.

 - 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:


Result: 2014-12-31

Glenn RayExcel VBA DeveloperCommented:
@gustav, this was incorrectly tagged under MS Access.  He needs an Excel solution. :-)
Gustav BrockCIOCommented:
It is. VBA works in Excel too.

pdvsaProject financeAuthor Commented:
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.
Glenn RayExcel VBA DeveloperCommented:

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.