WEEKDAY()

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