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