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