Exclude Saturday and Sunday in Excel

Hello,

I’m not sure if this can be applied in a formula, as it requires analysis work on the dates. However, in VBA, it's possible to develop code that can calculate this and return the result either as a message or write it into a formula.

The code in question can be called using a button.

The principle of the function could be:

Initialize a day counter to 0 and use

A loop that for each day between the start date and the end date
1) creates a date object from the read date
2) extracts the corresponding day of the week from the date
3) checks if this day is Saturday or Sunday
4) if not, adds 1 to the counter
5) returns the counter

This may not exactly answer your problem, but it could be a lead. I love VBA, it allows for a lot of automation in Excel, Word, Access.... :-)

Best regards,

IC

Hello Karlum and other participants,

The formula excludes Saturday and Sunday

taking my example:
18/9 Thursday thus 1
19/9 Friday thus 2
20/9 21/9 Saturday and Sunday not counted thus 2
22/9 Monday thus 3

For Jean_Jacques: to calculate the number of working days (only Sunday removed) you need to nest WEEKDAY and NETWORKDAYS (a complicated formula!), hence my remark as I thought... so, forgive me for my misinterpretation

Have a nice Sunday,

Edit: 9:50 for fun, "working days" formula from the must-visit site of Jacques Boisgontier (to bookmark!!!):
http://boisgontierjacques.free.fr/

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<7)*(COUNTIF(holidays,ROW(INDIRECT(A2&":"&B2)))=0))


--
Michel

First of all, thank you Incaout for your quick response.

I don't know what VBA is?

However, I find the idea very interesting indeed.
It's true that if I can increment a counter by 1 for all days except Saturday and Sunday, that would solve the problem.

I will delve into that right away.

Thanks again.

Re,

I think that VBA stands for Visual Basic for Applications.

I will study that.

Thanks again.

VBA is Visual Basic for Applications. In fact, it is an object-oriented programming language that allows you to manipulate elements in Excel, such as sheets, cells, etc., to perform calculations, formatting, and to respond to user actions.

It is indeed an interesting solution for developing small functions with tight deadlines. Obviously, the downside is that you have to dive into it.

If you're interested in taking a look, you can access it via the "Tools" menu -> "Macro" -> Visual Basic

Otherwise, in the insert menu -> functions, there is a function in the dates and times category called WEEKDAY that allows you to find out the day of the week for a date. I use Excel 2000, I hope it has the same name in version 2003. But I'm not sure that a function can perform all the control.

Good luck.

IC

Hi

That's great. I didn't know that. Thanks for the tip ;-)

IC

Good evening,

In my opinion, we need a formula that combines WEEKDAY and NETWORKDAYS to calculate the number of working days ....

But formulas are not my strong suit.

I'm throwing out the idea, but I don't know how to do it

Sincerely

Science only discovers what has always existed.
REEVES Hubert.

Good evening Jean Jacques

Wouldn't you confuse working days and business days?

Best regards
--
Michel

Thank you all, guys, because the business days formula is very handy.

However, I believe it does not eliminate Saturday and Sunday but only one day.
I will work on that.

Thanks again because I think I will be able to mark this question as resolved.
I will wait a bit just in case.

Thank you and have a good weekend.

Re-hello everyone,

I think the issue is resolved and I thank you.

For your information, in order to complete my study, I've posted a new question that is a bit different, that's why I didn't include it here.

If you want, you can take a look, it's the following topic:
Color modification on a map based on time delay in Excel

http://www.commentcamarche.net/forum/affich 8532885 color modification on a map based on d

Thanks again and have a great week.