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.