Regarding WEEKDAY(), I read that problems can occur if dates are entered as text.

Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a wrong result (7). This is because general format is same as text.

If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5).

Okay, so far. What I don't understand is the following.

I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I also get the correct result (5).

The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008 *directly* to the formula. It amazes me that referencing A1 in WEEKDAY() gives me the correct answer whereas keying it in as part of the formula won't work.

Comments welcome.

Epinn

But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559,
which it presents/formats as that date.

When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

If you want to enter the date into the WEEKDAY function, you have to force
it into a number, either using another function such as you did with DATE,
or coerce it directly, like

=WEEKDAY(--"21/4/2008")

or my preferred format of

=WEEKDAY(--"2008-04-21")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Regarding WEEKDAY(), I read that problems can occur if dates are entered as
text.

Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a wrong
result (7). This is because general format is same as text.

If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5).

Okay, so far. What I don't understand is the following.

I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I also
get the correct result (5).

The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008
*directly* to the formula. It amazes me that referencing A1 in WEEKDAY()
gives me the correct answer whereas keying it in as part of the formula
won't work.

Comments welcome.

Epinn

Hi Bob,

Can you also explain another one along similar lines.
If I put todays date 09/09/2006 in A1.
Then in A2 I put =MONTH(A1) and I get 9 which is good.
I then format A2 as mmmm and I get January. What gives??

Confused
Martin

Again it is because the underlying values of dates are just numbers. As I
said to Epinn, the value stored in a date of 21/4/2008 is 39559.

Now when you do a =MONTH(A1) in A2, you are not really returning a date, but
a simple month number (9 in the case quoted). But, because it is a number,
if you do date type things on it, Excel will not complain, it will just work
on whatever date that number resolves to. As a date is stored as the number
of days since 1st January 1900, the value of 9 will be treated as 9th Jan
1900, so if you format it as mmmm, you get January. Format it as dd/mm/yyyy,
and see what I mean.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MartinW" wrote in message
...
Hi Bob,

Can you also explain another one along similar lines.
If I put todays date 09/09/2006 in A1.
Then in A2 I put =MONTH(A1) and I get 9 which is good.
I then format A2 as mmmm and I get January. What gives??

Confused
Martin

Bob,

Glad I still caught you this late. Thank you for enlightening me.

<<21/4/2008 is not a number here, so it errors.

If it gives me an error, then I know. The problem is it returns "7" instead of "5" in my other example. That's very misleading and dangerous.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

So, I understand why you prefer =WEEKDAY(--"2008-04-21").

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

My preference will be =WEEKDAY(--"2008/4/21").

I assume "--" above is the same as "--" in SUMPRODUCT(). Please confirm. Can't find double negating in Help.

I find date functions may be as confusing as SUMPRODUCT().

Will see.

Epinn

"Bob Phillips" wrote in message ...
But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559,
which it presents/formats as that date.

When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

If you want to enter the date into the WEEKDAY function, you have to force
it into a number, either using another function such as you did with DATE,
or coerce it directly, like

=WEEKDAY(--"21/4/2008")

or my preferred format of

=WEEKDAY(--"2008-04-21")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Regarding WEEKDAY(), I read that problems can occur if dates are entered as
text.

Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a wrong
result (7). This is because general format is same as text.

If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5).

Okay, so far. What I don't understand is the following.

I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I also
get the correct result (5).

The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008
*directly* to the formula. It amazes me that referencing A1 in WEEKDAY()
gives me the correct answer whereas keying it in as part of the formula
won't work.

Comments welcome.

Epinn

Thanks Bob, of course it is I should have seen that...duh!
So to get a return of September I can see a way of doing it by
hiding the month cell and a Vlookup table somewhere out of the
way and using a Vlookup in A2. Is there a simpler method?

Thanks again
Martin

Because weekday(21/4/2008) does give an error
as it treats 21/4/2008 as 21/4 divided by 2008 - nearly 0
ie 0/1/1900 in date terms
Odd but thats what's happening

Steve




On Sat, 09 Sep 2006 01:52:22 +0100, Epinn
wrote:

Bob,

Glad I still caught you this late. Thank you for enlightening me.

<<21/4/2008 is not a number here, so it errors.

If it gives me an error, then I know. The problem is it returns "7"
instead of "5" in my other example. That's very misleading and
dangerous.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

So, I understand why you prefer =WEEKDAY(--"2008-04-21").

But I prefer "/" to "-" and I tested it. I am glad that "/" works too..

My preference will be =WEEKDAY(--"2008/4/21").

I assume "--" above is the same as "--" in SUMPRODUCT(). Please
confirm. Can't find double negating in Help.

I find date functions may be as confusing as SUMPRODUCT().

Will see.

Epinn

"Bob Phillips" wrote in message
...
But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559,
which it presents/formats as that date.

When you enter it into a function, the function treats it as its
argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

If you want to enter the date into the WEEKDAY function, you have to
force
it into a number, either using another function such as you did with
DATE,
or coerce it directly, like

=WEEKDAY(--"21/4/2008")

or my preferred format of

=WEEKDAY(--"2008-04-21")




--
Steve (3)

When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

Actually, the formula does not "error" but calculates properly.

The argument: 2/14/2008 is the equivalent of:

2 divided by 14 divided by 2008

which equals: 0.0000711439954467843

Since WEEKDAY only works with integers Excel truncates the value to 0.

Weekday( 0 ) is actually 12/31/1899 which is a Saturday or weekday 7 when
the return_type used is 1 or omitted.

This is another Excel nuance. The date serial system doesn't start until day
1 which is 1/1/1900 yet you can calculate a date to be 1/0/1900. Excel
treats the 0th day of the month as the last day of the previous month.
That's how we end up with 7 as the result of the Weekday function.

Biff

"Bob Phillips" wrote in message
...
But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559,
which it presents/formats as that date.

When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

If you want to enter the date into the WEEKDAY function, you have to force
it into a number, either using another function such as you did with DATE,
or coerce it directly, like

=WEEKDAY(--"21/4/2008")

or my preferred format of

=WEEKDAY(--"2008-04-21")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Regarding WEEKDAY(), I read that problems can occur if dates are entered
as
text.

Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a
wrong
result (7). This is because general format is same as text.

If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5).

Okay, so far. What I don't understand is the following.

I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I
also
get the correct result (5).

The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008
*directly* to the formula. It amazes me that referencing A1 in WEEKDAY()
gives me the correct answer whereas keying it in as part of the formula
won't work.

Comments welcome.

Epinn

the N and the T on my keyboard are sicky sorry sTicky
So I meant to write "weekday(21/4/2008) doesN'T give an error"

Steve

On Sat, 09 Sep 2006 02:15:26 +0100, SteveW
wrote:

Because weekday(21/4/2008) does give an error
as it treats 21/4/2008 as 21/4 divided by 2008 - nearly 0
ie 0/1/1900 in date terms
Odd but thats what's happening

Steve




On Sat, 09 Sep 2006 01:52:22 +0100, Epinn
wrote:

Bob,

Glad I still caught you this late. Thank you for enlightening me.

<<21/4/2008 is not a number here, so it errors.

If it gives me an error, then I know. The problem is it returns "7"
instead of "5" in my other example. That's very misleading and
dangerous.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

So, I understand why you prefer =WEEKDAY(--"2008-04-21").

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

My preference will be =WEEKDAY(--"2008/4/21").

I assume "--" above is the same as "--" in SUMPRODUCT(). Please
confirm. Can't find double negating in Help.

I find date functions may be as confusing as SUMPRODUCT().

Will see.

Epinn

"Bob Phillips" wrote in message
...
But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559,
which it presents/formats as that date.

When you enter it into a function, the function treats it as its
argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

If you want to enter the date into the WEEKDAY function, you have to
force
it into a number, either using another function such as you did with
DATE,
or coerce it directly, like

=WEEKDAY(--"21/4/2008")

or my preferred format of

=WEEKDAY(--"2008-04-21")







--
Steve (3)

In A2 enter:
=A1
*Then* format A2 to mmmm!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MartinW" wrote in message
...
Thanks Bob, of course it is I should have seen that...duh!
So to get a return of September I can see a way of doing it by
hiding the month cell and a Vlookup table somewhere out of the
way and using a Vlookup in A2. Is there a simpler method?

Thanks again
Martin

Probably the reason 21/4/2008 doesn't work for you is because your regional
settings aren't set-up as d/m/y, BUT as m/d/y.

It *would* work on Bob's machine because he probably has the European short
date set-up in his regional settings.

And of course, 2008/04/21 should work *everywhere*, since it's recognized as
international.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" wrote in message
...
Bob,

Glad I still caught you this late. Thank you for enlightening me.

<<21/4/2008 is not a number here, so it errors.

If it gives me an error, then I know. The problem is it returns "7" instead
of "5" in my other example. That's very misleading and dangerous.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

So, I understand why you prefer =WEEKDAY(--"2008-04-21").

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

My preference will be =WEEKDAY(--"2008/4/21").

I assume "--" above is the same as "--" in SUMPRODUCT(). Please confirm.
Can't find double negating in Help.

I find date functions may be as confusing as SUMPRODUCT().

Will see.

Epinn

"Bob Phillips" wrote in message
...
But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559,
which it presents/formats as that date.

When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

If you want to enter the date into the WEEKDAY function, you have to force
it into a number, either using another function such as you did with DATE,
or coerce it directly, like

=WEEKDAY(--"21/4/2008")

or my preferred format of

=WEEKDAY(--"2008-04-21")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Regarding WEEKDAY(), I read that problems can occur if dates are entered as
text.

Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a wrong
result (7). This is because general format is same as text.

If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5).

Okay, so far. What I don't understand is the following.

I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I also
get the correct result (5).

The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008
*directly* to the formula. It amazes me that referencing A1 in WEEKDAY()
gives me the correct answer whereas keying it in as part of the formula
won't work.

Comments welcome.

Epinn

Yeah tried that RD but I'm cocatenating the result and that
returns a value of 38969 instead of september for the cell
even though it displays as september. The Vlookup works
fine so I might just stick with that.

Thanks for postig
Martin

Try this to concatenate:

C1 contains the text,
20 days in

SO ...

=C1&" "&TEXT(A2,"mmmm")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MartinW" wrote in message
...
Yeah tried that RD but I'm cocatenating the result and that
returns a value of 38969 instead of september for the cell
even though it displays as september. The Vlookup works
fine so I might just stick with that.

Thanks for postig
Martin

Hi Epinn,

The "--" is exactly the same as its usage in SUMPRODUCT, it is coercing into
a numeric.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

As RD says the second version of that works for you, but fails for me is
because we have different date settings, yours are American, mine are
European.

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

The purpose of using the date in the format yyyy-mm-dd or yyyy/mm/dd is to
remove ambiguity (10/09/2006 is 9th Oct to you, it's 10th Sep to me). I
prefer the use of the "-" separator because that is part of the ISO
standard.

I find date functions may be as confusing as SUMPRODUCT().

No, you are just trying to understand properly so that you can use more
effectively. Nothing wrong with that. I answered a post on another forum
where a guy gave totally misleading information about SUMPRODUCT as if it
were gospel. It didn't affect the solution, which worked, but should the OP
have tried to take it further, he would really have gotten confused. I don't
see that happening to you.

And if you think dates are confusing, just pity us developers who work in
both markets, catering for all forms can be really challenging.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thank you all. This has been very educational. I have a few comments.

Probably the reason 21/4/2008 doesn't work for you is because your regional
settings aren't set-up as d/m/y, BUT as m/d/y.

Good point, RD.

I'm cocatenating the result and that returns a value of 38969 instead of September.

If I key 9/9/2006 into A1, what formula do I use in A2 to get 38969. Please advise.

=C1&" "&TEXT(A2,"mmmm")

If I key 9/9/2006 into A1, I think I can reference A1 directly in the above formula, instead of entering =A1 in A2 and then using A2 in the above formula.

The argument: 2/14/2008 is the equivalent of: 2 divided by 14 divided by 2008 which equals: 0.0000711439954467843........Weekday( 0 ) is actually 12/31/1899 which is a Saturday or weekday 7 when the return_type used is 1 or omitted.

Don't think I want to agree with this. I key in =WEEKDAY(0) into a cell, I get 7-Jan-00. Don't know why and not sure if we are talking about the same thing.

=MONTH(0) yields 1-Jan-1900. In both cases, we never got back to 1899.

=WEEKDAY(--"1899/12/31") gives an error #VALUE! which makes sense as the date system starts at 1/1/1900.

Talking about 1899 makes me feel very old ;) but all this is very interesting. Please keep the date talk going.

Next I have to analyze the formula for "last workday of the current month." This formual is three-line long and uses EOMONTH(). Looks tough. If it gets too confusing, I'll just use it without understanding it. There is a shorter formula but uses "holidays" as part of the syntax. (Holidays is not a function.)

Epinn

"Bob Phillips" wrote in message ...
Hi Epinn,

The "--" is exactly the same as its usage in SUMPRODUCT, it is coercing into
a numeric.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

As RD says the second version of that works for you, but fails for me is
because we have different date settings, yours are American, mine are
European.

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

The purpose of using the date in the format yyyy-mm-dd or yyyy/mm/dd is to
remove ambiguity (10/09/2006 is 9th Oct to you, it's 10th Sep to me). I
prefer the use of the "-" separator because that is part of the ISO
standard.

I find date functions may be as confusing as SUMPRODUCT().

No, you are just trying to understand properly so that you can use more
effectively. Nothing wrong with that. I answered a post on another forum
where a guy gave totally misleading information about SUMPRODUCT as if it
were gospel. It didn't affect the solution, which worked, but should the OP
have tried to take it further, he would really have gotten confused. I don't
see that happening to you.

And if you think dates are confusing, just pity us developers who work in
both markets, catering for all forms can be really challenging.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

=weekday(d/m/y) or (m/d/y or whatever)
it treats it like a number on mine exactly the same results
as if I type =d/m/y ... in a cell and have =weekday(cell)

They all act as (a/b)/c for me
Now if they were in ""'s that might be different

Steve


On Sat, 09 Sep 2006 11:43:01 +0100, Epinn
wrote:

Thank you all. This has been very educational. I have a few comments..

Probably the reason 21/4/2008 doesn't work for you is because your
regional
settings aren't set-up as d/m/y, BUT as m/d/y.

Good point, RD.

I'm cocatenating the result and that returns a value of 38969 instead
of September.

If I key 9/9/2006 into A1, what formula do I use in A2 to get 38969.
Please advise.

=C1&" "&TEXT(A2,"mmmm")

If I key 9/9/2006 into A1, I think I can reference A1 directly in the
above formula, instead of entering =A1 in A2 and then using A2 in the
above formula.

The argument: 2/14/2008 is the equivalent of: 2 divided by 14 divided
by 2008 which equals: 0.0000711439954467843........Weekday( 0 ) is
actually 12/31/1899 which is a Saturday or weekday 7 when the
return_type used is 1 or omitted.

Don't think I want to agree with this. I key in =WEEKDAY(0) into a
cell, I get 7-Jan-00. Don't know why and not sure if we are talking
about the same thing.

=MONTH(0) yields 1-Jan-1900. In both cases, we never got back to 1899.

=WEEKDAY(--"1899/12/31") gives an error #VALUE! which makes sense as
the date system starts at 1/1/1900.

Talking about 1899 makes me feel very old ;) but all this is very
interesting. Please keep the date talk going.

Next I have to analyze the formula for "last workday of the current
month." This formual is three-line long and uses EOMONTH(). Looks
tough. If it gets too confusing, I'll just use it without understanding
it. There is a shorter formula but uses "holidays" as part of the
syntax. (Holidays is not a function.)

Epinn

"Bob Phillips" wrote in message
...
Hi Epinn,

The "--" is exactly the same as its usage in SUMPRODUCT, it is coercing
into
a numeric.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

As RD says the second version of that works for you, but fails for me is
because we have different date settings, yours are American, mine are
European.

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

The purpose of using the date in the format yyyy-mm-dd or yyyy/mm/dd is
to
remove ambiguity (10/09/2006 is 9th Oct to you, it's 10th Sep to me). I
prefer the use of the "-" separator because that is part of the ISO
standard.

I find date functions may be as confusing as SUMPRODUCT().

No, you are just trying to understand properly so that you can use more
effectively. Nothing wrong with that. I answered a post on another forum
where a guy gave totally misleading information about SUMPRODUCT as if it
were gospel. It didn't affect the solution, which worked, but should the
OP
have tried to take it further, he would really have gotten confused. I
don't
see that happening to you.

And if you think dates are confusing, just pity us developers who work in
both markets, catering for all forms can be really challenging.

Steve,

I heard you. But we were discussing WEEKDAY() *with coercing* i.e. including double negating in the formulae. Do I say it right, Bob? Try to include double negating in both of your formulae and see if you get an error for either one. Like Bob and RD said, the error depends on your date default system for your region.

Epinn

"SteveW" wrote in message news:op.tflxowdeevjsnp@enigma03...
=weekday(d/m/y) or (m/d/y or whatever)
it treats it like a number on mine exactly the same results
as if I type =d/m/y ... in a cell and have =weekday(cell)

They all act as (a/b)/c for me
Now if they were in ""'s that might be different

Steve


On Sat, 09 Sep 2006 11:43:01 +0100, Epinn
wrote:

Thank you all. This has been very educational. I have a few comments.

Probably the reason 21/4/2008 doesn't work for you is because your
regional
settings aren't set-up as d/m/y, BUT as m/d/y.

Good point, RD.

I'm cocatenating the result and that returns a value of 38969 instead
of September.

If I key 9/9/2006 into A1, what formula do I use in A2 to get 38969.
Please advise.

=C1&" "&TEXT(A2,"mmmm")

If I key 9/9/2006 into A1, I think I can reference A1 directly in the
above formula, instead of entering =A1 in A2 and then using A2 in the
above formula.

The argument: 2/14/2008 is the equivalent of: 2 divided by 14 divided
by 2008 which equals: 0.0000711439954467843........Weekday( 0 ) is
actually 12/31/1899 which is a Saturday or weekday 7 when the
return_type used is 1 or omitted.

Don't think I want to agree with this. I key in =WEEKDAY(0) into a
cell, I get 7-Jan-00. Don't know why and not sure if we are talking
about the same thing.

=MONTH(0) yields 1-Jan-1900. In both cases, we never got back to 1899.

=WEEKDAY(--"1899/12/31") gives an error #VALUE! which makes sense as
the date system starts at 1/1/1900.

Talking about 1899 makes me feel very old ;) but all this is very
interesting. Please keep the date talk going.

Next I have to analyze the formula for "last workday of the current
month." This formual is three-line long and uses EOMONTH(). Looks
tough. If it gets too confusing, I'll just use it without understanding
it. There is a shorter formula but uses "holidays" as part of the
syntax. (Holidays is not a function.)

Epinn

"Bob Phillips" wrote in message
...
Hi Epinn,

The "--" is exactly the same as its usage in SUMPRODUCT, it is coercing
into
a numeric.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

As RD says the second version of that works for you, but fails for me is
because we have different date settings, yours are American, mine are
European.

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

The purpose of using the date in the format yyyy-mm-dd or yyyy/mm/dd is
to
remove ambiguity (10/09/2006 is 9th Oct to you, it's 10th Sep to me). I
prefer the use of the "-" separator because that is part of the ISO
standard.

I find date functions may be as confusing as SUMPRODUCT().

No, you are just trying to understand properly so that you can use more
effectively. Nothing wrong with that. I answered a post on another forum
where a guy gave totally misleading information about SUMPRODUCT as if it
were gospel. It didn't affect the solution, which worked, but should the
OP
have tried to take it further, he would really have gotten confused. I
don't
see that happening to you.

And if you think dates are confusing, just pity us developers who work in
both markets, catering for all forms can be really challenging.

"Epinn" wrote in message
...
Thank you all. This has been very educational. I have a few comments.

If I key 9/9/2006 into A1, what formula do I use in A2 to get 38969.
Please advise.

=A1 and format as General. As we said the underlying value of a date is just
the number of days since 1st Jan 1900, so it is already that number. You
just format it to see it.


If I key 9/9/2006 into A1, I think I can reference A1 directly in the
above formula,
instead of entering =A1 in A2 and then using A2 in the above formula.

Correct

The argument: 2/14/2008 is the equivalent of: 2 divided by 14 divided
by 2008
which equals: 0.0000711439954467843........Weekday( 0 ) is actually
12/31/1899
which is a Saturday or weekday 7 when the return_type used is 1 or
omitted.

Don't think I want to agree with this. I key in =WEEKDAY(0) into a cell,
I get 7-Jan-00.
Don't know why and not sure if we are talking about the same thing.

You may not want to, but Biff is right. 1st Jan 1900 was a Sunday, the 31st
Dec 1899 was a saturday, which is day 7 to WEEKDAY. You get 7-Jan-00 because
it is formatted that way, the underlying value is 7.

=MONTH(0) yields 1-Jan-1900. In both cases, we never got back to 1899.

No, it yields 1. You just have it formatted as a date. A month number is not
a date, it is the ordinal value of the month within the year.

=WEEKDAY(--"1899/12/31") gives an error #VALUE! which makes sense as
the date system starts at 1/1/1900.

It does, as Excel "knows" that is not a date in its view of the world, but
you can fool it

=WEEKDAY(--"1900/01/01"-1)

returns 7.

Again as Biff, this is another nuance of Excel.

Talking about 1899 makes me feel very old ;) but all this is very
interesting.
Please keep the date talk going.

It would be nice if Norman Harker joined the discussion. He has made the
study of dates a speciality.

Next I have to analyze the formula for "last workday of the current
month."
This formual is three-line long and uses EOMONTH(). Looks tough.
If it gets too confusing, I'll just use it without understanding it.
There is a shorter formula but uses "holidays" as part of the syntax.
(Holidays is not a function.)

How about

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),-1)

or without the ATP function

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)
-5))

Epinn

"Bob Phillips" wrote in message
...

Yes, without quotes nor double negating, I don't get an error either for both formats.

Epinn

"SteveW" wrote in message news:op.tflxowdeevjsnp@enigma03...
=weekday(d/m/y) or (m/d/y or whatever)
it treats it like a number on mine exactly the same results
as if I type =d/m/y ... in a cell and have =weekday(cell)

They all act as (a/b)/c for me
Now if they were in ""'s that might be different

Steve


On Sat, 09 Sep 2006 11:43:01 +0100, Epinn
wrote:

Thank you all. This has been very educational. I have a few comments.

Probably the reason 21/4/2008 doesn't work for you is because your
regional
settings aren't set-up as d/m/y, BUT as m/d/y.

Good point, RD.

I'm cocatenating the result and that returns a value of 38969 instead
of September.

If I key 9/9/2006 into A1, what formula do I use in A2 to get 38969.
Please advise.

=C1&" "&TEXT(A2,"mmmm")

If I key 9/9/2006 into A1, I think I can reference A1 directly in the
above formula, instead of entering =A1 in A2 and then using A2 in the
above formula.

The argument: 2/14/2008 is the equivalent of: 2 divided by 14 divided
by 2008 which equals: 0.0000711439954467843........Weekday( 0 ) is
actually 12/31/1899 which is a Saturday or weekday 7 when the
return_type used is 1 or omitted.

Don't think I want to agree with this. I key in =WEEKDAY(0) into a
cell, I get 7-Jan-00. Don't know why and not sure if we are talking
about the same thing.

=MONTH(0) yields 1-Jan-1900. In both cases, we never got back to 1899.

=WEEKDAY(--"1899/12/31") gives an error #VALUE! which makes sense as
the date system starts at 1/1/1900.

Talking about 1899 makes me feel very old ;) but all this is very
interesting. Please keep the date talk going.

Next I have to analyze the formula for "last workday of the current
month." This formual is three-line long and uses EOMONTH(). Looks
tough. If it gets too confusing, I'll just use it without understanding
it. There is a shorter formula but uses "holidays" as part of the
syntax. (Holidays is not a function.)

Epinn

"Bob Phillips" wrote in message
...
Hi Epinn,

The "--" is exactly the same as its usage in SUMPRODUCT, it is coercing
into
a numeric.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

As RD says the second version of that works for you, but fails for me is
because we have different date settings, yours are American, mine are
European.

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

The purpose of using the date in the format yyyy-mm-dd or yyyy/mm/dd is
to
remove ambiguity (10/09/2006 is 9th Oct to you, it's 10th Sep to me). I
prefer the use of the "-" separator because that is part of the ISO
standard.

I find date functions may be as confusing as SUMPRODUCT().

No, you are just trying to understand properly so that you can use more
effectively. Nothing wrong with that. I answered a post on another forum
where a guy gave totally misleading information about SUMPRODUCT as if it
were gospel. It didn't affect the solution, which worked, but should the
OP
have tried to take it further, he would really have gotten confused. I
don't
see that happening to you.

And if you think dates are confusing, just pity us developers who work in
both markets, catering for all forms can be really challenging.

that -- bit was so far down I'd forgotten :)

Steve



On Sat, 09 Sep 2006 12:02:57 +0100, Epinn
wrote:

Steve,

I heard you. But we were discussing WEEKDAY() *with coercing* i.e.
including double negating in the formulae. Do I say it right, Bob?
Try to include double negating in both of your formulae and see if you
get an error for either one. Like Bob and RD said, the error depends on
your date default system for your region.

Epinn

"SteveW" wrote in message
news:op.tflxowdeevjsnp@enigma03...
=weekday(d/m/y) or (m/d/y or whatever)
it treats it like a number on mine exactly the same results
as if I type =d/m/y ... in a cell and have =weekday(cell)

They all act as (a/b)/c for me
Now if they were in ""'s that might be different

Steve


On Sat, 09 Sep 2006 11:43:01 +0100, Epinn
wrote:

Thank you all. This has been very educational. I have a few comments.

Probably the reason 21/4/2008 doesn't work for you is because your
regional
settings aren't set-up as d/m/y, BUT as m/d/y.

Good point, RD.

I'm cocatenating the result and that returns a value of 38969 instead
of September.

If I key 9/9/2006 into A1, what formula do I use in A2 to get 38969.
Please advise.

=C1&" "&TEXT(A2,"mmmm")

If I key 9/9/2006 into A1, I think I can reference A1 directly in the
above formula, instead of entering =A1 in A2 and then using A2 in the
above formula.

The argument: 2/14/2008 is the equivalent of: 2 divided by 14 divided
by 2008 which equals: 0.0000711439954467843........Weekday( 0 ) is
actually 12/31/1899 which is a Saturday or weekday 7 when the
return_type used is 1 or omitted.

Don't think I want to agree with this. I key in =WEEKDAY(0) into a
cell, I get 7-Jan-00. Don't know why and not sure if we are talking
about the same thing.

=MONTH(0) yields 1-Jan-1900. In both cases, we never got back to 1899.

=WEEKDAY(--"1899/12/31") gives an error #VALUE! which makes sense as
the date system starts at 1/1/1900.

Talking about 1899 makes me feel very old ;) but all this is very
interesting. Please keep the date talk going.

Next I have to analyze the formula for "last workday of the current
month." This formual is three-line long and uses EOMONTH(). Looks
tough. If it gets too confusing, I'll just use it without understanding
it. There is a shorter formula but uses "holidays" as part of the
syntax. (Holidays is not a function.)

Epinn

"Bob Phillips" wrote in message
...
Hi Epinn,

The "--" is exactly the same as its usage in SUMPRODUCT, it is coercing
into
a numeric.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

As RD says the second version of that works for you, but fails for me is
because we have different date settings, yours are American, mine are
European.

But I prefer "/" to "-" and I tested it. I am glad that "/" works
too.

The purpose of using the date in the format yyyy-mm-dd or yyyy/mm/dd is
to
remove ambiguity (10/09/2006 is 9th Oct to you, it's 10th Sep to me). I
prefer the use of the "-" separator because that is part of the ISO
standard.

I find date functions may be as confusing as SUMPRODUCT().

No, you are just trying to understand properly so that you can use more
effectively. Nothing wrong with that. I answered a post on another forum
where a guy gave totally misleading information about SUMPRODUCT as if
it
were gospel. It didn't affect the solution, which worked, but should the
OP
have tried to take it further, he would really have gotten confused. I
don't
see that happening to you.

And if you think dates are confusing, just pity us developers who work
in
both markets, catering for all forms can be really challenging.

Bob,

=A1 and format as General. As we said......

Thank you for being patient and putting up with me. I am very unfamiliar with dates, just starting.
I did hear you - "......underlying value of a date is just the number of days since 1st Jan 1900...... when you key 21/4/2008 into cell A1, Excel recognises it as a date and converts it to an underlying value of 39559......" I registered the small pieces of info and I thought I understood. But when I tried to piece them together ...... Here's what has happened.

I had a blank cell and I thought I saw the format as "general." Then I keyed in a date like 21/4/2008 into the cell and I saw the exact same thing as I had keyed in. ***I was under the impression that "general" was still the format***. I didn't recheck the format at that point. When I did, I noticed that it was changed from "general" to "date." This is why I don't know that "general" format can display the actual no. of days. So, this is what I have missed and you can tell how green I am.

You may not want to, but Biff is right. 1st Jan 1900 was a Sunday, the 31st
Dec 1899 was a saturday, which is day 7 to WEEKDAY. You get 7-Jan-00 because
it is formatted that way, the underlying value is 7.

When I read Biff's writeup, I found it very logical because I did agree that 1/1/1900 was a Sunday. But when I keyed in =WEEKDAY(0) ......

=WEEKDAY(--"1900/01/01"-1) returns 7.

Ah, this is what I have missed from Biff's writeup and what I didn't do. Now, I am totally convinced. Bob, you are such a good teacher and thank you for clarifying. Now, I can explain =WEEKDAY(0) to other people in **absolute details**. ;)

Epinn wrote: =MONTH(0) yields 1-Jan-1900......

Bob wrote: No, it yields 1. You just have it formatted as a date.

Sorry, I skipped one step. I like programmers and/or accountants because they talk my language - exact and precise. Okay, I slipped occasionally. ;)

Is Norman Harker still around somewhere? Another MVP? After talking about 1900 for a few hours, I may have lost track of time. Okay, it is still 2006, I have just checked the clock on my computer. A while back, I had a post in another MS forum talking about "teleport" which was a feature I attempted to do using the MS software.

I'll worry about WORKDAY() after I have got some rest. What does ATP stand for? Just looking at your formula, I am relieved although I haven't digested it yet. Do you want to see the three-line formula a reputable web site suggested?

What do I do without you, Bob?

Epinn


"Bob Phillips" wrote in message ...

"Epinn" wrote in message
...
Thank you all. This has been very educational. I have a few comments.

If I key 9/9/2006 into A1, what formula do I use in A2 to get 38969.
Please advise.

=A1 and format as General. As we said the underlying value of a date is just
the number of days since 1st Jan 1900, so it is already that number. You
just format it to see it.


If I key 9/9/2006 into A1, I think I can reference A1 directly in the
above formula,
instead of entering =A1 in A2 and then using A2 in the above formula.

Correct

The argument: 2/14/2008 is the equivalent of: 2 divided by 14 divided
by 2008
which equals: 0.0000711439954467843........Weekday( 0 ) is actually
12/31/1899
which is a Saturday or weekday 7 when the return_type used is 1 or
omitted.

Don't think I want to agree with this. I key in =WEEKDAY(0) into a cell,
I get 7-Jan-00.
Don't know why and not sure if we are talking about the same thing.

You may not want to, but Biff is right. 1st Jan 1900 was a Sunday, the 31st
Dec 1899 was a saturday, which is day 7 to WEEKDAY. You get 7-Jan-00 because
it is formatted that way, the underlying value is 7.

=MONTH(0) yields 1-Jan-1900. In both cases, we never got back to 1899.

No, it yields 1. You just have it formatted as a date. A month number is not
a date, it is the ordinal value of the month within the year.

=WEEKDAY(--"1899/12/31") gives an error #VALUE! which makes sense as
the date system starts at 1/1/1900.

It does, as Excel "knows" that is not a date in its view of the world, but
you can fool it

=WEEKDAY(--"1900/01/01"-1)

returns 7.

Again as Biff, this is another nuance of Excel.

Talking about 1899 makes me feel very old ;) but all this is very
interesting.
Please keep the date talk going.

It would be nice if Norman Harker joined the discussion. He has made the
study of dates a speciality.

Next I have to analyze the formula for "last workday of the current
month."
This formual is three-line long and uses EOMONTH(). Looks tough.
If it gets too confusing, I'll just use it without understanding it.
There is a shorter formula but uses "holidays" as part of the syntax.
(Holidays is not a function.)

How about

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),-1)

or without the ATP function

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)
-5))

Epinn

"Bob Phillips" wrote in message
...

I know what you mean about the format. I have experienced that problem so
often that I added an edit field to one of my toolbars which shows the
format of the activecell. I cannot tell you how often I have found that
useful, the cell that I thought was a number turns out to be text.

Unfortunately, Norman seems to have been offline for quite some time. He is
an Excel MVP, and he specialises in Excel financial and date and time
functions (and giving extremely long and detailed answers <vbg). If he were
around I am sure he wouldn't have written you an answer that would explain
things far better than I have.

ATP is the Analysis Toolpak, a Microsoft Excel addin that provides some
extra functions, of which the WORKDAY function is but one. and yes, I would
be interested in seeing your 3 line solution (and also knowing where it was
recommended <G)

BTW, my first formula is wrong, it should be

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

=A1 and format as General. As we said......

Thank you for being patient and putting up with me. I am very unfamiliar
with dates, just starting.
I did hear you - "......underlying value of a date is just the number of
days since 1st Jan 1900...... when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of
39559......" I registered the small pieces of info and I thought I
understood. But when I tried to piece them together ...... Here's what has
happened.

I had a blank cell and I thought I saw the format as "general." Then I
keyed in a date like 21/4/2008 into the cell and I saw the exact same thing
as I had keyed in. ***I was under the impression that "general" was still
the format***. I didn't recheck the format at that point. When I did, I
noticed that it was changed from "general" to "date." This is why I don't
know that "general" format can display the actual no. of days. So, this is
what I have missed and you can tell how green I am.

You may not want to, but Biff is right. 1st Jan 1900 was a Sunday, the
31st
Dec 1899 was a saturday, which is day 7 to WEEKDAY. You get 7-Jan-00 because
it is formatted that way, the underlying value is 7.

When I read Biff's writeup, I found it very logical because I did agree that
1/1/1900 was a Sunday. But when I keyed in =WEEKDAY(0) ......

=WEEKDAY(--"1900/01/01"-1) returns 7.

Ah, this is what I have missed from Biff's writeup and what I didn't do.
Now, I am totally convinced. Bob, you are such a good teacher and thank you
for clarifying. Now, I can explain =WEEKDAY(0) to other people in
**absolute details**. ;)

Epinn wrote: =MONTH(0) yields 1-Jan-1900......

Bob wrote: No, it yields 1. You just have it formatted as a date.

Sorry, I skipped one step. I like programmers and/or accountants because
they talk my language - exact and precise. Okay, I slipped occasionally.
;)

Is Norman Harker still around somewhere? Another MVP? After talking about
1900 for a few hours, I may have lost track of time. Okay, it is still
2006, I have just checked the clock on my computer. A while back, I had a
post in another MS forum talking about "teleport" which was a feature I
attempted to do using the MS software.

I'll worry about WORKDAY() after I have got some rest. What does ATP stand
for? Just looking at your formula, I am relieved although I haven't
digested it yet. Do you want to see the three-line formula a reputable web
site suggested?

What do I do without you, Bob?

Epinn

Thanks RD,

That is a lot tidier than my Vlookup!

With the amount of computing power that is available these days
there is no real need to shorten processes (make it work no matter how
long winded and that's good enough is all that it really requires), however,
I still like to cut corners wherever I can. Old Habits...etc, etc.

Many Thanks
Martin

"MartinW" wrote in message
...

With the amount of computing power that is available these days
there is no real need to shorten processes (make it work no matter how
long winded and that's good enough is all that it really requires),
however,
I still like to cut corners wherever I can. Old Habits...etc, etc.

I think there is. It is very easy to bring a spreadsheet to its knees with
poor design, coding, etc. Keep looking to cut those corners.

I was in no way suggesting that Norman would explain things far better than you did. I was just wondering if he is someone of our time. Yes, good point, you are precise and concise which is very important as I can get lost in long strolls.

Everyone, I am going to start a new thread on WORKDAY(). If you are interested to join in, please move over.

Epinn

"Bob Phillips" wrote in message ...
I know what you mean about the format. I have experienced that problem so
often that I added an edit field to one of my toolbars which shows the
format of the activecell. I cannot tell you how often I have found that
useful, the cell that I thought was a number turns out to be text.

Unfortunately, Norman seems to have been offline for quite some time. He is
an Excel MVP, and he specialises in Excel financial and date and time
functions (and giving extremely long and detailed answers <vbg). If he were
around I am sure he wouldn't have written you an answer that would explain
things far better than I have.

ATP is the Analysis Toolpak, a Microsoft Excel addin that provides some
extra functions, of which the WORKDAY function is but one. and yes, I would
be interested in seeing your 3 line solution (and also knowing where it was
recommended <G)

BTW, my first formula is wrong, it should be

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

=A1 and format as General. As we said......

Thank you for being patient and putting up with me. I am very unfamiliar
with dates, just starting.
I did hear you - "......underlying value of a date is just the number of
days since 1st Jan 1900...... when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of
39559......" I registered the small pieces of info and I thought I
understood. But when I tried to piece them together ...... Here's what has
happened.

I had a blank cell and I thought I saw the format as "general." Then I
keyed in a date like 21/4/2008 into the cell and I saw the exact same thing
as I had keyed in. ***I was under the impression that "general" was still
the format***. I didn't recheck the format at that point. When I did, I
noticed that it was changed from "general" to "date." This is why I don't
know that "general" format can display the actual no. of days. So, this is
what I have missed and you can tell how green I am.

You may not want to, but Biff is right. 1st Jan 1900 was a Sunday, the
31st
Dec 1899 was a saturday, which is day 7 to WEEKDAY. You get 7-Jan-00 because
it is formatted that way, the underlying value is 7.

When I read Biff's writeup, I found it very logical because I did agree that
1/1/1900 was a Sunday. But when I keyed in =WEEKDAY(0) ......

=WEEKDAY(--"1900/01/01"-1) returns 7.

Ah, this is what I have missed from Biff's writeup and what I didn't do.
Now, I am totally convinced. Bob, you are such a good teacher and thank you
for clarifying. Now, I can explain =WEEKDAY(0) to other people in
**absolute details**. ;)

Epinn wrote: =MONTH(0) yields 1-Jan-1900......

Bob wrote: No, it yields 1. You just have it formatted as a date.

Sorry, I skipped one step. I like programmers and/or accountants because
they talk my language - exact and precise. Okay, I slipped occasionally.
;)

Is Norman Harker still around somewhere? Another MVP? After talking about
1900 for a few hours, I may have lost track of time. Okay, it is still
2006, I have just checked the clock on my computer. A while back, I had a
post in another MS forum talking about "teleport" which was a feature I
attempted to do using the MS software.

I'll worry about WORKDAY() after I have got some rest. What does ATP stand
for? Just looking at your formula, I am relieved although I haven't
digested it yet. Do you want to see the three-line formula a reputable web
site suggested?

What do I do without you, Bob?

Epinn

You might not of been, but I certainly was <vbg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
I was in no way suggesting that Norman would explain things far better than
you did. I was just wondering if he is someone of our time. Yes, good
point, you are precise and concise which is very important as I can get lost
in long strolls.

Everyone, I am going to start a new thread on WORKDAY(). If you are
interested to join in, please move over.

Epinn

"Bob Phillips" wrote in message
...
I know what you mean about the format. I have experienced that problem so
often that I added an edit field to one of my toolbars which shows the
format of the activecell. I cannot tell you how often I have found that
useful, the cell that I thought was a number turns out to be text.

Unfortunately, Norman seems to have been offline for quite some time. He is
an Excel MVP, and he specialises in Excel financial and date and time
functions (and giving extremely long and detailed answers <vbg). If he were
around I am sure he wouldn't have written you an answer that would explain
things far better than I have.

ATP is the Analysis Toolpak, a Microsoft Excel addin that provides some
extra functions, of which the WORKDAY function is but one. and yes, I would
be interested in seeing your 3 line solution (and also knowing where it was
recommended <G)

BTW, my first formula is wrong, it should be

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

=A1 and format as General. As we said......

Thank you for being patient and putting up with me. I am very unfamiliar
with dates, just starting.
I did hear you - "......underlying value of a date is just the number of
days since 1st Jan 1900...... when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of
39559......" I registered the small pieces of info and I thought I
understood. But when I tried to piece them together ...... Here's what has
happened.

I had a blank cell and I thought I saw the format as "general." Then I
keyed in a date like 21/4/2008 into the cell and I saw the exact same thing
as I had keyed in. ***I was under the impression that "general" was still
the format***. I didn't recheck the format at that point. When I did, I
noticed that it was changed from "general" to "date." This is why I don't
know that "general" format can display the actual no. of days. So, this is
what I have missed and you can tell how green I am.

You may not want to, but Biff is right. 1st Jan 1900 was a Sunday, the
31st
Dec 1899 was a saturday, which is day 7 to WEEKDAY. You get 7-Jan-00 because
it is formatted that way, the underlying value is 7.

When I read Biff's writeup, I found it very logical because I did agree that
1/1/1900 was a Sunday. But when I keyed in =WEEKDAY(0) ......

=WEEKDAY(--"1900/01/01"-1) returns 7.

Ah, this is what I have missed from Biff's writeup and what I didn't do.
Now, I am totally convinced. Bob, you are such a good teacher and thank you
for clarifying. Now, I can explain =WEEKDAY(0) to other people in
**absolute details**. ;)

Epinn wrote: =MONTH(0) yields 1-Jan-1900......

Bob wrote: No, it yields 1. You just have it formatted as a date.

Sorry, I skipped one step. I like programmers and/or accountants because
they talk my language - exact and precise. Okay, I slipped occasionally.
;)

Is Norman Harker still around somewhere? Another MVP? After talking about
1900 for a few hours, I may have lost track of time. Okay, it is still
2006, I have just checked the clock on my computer. A while back, I had a
post in another MS forum talking about "teleport" which was a feature I
attempted to do using the MS software.

I'll worry about WORKDAY() after I have got some rest. What does ATP stand
for? Just looking at your formula, I am relieved although I haven't
digested it yet. Do you want to see the three-line formula a reputable web
site suggested?

What do I do without you, Bob?

Epinn

I must agree with Bob.

No matter how much computing power you have available, it is *very* easy to
bring the program to it's knees with *deficient* formulas/coding.

What can seem to operate efficiently in a 100 X 100 worksheet, can STOP an
entire machine if inserted in a 10,000 row database sheet.
And think what *will* happen with the XL07 *million* row program!

For example:

A Vlookup() formula, with an additional Vlookup() function call inserted for
error checking in a 40,000 X 150 database sheet can take 20 minutes to load
and/or recalc, where replacing it with an Index() and Match() combo can
reduce that to under 10 minutes.
Mind you, I'm *not* advocating XL's use as a database, just showing
comparisons.

AND ... *don't* try this at home ... BUT ...

=SUM($A$1:A1)

If used to produce a running total, works beautifully for several hundred
rows,
*BUT*
Just copying down 15,000 - 20,000 rows, can take more then 2 or 3 minutes.
AND - deleting that column of formulas can take in excess of 30 - 45
minutes.

SO, you should/must *always* be in the market for shorter, more efficient,
and/or smarter solutions.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bob Phillips" wrote in message
...

"MartinW" wrote in message
...

With the amount of computing power that is available these days
there is no real need to shorten processes (make it work no matter how
long winded and that's good enough is all that it really requires),
however,
I still like to cut corners wherever I can. Old Habits...etc, etc.

I think there is. It is very easy to bring a spreadsheet to its knees with
poor design, coding, etc. Keep looking to cut those corners.

Hi Bob and RD

I guess I've got a bit of tunnelvision there!

You are right of course, however, where I work we really don't have
to be concerned about these sort of issues as all our spreadsheets are
very small. A 100 x 100 sheet would be fairly large to us.

Our sheets handle some very complex equations but any data analysis
and data storage is handled by a couple of database programs.

Basically we use Excel like a very powerful programmable
scientific calculator. Rather than looking for a smart solution that will
encompass all possibilities. It is quite often simpler to break a
calculation
down to small workable components with the use of helper cells
and put it all back together at the end.

Due to the fact that we are only using a very small part of the program's
capabilities we can take a lot of freedoms that would be unthinkable
in other disciplines (as you both quite rightly pointed out.)

The bottom line is it works for us very very well. I'm sure there are
plenty of other users who use Excel with the same cavalier attitude
as ourselves but collectively we would be a very small minority group.

Kind Regards
Martin

Biff,

Thank you for taking the time to explain things to me, another "perfectionist." Unfortunately, I am learning how to run before I can walk steadily. Bob is right and I am having an episode of "information overload" at this time. I am sure that I can appreciate your analysis even better in the future and I look forward to more. By the way, "anal" is good, and I don't feel like I am the only one being detailed. All good programmers have a sense of perfection.

Cheers,

Epinn

"Biff" wrote in message ...
When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

Actually, the formula does not "error" but calculates properly.

The argument: 2/14/2008 is the equivalent of:

2 divided by 14 divided by 2008

which equals: 0.0000711439954467843

Since WEEKDAY only works with integers Excel truncates the value to 0.

Weekday( 0 ) is actually 12/31/1899 which is a Saturday or weekday 7 when
the return_type used is 1 or omitted.

This is another Excel nuance. The date serial system doesn't start until day
1 which is 1/1/1900 yet you can calculate a date to be 1/0/1900. Excel
treats the 0th day of the month as the last day of the previous month.
That's how we end up with 7 as the result of the Weekday function.

Biff

"Bob Phillips" wrote in message
...
But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559,
which it presents/formats as that date.

When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

If you want to enter the date into the WEEKDAY function, you have to force
it into a number, either using another function such as you did with DATE,
or coerce it directly, like

=WEEKDAY(--"21/4/2008")

or my preferred format of

=WEEKDAY(--"2008-04-21")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Regarding WEEKDAY(), I read that problems can occur if dates are entered
as
text.

Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a
wrong
result (7). This is because general format is same as text.

If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5).

Okay, so far. What I don't understand is the following.

I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I
also
get the correct result (5).

The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008
*directly* to the formula. It amazes me that referencing A1 in WEEKDAY()
gives me the correct answer whereas keying it in as part of the formula
won't work.

Comments welcome.

Epinn

I better clarify for those who haven't read the other thread. "Anal" is a compliment by _ _ _ and I just carried it over. Hope using the word here hasn't offended anyone. ;)

Epinn

"Epinn" wrote in message ...
Biff,

Thank you for taking the time to explain things to me, another "perfectionist." Unfortunately, I am learning how to run before I can walk steadily. Bob is right and I am having an episode of "information overload" at this time. I am sure that I can appreciate your analysis even better in the future and I look forward to more. By the way, "anal" is good, and I don't feel like I am the only one being detailed. All good programmers have a sense of perfection.

Cheers,

Epinn

"Biff" wrote in message ...
When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

Actually, the formula does not "error" but calculates properly.

The argument: 2/14/2008 is the equivalent of:

2 divided by 14 divided by 2008

which equals: 0.0000711439954467843

Since WEEKDAY only works with integers Excel truncates the value to 0.

Weekday( 0 ) is actually 12/31/1899 which is a Saturday or weekday 7 when
the return_type used is 1 or omitted.

This is another Excel nuance. The date serial system doesn't start until day
1 which is 1/1/1900 yet you can calculate a date to be 1/0/1900. Excel
treats the 0th day of the month as the last day of the previous month.
That's how we end up with 7 as the result of the Weekday function.

Biff

"Bob Phillips" wrote in message
...
But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559,
which it presents/formats as that date.

When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying
value). 21/4/2008 is not a number here, so it errors.

If you want to enter the date into the WEEKDAY function, you have to force
it into a number, either using another function such as you did with DATE,
or coerce it directly, like

=WEEKDAY(--"21/4/2008")

or my preferred format of

=WEEKDAY(--"2008-04-21")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Regarding WEEKDAY(), I read that problems can occur if dates are entered
as
text.

Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a
wrong
result (7). This is because general format is same as text.

If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5).

Okay, so far. What I don't understand is the following.

I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I
also
get the correct result (5).

The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008
*directly* to the formula. It amazes me that referencing A1 in WEEKDAY()
gives me the correct answer whereas keying it in as part of the formula
won't work.

Comments welcome.

Epinn