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

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,

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

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

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

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

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 -

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 -

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

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 -

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

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 Biff,

Of course I can - my INT/MOD one I derived on my own - but Daniel M.
was the first one to show it (with the weekday function), I presume:
http://www.sulprobil.com/html/date_formulas.html

My MOD() part just calculates the weekday which is used to shift the
calculation to the right edge of the /7 cut, and the INT / 7 part cuts
the right weeks.

OT: If you need more details, come to London or to Berlin for a
beer :-)

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

2008-10-01 and 2009-09-30
I've tried almost all Date formats provided by MS, also General and
Number
no avail



On 22 Mar, 21:28, "T. Valko" wrote:
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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

I get 52 (with Excel 2003).
Have you double-checked what =A1 and =A2 show you in General format. Should
be 39722 and 40086 respectively.
It may also be worth copying the formula from the formula bar and pasting it
in here just in case you've got a hiccup in the formula.
--
David Biddulph

Jarek Kujawa wrote:
2008-10-01 and 2009-09-30
I've tried almost all Date formats provided by MS, also General and
Number
no avail



On 22 Mar, 21:28, "T. Valko" wrote:
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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Using Excel 2007 with regional date settings of U.S. English m/d/yyyy...

A1 = 10/1/2008
A2 = 9/30/2009

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

Returns 52 which is correct.

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
I get 52 (with Excel 2003).
Have you double-checked what =A1 and =A2 show you in General format.
Should be 39722 and 40086 respectively.
It may also be worth copying the formula from the formula bar and pasting
it in here just in case you've got a hiccup in the formula.
--
David Biddulph

Jarek Kujawa wrote:
2008-10-01 and 2009-09-30
I've tried almost all Date formats provided by MS, also General and
Number
no avail



On 22 Mar, 21:28, "T. Valko" wrote:
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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

thanks T.
I checked it on Excel 2007 and 2003, on 2 different PCs
to na avail
I think there might be some bug in WEEKDAY (or I don't know where)
function as both my versions of Excel are Polish (mistake in
translation or sth.)


On 23 Mar, 16:50, "T. Valko" wrote:
Using Excel 2007 with regional date settings of U.S. English m/d/yyyy...

A1 = 10/1/2008
A2 = 9/30/2009

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

Returns 52 which is correct.

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in m...



I get 52 (with Excel 2003).
Have you double-checked what =A1 and =A2 show you in General format..
Should be 39722 and 40086 respectively.
It may also be worth copying the formula from the formula bar and pasting
it in here just in case you've got a hiccup in the formula.
--
David Biddulph

Jarek Kujawa wrote:
2008-10-01 and 2009-09-30
I've tried almost all Date formats provided by MS, also General and
Number
no avail

On 22 Mar, 21:28, "T. Valko" wrote:
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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

thks David
both values are same as qouted by you


On 23 Mar, 10:02, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
I get 52 (with Excel 2003).
Have you double-checked what =A1 and =A2 show you in General format. Â*Should
be 39722 and 40086 respectively.
It may also be worth copying the formula from the formula bar and pasting it
in here just in case you've got a hiccup in the formula.
--
David Biddulph



Jarek Kujawa wrote:
2008-10-01 and 2009-09-30
I've tried almost all Date formats provided by MS, also General and
Number
no avail

On 22 Mar, 21:28, "T. Valko" wrote:
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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

So, I can't get an explanation unless I come to London or Berlin for a beer?

I don't know...

By the time I "get it" we'll have drank the towns dry!

--
Biff
Microsoft Excel MVP


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

Of course I can - my INT/MOD one I derived on my own - but Daniel M.
was the first one to show it (with the weekday function), I presume:
http://www.sulprobil.com/html/date_formulas.html

My MOD() part just calculates the weekday which is used to shift the
calculation to the right edge of the /7 cut, and the INT / 7 part cuts
the right weeks.

OT: If you need more details, come to London or to Berlin for a
beer :-)

Regards,
Bernd

In which case, why not try putting the dates from A1 to A2 in a range of
cells and see what the WEEKDAY function produces from each date?

Even then, it wouldn't make sense to get 2555 as you are applying SUMPRODUCT
to a range of Booleans over a range of only 365 rows, so it can't return
2555.
I suggest that you break down your formula a stage at a time to debug it.
It's a simple enough formula, so the error can't be a complicated one.
Start by looking at what =(A1&":"&A2) returns. It should be 39722:40086
You could also try =COUNT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2))))) as an array
formula, which should return 365.

I did say in another message:
"It may also be worth copying the formula from the formula bar and pasting
it in here just in case you've got a hiccup in the formula."
If you do that, it would help people to help you.
--
David Biddulph

Jarek Kujawa wrote:
thanks T.
I checked it on Excel 2007 and 2003, on 2 different PCs
to na avail
I think there might be some bug in WEEKDAY (or I don't know where)
function as both my versions of Excel are Polish (mistake in
translation or sth.)


On 23 Mar, 16:50, "T. Valko" wrote:
Using Excel 2007 with regional date settings of U.S. English
m/d/yyyy...

A1 = 10/1/2008
A2 = 9/30/2009

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

Returns 52 which is correct.

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in
m...



I get 52 (with Excel 2003).
Have you double-checked what =A1 and =A2 show you in General format.
Should be 39722 and 40086 respectively.
It may also be worth copying the formula from the formula bar and
pasting it in here just in case you've got a hiccup in the formula.
--
David Biddulph

Jarek Kujawa wrote:
2008-10-01 and 2009-09-30
I've tried almost all Date formats provided by MS, also General and
Number
no avail

On 22 Mar, 21:28, "T. Valko" wrote:
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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Pokaz cytowany tekst -

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