yisfromer

New Member
Joined
Nov 14, 2011
Messages
21
Hi,
I'm trying to get the sum of workdays between 2 given dates (A1 is the start date and B1 is the end date) with Saturday as the only non-workday (I'd like to exclude Saturday only)
I used Networkdays.intl in excel 2010 for this, but I have excel 2007 on my computer. How would I do it in Excel 2007?
Thank you!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

Try

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))<>7))
 

yisfromer

New Member
Joined
Nov 14, 2011
Messages
21
Thank you, however, the function you gave me gives me the total difference of days and doesn't exclude saturdays at all.
example:
A1=18/11/2011
B1=22/11/2011
C1 (your function)=4
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
There are 4 NON Saturdays between Nov18 and Nov22
Fri Nov 18
Sun Nov 20
Mon Nov 21
Tue Nov 22

I suppose the issue here is the definition of the word "Between"

Do you want to NOT include the beginning and/or ending dates?
 

yisfromer

New Member
Joined
Nov 14, 2011
Messages
21

ADVERTISEMENT

Sorry, I realize now it is including both the beginning and end date.
Yes, I would like to exclude the beginning date and saturday in any count
so that in Nov 18 to Nov 22 it will count only nov 20, 21 and 22.
Thank you very much!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
To exclude the beginning date, try
=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A1+1):INDEX(A:A,B1)))<>7))

FYI, if you wanted to exclude the END date
=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1-1)))<>7))

And logically, if you wanted to exclude both..

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A1+1):INDEX(A:A,B1-1)))<>7))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
This formula will also count non-Saturdays without the start date

=B1-A1-INT((B1-A1-WEEKDAY(B1-6))/7)-1

format as general
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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
Top