date +5

[Trevor3007]

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

 

[Joe4]

Use the WORKDAY function.
See: https://exceljet.net/excel-functions/excel-workday-function

 

[Trevor3007]

thanks Joe4,

cannot get my 'fried' head around 'howto'
would you be so kind and sho me ?

 

[Joe4]

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:

=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).

 

[Trevor3007]

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:

=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

 

[Joe4]

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.

 

[SpillerBD]

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'

=WORKDAY(A2+6,-1,Z1:Z10)

 

[Trevor3007]

thanks Joe4 :{

ok...is there one that will only use workdays?

 

[Joe4]

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.

 

[Trevor3007]

See the last line in my previous post:

It also says as much in the link I provided in my first reply.

many thanks & hope you have a good day.

KR
Trevor3007