Thread: WEEKDAY()
View Single Post
  #25   Report Post  
Posted to microsoft.public.excel.newusers
Epinn Epinn is offline
external usenet poster
 
Posts: 605
Default WEEKDAY()

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