Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
667
Office Version
  1. 365
Platform
  1. Windows
hello,

is there a fomula to NOT incude bank hols, sat & sundays?

for example:-

(a2 ) 23/819 (b2) =a2 + 5days ( not inc sat/sun & bank holiday) = 2/9/19


many thanks in advance
KR
Trevor3007
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
thanks Joe4,

cannot get my 'fried' head around 'howto'
would you be so kind and sho me ?
 
Upvote 0
Did you look at the example in that link? It shows you exactly how to do it with a simply example. Just copy the exact same process.

Your formula would look something like:
Code:
=WORKDAY(A2,5,Z1:Z10)
where Z1:Z10 is your list of holidays (change that rnage to whatever range you have listed your holidays in).
 
Upvote 0
Did you look at the example in that link? It shows you exactly how to do it with a simply example. Just copy the exact same process.

Your formula would look something like:
Code:
=WORKDAY(A2,5,Z1:Z10)
where Z1:Z10 is your list of holidays (change that rnage to whatever range you have listed your holidays in).

hi Joe4.


I dont have a list of holidays, i was hoping that there was a magic formula that would 'just do':{

thanks anyhoo
 
Upvote 0
I dont have a list of holidays, i was hoping that there was a magic formula that would 'just do':{
No such thing exists. It actually isn't possible, if you really think about it.
Remember, not only does every country have their own holidays, different companies/entities have different holidays off!
I know where I work, we get some American holidays off, but not all. Banks, schools, and government workers all get different holidays off than I do.

So, if you want to include holidays, you need to set up a list to use. There is no way around that.
Note that the Holidays argument is an optional argument, meaning that you do not actually need to include it in your formula. If you leave it off, it will only skip weekend days.
 
Upvote 0
I maintain a list of Holidays in another WorkBook that is shared. I can import that sheet to a project, reference that Table, or use a PowerQuery code to bring the Table to my current Workbook.

WORKDAY uses inertia to determine the next appropriate day. If you want the day before the Holiday/weekend, then you want that inertia going 'back in time'
Code:
=WORKDAY(A2+6,-1,Z1:Z10)
 
Upvote 0
thanks Joe4 :{

ok...is there one that will only use workdays?
 
Upvote 0
ok...is there one that will only use workdays?
See the last line in my previous post:
Note that the Holidays argument is an optional argument, meaning that you do not actually need to include it in your formula. If you leave it off, it will only skip weekend days.
It also says as much in the link I provided in my first reply.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top