formula for adding calaender days (1 Viewer)

smiler44

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
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

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
Just search this site.
That question has been asked several times.

This is just one example

https://www.access-programmers.co.uk/forums/showthread.php?t=289016&highlight=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
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:58
Joined
Sep 21, 2011
Messages
14,047
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/forums/showthread.php?t=286444&highlight=working+days

HTH
 

smiler44

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
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

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:58
Joined
Sep 21, 2011
Messages
14,047
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?
 

smiler44

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
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

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
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
 

Attachments

  • add calender days & account for bank holidays.xls
    56 KB · Views: 111

Gasman

Enthusiastic Amateur
Local time
Today, 19:58
Joined
Sep 21, 2011
Messages
14,047
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. ;)

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
 

Attachments

  • BH dates.xls
    20.5 KB · Views: 116

smiler44

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:58
Joined
Sep 21, 2011
Messages
14,047
Ah, I was subtracting the hols, not adding them :)

I've adjusted it to now add and it shows the number of holidays.
 

Attachments

  • bh add dates.xls
    20.5 KB · Views: 115

smiler44

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
very kind Gasman thank you, I'll study it tomorrow

smiler44
 

smiler44

Registered User.
Local time
Today, 19:58
Joined
Jul 15, 2008
Messages
641
Gasman, thank you for the formula, its been of great help. I have used it during the week and I can now add it into my spread sheet to use properly.

thanks again

smiler44
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:58
Joined
Sep 21, 2011
Messages
14,047
Give it a good test, as I only gave it a quick test.
 

Users who are viewing this thread

Top Bottom