Counting # of days between 2 dates excluding Fri & Sat)

[Khaledity]

Hi,

I am doing a vacation tracking sheet for my subordinates, and I want
to count the number of days they have consumed by entering start date
and end date.

Any advice?

Thanks.

Khaledity

 

[Bernd P]

Hello,

I suggest to count and add the other weekdays:
http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd

 

[Jacob Skaria]

ColumnA - Start Date
ColumnB - End Date
Column C Formula '=B1-A1'

Select the date columns. format cells to Date (Format|Number Tab)
Select the number of days column. format cells to Number (Decimal places 0)

If this post helps click Yes

 

[Jacob Skaria]

Sorry, I missed you subject part (Fri & Sat)

If this post helps click Yes

 

[Jacob Skaria]

Range("A1") = startDate
Range("B1") = EndDate
Range("C1") =
(B1-WEEKDAY(B1,1)+WEEKDAY(A1,1)-A1)/7*5-MIN(5,WEEKDAY(A1,1))+MIN(5,WEEKDAY(B1,1))

If this post helps click Yes

 

[John]

Hi
I would use =NETWORKDAYS, its part of the Analysis Toolpak, goto > Tools >
Add-Ins and select Analysis Toolpak.
This function will calculate the number of working days between two dates.
It will exclude weekends and any holidays if you make a list of holidays.
=NETWORKDAYS(StartDate,EndDate,Holidays)
HTH
John

 

[Mike H]

Try this

=NETWORKDAYS(A1+1,A2+1)

Start date in A1
End date in A2

In the UK if a public holiday falls with a persons vacation period
then some companies don't deduct that day so to include hoilidays use

=NETWORKDAYS(A1+1,A2+1,Holidays+1)

Where Holidays is a named range of dates to exclude form the
calculation

This now becomes an array formula

This is an array formula which must be entered by pressing CTRL+Shift
+Enter
'and not just Enter. If you do it correctly then Excel will put curly
brackets
'around the formula {}. You can't type these yourself. If you edit the
formula
'you must enter it again with CTRL+Shift+Enter.


Mike

 

[Ashish Mathur]

Hi,

This will not work because the networkdays assumes Sat and Sun as weekends
and not Fri/Sat

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

The networkdays function assumes weekends as Sat/Sun and not Fri/Sat

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Chip Pearson]

This will not work because the networkdays assumes Sat and Sun as weekends
and not Fri/Sat

I have a formula replacement for NETWORKDAYS that allows you to
specify any days as non-working days. You are not restricted to just
two non-working days. You can specify any number of days.

http://www.cpearson.com/Excel/BetterNetworkDays.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jarek Kujawa]

cannot think of any relevant formula but maybe this function would
help

Function vacation(strt As Date, nend As Date) As Integer
Dim i As Integer
dim dadd as Integer

For i = 0 To nend - strt
If Weekday(strt + i, 2) = 5 Or Weekday(strt + i, 2) = 6 Then
dadd = dadd
Else
dadd = dadd + 1
End If
Next i

vacation = dadd

End Function

press ALT+F11 to open a VBA window, then Insert->Module and copy/paste
this code

go back to yr worksheet and enter =vacation(A1,A2)

change addresses to suit

 

[HARSHAWARDHAN. S .SHASTRI]

Dear Khaledity,

Assuming cell A1 is start date and cell B1 is end date put following
formula in any relevent cell.

=IF(OR(WEEKDAY(A1)=6,WEEKDAY(A1)=7,WEEKDAY(B1)=6,WEEKDAY(B1)=7),1+INT((B1-6)/7)+INT((B1-7)/7)-INT((A1-6)/7)-INT((A1-7)/7),1+INT((B1-6)/7)+INT((B1-7)/7)-INT((A1-6)/7)-INT((A1-7)/7))
--
HARSHAWARDHAN.S.SHASTRI

Pl do not forget to press "YES" button if post found useful.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

[Mike H]

Did you try

=NETWORKDAYS(A1+1,A2+1)

with some test dates? If not try the formula with the dates below
which are a Friday and a Saturday and I bet you get zero and if you
try again omitting the +1 you will get 1.

6/3/2009
7/3/2009

Mike

 

[Shane Devenshire]

Hi,

Try this:

=SUMPRODUCT(--(MOD(ROW(INDIRECT(A1&":"&B1))+1,7)>1))

Where A1 and B1 are the Start and End dates.

 

[T. Valko]

Another one:

A1 = start date
B1 = end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<6))

 

[Jarek Kujawa]

this one is excellent...

Another one:

A1 = start date
B1 = end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<6))

--
Biff
Microsoft Excel MVP








- Poka¿ cytowany tekst -

 

[Bernd P]

Hello,

Yet another one:
=B1-A1+1-INT((B1-MOD(B1-6,7)-A1+7)/7)-INT((B1-MOD(B1-7,7)-A1+7)/7)
Non-volatile and non-matrix.

Regards,
Bernd

 

[T. Valko]

Yet another one:

=SUM(INT((WEEKDAY(A1-{1,2,3,4,5})+B1-A1)/7))

Just don't ask me how it works!

 

[T. Valko]

I agree! <g>

--
Biff
Microsoft Excel MVP


this one is excellent...

 

[Jarek Kujawa]

Hi T.,

am trying to adjust yr formula for another poster (count the number of
Thursdays between 2 dates) and my Excel 2007 shows 2555 as a result of

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=5))

what am I doing wrong?

could you pls explain?