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

  • Thread starter Thread starter Khaledity
  • Start date Start date
K

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
 
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
 
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
 
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
 
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
 
This will not work because the networkdays assumes Sat and Sun as weekends
and not Fri/Sat
Click to expand...

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
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
 
Hi,

Try this:

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

Where A1 and B1 are the Start and End dates.
 
Another one:

A1 = start date
B1 = end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<6))
 
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 -
Click to expand...
 
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
 
Yet another one:

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

Just don't ask me how it works!
 
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?
 
Back
Top