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

Hello,

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

Regards,
Bernd

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


"Khaledity" wrote:

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

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

If this post helps click Yes
--------------
Jacob Skaria


"Jacob Skaria" wrote:

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


"Khaledity" wrote:

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

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
-------------
Jacob Skaria


"Khaledity" wrote:

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

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

"Khaledity" wrote in message
...
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

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

"John" wrote in message
...
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

"Khaledity" wrote in message
...
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

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)



On Fri, 20 Mar 2009 18:21:27 +0530, "Ashish Mathur"
wrote:

Hi,

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

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

On 20 Mar, 10:59, Khaledity wrote:
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

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Mike H" wrote in message
...
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

On 20 Mar, 10:59, Khaledity wrote:
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

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

On 20 Mar, 12:52, "Ashish Mathur" wrote:
Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"Mike H" wrote in message

...



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

On 20 Mar, 10:59, Khaledity wrote:
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- Hide quoted text -

- Show quoted text -

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


On 20 Mar, 11:59, Khaledity wrote:
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

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,W EEKDAY(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.

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++
"Khaledity" wrote:

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

Hi,

Try this:

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

Where A1 and B1 are the Start and End dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Khaledity" wrote:

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

Another one:

A1 = start date
B1 = end date

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

--
Biff
Microsoft Excel MVP


"Khaledity" wrote in message
...
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

this one is excellent...

On 20 Mar, 18:44, "T. Valko" wrote:
Another one:

A1 = start date
B1 = end date

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

--
Biff
Microsoft Excel MVP

"Khaledity" wrote in message

...



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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

I agree! <g

--
Biff
Microsoft Excel MVP


"Jarek Kujawa" wrote in message
...
this one is excellent...

On 20 Mar, 18:44, "T. Valko" wrote:
Another one:

A1 = start date
B1 = end date

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

--
Biff
Microsoft Excel MVP

"Khaledity" wrote in message

...



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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Hello,

Now let's have a look how long our formulas take to compute. I ran
them on 5 pairs of dates, 1 day, 1 week, 1 month, 1 year and 10k days
difference and got from FASTEXCEL:
Biff''s SUMPRODUCT 10.61 ms
Mike' NETWORKDAYS 0.23 ms
Biff''s SUM 0.15 ms
Bernd's INT/MOD 0.11 ms

Regards,
Bernd

<rant

That's all fine and good.

Here's one thing that I think is important (at least it is to me), I
understand and can explain how the SUMPRODUCT formula works. If I need/want
to change it for other conditions I can do it easily.

Do you really understand these formulas:

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

I don't!

I mean, yeah sure, I know what INT, MOD, SUM and WEEKDAY do. I know what
"plus this minus that divided by this" means. But, can you explain the
*logic of why* the formulas do "plus this minus that divided by this"? I
can't! If I needed/wanted to change those other formulas for other
conditions I don't think It'd be that easy and would take some time to
experiement.

I have the SUM(INT formula in my "library" but I don't suggest it because I
can't explain how it works if someone asks.

I could respond by saying:

You subtract this from that then add this then subtract that and divide by
this but what kind of an explanation is that? That's the explanation of
someone that doesn't understand what they're talking about!

So, my challenge to you is: explain how those formulas work!

</rant
<VBG

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

Now let's have a look how long our formulas take to compute. I ran
them on 5 pairs of dates, 1 day, 1 week, 1 month, 1 year and 10k days
difference and got from FASTEXCEL:
Biff''s SUMPRODUCT 10.61 ms
Mike' NETWORKDAYS 0.23 ms
Biff''s SUM 0.15 ms
Bernd's INT/MOD 0.11 ms

Regards,
Bernd

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!

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
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

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?



On 20 Mar, 18:44, "T. Valko" wrote:
Another one:

A1 = start date
B1 = end date

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

--
Biff
Microsoft Excel MVP

"Khaledity" wrote in message

...



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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Hello Jarek,

The formula looks fine, maye you got an input date wrong - perhaps got
tricked by Excel "auto century guess" 1900 resp. 2000?

But if you want to count single weekdays there is a better non-
volatile formula:
http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd

it looks fine to me too
but despite everything is set OK with the dates in my worksheet the
result still comes wrong
thks for yr response


On 22 Mar, 12:56, Bernd P wrote:
Hello Jarek,

The formula looks fine, maye you got an input date wrong - perhaps got
tricked by Excel "auto century guess" 1900 resp. 2000?

But if you want to count single weekdays there is a better non-
volatile formula:http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd

What dates do you have in A1 and A2?

--
Biff
Microsoft Excel MVP


"Jarek Kujawa" wrote in message
...
it looks fine to me too
but despite everything is set OK with the dates in my worksheet the
result still comes wrong
thks for yr response


On 22 Mar, 12:56, Bernd P wrote:
Hello Jarek,

The formula looks fine, maye you got an input date wrong - perhaps got
tricked by Excel "auto century guess" 1900 resp. 2000?

But if you want to count single weekdays there is a better non-
volatile formula:http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd

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
-------------
Jacob Skaria


"Khaledity" wrote:

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