Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-09-2017, 11:25 AM   #1
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 569
Thanks: 8
Thanked 7 Times in 6 Posts
smiler44 is on a distinguished road
formula for adding calaender days

I want to add a number of days to a date and get the new calendar date but also account for bank holidays.

cell A1 a date
number of days to add
bank holidays in cells A3:I3

something like this

=WORKDAY.INTL(A1, 90, 7, A3:I3) but this is working days and takes into account the bank holidays

please can you help as I'm lost? I have done Goggle searches but could not find what I wanted or I did not understand them

thanks

smiler44

smiler44 is offline   Reply With Quote
Old 06-09-2017, 11:32 AM   #2
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,600
Thanks: 203
Thanked 221 Times in 207 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: formula for adding calaender days

Just search this site.
That question has been asked several times.

This is just one example

https://www.access-programmers.co.uk...=bank+holidays
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 06-09-2017, 12:56 PM   #3
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 569
Thanks: 8
Thanked 7 Times in 6 Posts
smiler44 is on a distinguished road
Re: formula for adding calaender days

Quote:
Originally Posted by Gasman View Post
Just search this site.
That question has been asked several times.

This is just one example

https://www.access-programmers.co.uk...=bank+holidays
Gasman, thank you. that thread got rather heated.
The result seemed to be a macro for working out the number of working days between 2 dates. I was after a formula to add x number of calendar days to a known date but take into account bank holidays.
I hope in did not misunderstand the thread you referred me to

smiler44

smiler44 is offline   Reply With Quote
Old 06-09-2017, 01:43 PM   #4
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,600
Thanks: 203
Thanked 221 Times in 207 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: formula for adding calaender days

Quote:
Originally Posted by smiler44 View Post
Gasman, thank you. that thread got rather heated.
The result seemed to be a macro for working out the number of working days between 2 dates. I was after a formula to add x number of calendar days to a known date but take into account bank holidays.
I hope in did not misunderstand the thread you referred me to

smiler44
If you search some more, you will find that the experts here have supplied code to add days to a date and take into account weekends(non working days)

To include holidays I believe every solution involves a table holding those holidays and taking them into account.

Regardless of the o/p not being appreciative of the help offered, I believe you have a solution there?.

Another link
https://www.access-programmers.co.uk...t=working+days

HTH
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 06-09-2017, 02:08 PM   #5
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 569
Thanks: 8
Thanked 7 Times in 6 Posts
smiler44 is on a distinguished road
Re: formula for adding calaender days

Gasman, sorry I think we have our wires crossed or I really am missing something.
It is not working days I need but calendar days not including bank holidays.
May had 31 days with 2 of them in England being bank holidays.
So on May 1st if I add 28 days I should get my answer as 31.05.17
I cant see how to add 28 days to May 1st not include 2 bank holidays and end up with 31.05.17 as my answer.
Also, I am useless at calling a function so always need help with this.

it does seem that there is no formula and so a macro is the answer but I hope I am wrong

smiler44
smiler44 is offline   Reply With Quote
Old 06-09-2017, 03:01 PM   #6
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 569
Thanks: 8
Thanked 7 Times in 6 Posts
smiler44 is on a distinguished road
Re: formula for adding calaender days

I have just found this formula via a Goggle search
=B2-A2-COUNTIF(E2:E11,">="&A2)+COUNTIF(E2:E11,">"&B2)

it can take note of bank holidays but needs to know the start and end date so it can work out how many calendar days there have been.
If B2 could become a number such as 10 or 20 etc instead of a date, and it was A1 + B2 then this would I think be just what I need.

seems so close

smiler44
smiler44 is offline   Reply With Quote
Old 06-10-2017, 12:34 AM   #7
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,600
Thanks: 203
Thanked 221 Times in 207 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: formula for adding calaender days

No my apologies, I completely misread your post, I thought you were looking for an access version.

This is a little longwinded but at least easy to understand. If you added the number of days you wanted to the start date, that formula would give you the number of days excluding the bank holidays.

Code:
Start	        End	       Add	Days	Hols
28/05/2017	09/06/2017	12	11	01/01/2017
				                14/04/2017
				                17/04/017
				                01/05/2017
				                29/05/2017
				                07/08/2017
				                25/12/2017
				                26/12/2017
So here we have 11 days.
So just add that number to your start date?

Am I understanding you now?

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 06-10-2017, 12:50 AM   #8
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 569
Thanks: 8
Thanked 7 Times in 6 Posts
smiler44 is on a distinguished road
Re: formula for adding calaender days

No worries Gasman, I thought it was me

In your example you are adding 11 calendar days to 28.05.17 but as 29.05.17 is a bank holiday you need to add 12 and end up with 09.06.17
just as you have done.

I understand your example and it is just what I am needing to do.

Have you a method for doing this that's simple?
I spent hours last night and again this morning. I have a method but it needs to be tested some more but it's long winded, its painful it's so long.

I have my start date, I add 11 calendar days an get an end date. I then check if there is a bank holiday between the start date and end date. If there is then for each bank holiday my end date increases by one. Each time it increases I check to make sure the new date is not a bank holiday. If it is then the end date moves out my one.

I don't expect there to be more than 4 bank holidays in a row so I check only 4 times. I'm using a formula to make the checks and give me a new end date in a cell but then a macro to place the correct end date into a different cell for the rest of my formulas and macros to use.

I'll try to post my solution


smiler44
smiler44 is offline   Reply With Quote
Old 06-10-2017, 01:02 AM   #9
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 569
Thanks: 8
Thanked 7 Times in 6 Posts
smiler44 is on a distinguished road
Re: formula for adding calaender days

In the attached example only look at the cells highlighted Green ignore the rest and then see the macro referred to.
I am at the moment running the macro using the F8 key as need to modify the file to automate it or call it from some other macro in my proper work book
There must be a better way

should be an xlsm file but I can only upload as xls

smiler44
Attached Files
File Type: xls add calender days & account for bank holidays.xls (56.0 KB, 24 views)
smiler44 is offline   Reply With Quote
Old 06-10-2017, 01:53 AM   #10
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,600
Thanks: 203
Thanked 221 Times in 207 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: formula for adding calaender days

In my example I am adding a number of days in C, whose number without bank holidays is calculated in D and then adding that to A to give G
I did try an upload the sheet, but got invalid file type, as mine was .xlsx, so I've saved mine as .xls and attached that.

As I mentioned a little longwinded, but it works.
I expect you could adjust to suit what you want.?
No code involved.

My Excel is only marginally better than my Access.

Quote:
Originally Posted by smiler44 View Post
No worries Gasman, I thought it was me

In your example you are adding 11 calendar days to 28.05.17 but as 29.05.17 is a bank holiday you need to add 12 and end up with 09.06.17
just as you have done.

I understand your example and it is just what I am needing to do.

Have you a method for doing this that's simple?
I spent hours last night and again this morning. I have a method but it needs to be tested some more but it's long winded, its painful it's so long.

I have my start date, I add 11 calendar days an get an end date. I then check if there is a bank holiday between the start date and end date. If there is then for each bank holiday my end date increases by one. Each time it increases I check to make sure the new date is not a bank holiday. If it is then the end date moves out my one.

I don't expect there to be more than 4 bank holidays in a row so I check only 4 times. I'm using a formula to make the checks and give me a new end date in a cell but then a macro to place the correct end date into a different cell for the rest of my formulas and macros to use.

I'll try to post my solution


smiler44
Attached Files
File Type: xls BH dates.xls (20.5 KB, 25 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 06-12-2017, 05:45 AM   #11
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 569
Thanks: 8
Thanked 7 Times in 6 Posts
smiler44 is on a distinguished road
Re: formula for adding calaender days

sorry Gasman for some reason I did not get an email to say you had replied.

A2 + C2 = 09.06.17 but there is a bank holiday on 29.05.17 so new date should be 10.06.17 not 08.06.17

I don't fully understand your formula so cant try to change it

B2-A2 = x
count number of dates in column E that are on or between A2 and A2 + x

I don't understand the second half od the formula but I think the new date should be 10.06.17 A2 plus 13 12 days plus 1 for the bank holiday

smiler44
smiler44 is offline   Reply With Quote
Old 06-12-2017, 06:19 AM   #12
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,600
Thanks: 203
Thanked 221 Times in 207 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: formula for adding calaender days

Ah, I was subtracting the hols, not adding them :-)

I've adjusted it to now add and it shows the number of holidays.
Attached Files
File Type: xls bh add dates.xls (20.5 KB, 18 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 06-12-2017, 01:29 PM   #13
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 569
Thanks: 8
Thanked 7 Times in 6 Posts
smiler44 is on a distinguished road
Re: formula for adding calaender days

very kind Gasman thank you, I'll study it tomorrow

smiler44
smiler44 is offline   Reply With Quote
Old 06-13-2017, 12:59 PM   #14
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,745
Thanks: 605
Thanked 327 Times in 299 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: formula for adding calaender days

https://www.extendoffice.com/documen...lidays.html#a2
Rx_ is offline   Reply With Quote
Old 06-14-2017, 12:01 AM   #15
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,600
Thanks: 203
Thanked 221 Times in 207 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: formula for adding calaender days

I believe that is what the o/p started with?, but did not want to exclude weekends just holidays?



__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Working Days - Need Help modifying the formula rmulder Modules & VBA 4 02-26-2013 04:18 PM
Adding formula help Tansar Excel 2 01-28-2010 07:55 AM
adding 14 week days arunprathiba Reports 3 11-16-2007 06:54 AM
Need Help With Adding Up Formula. Snooza35 Reports 1 04-20-2007 05:12 AM
Need Help With Adding Up Formula. Snooza35 Reports 1 04-20-2007 03:09 AM




All times are GMT -8. The time now is 01:04 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World