weekday function returns wrong date-what is DIRECT method of getting day of week without having to put in a code number for Sun=1 or whatever

Question

I'm using =TEXT(WEEKDAY(O10), "ddd") but I keep getting the WRONG day of the week as an answer.

I've tried different dates, I've triple checked that I did have the correct day of the week, but I keep getting the wrong day of the week as an answer in different places.

I think Excel ***should*** have a direct method of getting the day of the week from a given date WITHOUT resorting to some ridiculous code for designating Sun=1 or Mon=1 etc.  There is a one to one correlation between a given date and a day of the week.

The function for designating either Sun or Mon as the first day of the week is IRRELEVANT to my purpose.  Is there a DIRECT method of getting the day of the week, in text (Mon, Tue etc) from a given date?  What is it? I don't want to have to go back and have to check whether the answers I'm getting is right or wrong.

The problem keeps happening on different computers with different Windows versions.  It is Excel 2007 though.

BTW, I have made sure that it is refering to the cell that it should be referring to, in other words, I have made sure that the mistake is not only part. 

OK, this is bizarre.  I just created a new workbook where I wrote in today's date, copied the same function I wrote above  from a different workbook in which that function does NOT work  and it did work in the brand new workbook.  I then went back to the original workbook and double checked EVERYTHING.  The identical formula does NOT work --get this --for the exact same date it returns a wrong day of the week in that workbook, while it returns the right day of the week in the new workbook.  So...WTF?!!?

The formula is EXACTLY identical => I copied it from one workbook to the other!  The date on which it is being applied is the same!

What is not obvious???  Neither workbook has hidden formating in those rows/columns. 

The original data was imported from MS Word.  I don't know of any hidden formatting that would be carried over and interfere.

So what am I missing?

Answer 1

RV wrote:

I am not finding the format  Custom ddd Under Format Cells/Custom--there is not "ddd"

No, you just have to type it into the Type field.

RV wrote:

> if you re-read my  post, I added that Excel is acting funny.

> Identical formulas will return one day in one workbook, but

> another in another workbook.  I don't know what is causing this.

Neither do I; too little information for an educated assessment.  I did not miss the comment.  It's just that I try not to comment on things that are most likely a user error unless I can offer a solution.

Besides, you were using the wrong function (WEEKDAY), so what's the point of pursuing it?

My wild-a.s.s guess is that whether or not TEXT(WEEKDAY(A1),"ddd") seemed to work was pure coincidence.

WEEKDAY(A1) returns the number 1 through 7, which TEXT would consider as the dates 1/1/1900 through 1/7/1900 in that context.  The day of the week ("ddd") that translates into has nothing to do with the original date in A1.  It is pure coincidence if happens to match the day of week of A1.

As to why "for the exact same date it returns a wrong day of the week in that workbook, while it returns the right day of the week in the new workbook", I can only guess that things are not exactly as you perceive or present them.  User error is more likely than anything else, especially given your obvious level of frustration with the problem.

If you want anything better than a WAG, I suggest that you upload an Excel file that demonstrates the problem to a file-sharing website and post the URL (http://...) here.  Be sure to make the uploaded file shared or sharable.  The following are some free file-sharing websites.

Windows Live Skydrive: http://skydrive.live.com

MediaFire: http://www.mediafire.com

FileFactory: http://www.filefactory.com

FileSavr: http://www.filesavr.com

FileDropper: http://www.filedropper.com

RapidShare: http://www.rapidshare.com

Box.Net: http://www.box.net/files

Answer 2

If by "in text" you mean in the form of Mon, Tue etc then you can do it by using a date format of "ddd" or "dddd" if you want Monday, Tuesday etc. on the actual date.

So taking your example your cell would be =O10 and the format Custom (ddd) this takes the day from the date rather than the weekday calculation.

If you actually want "real text" then I don't know sorry.

Answer 3

RV wrote:

I'm using =TEXT(WEEKDAY(O10), "ddd") but I keep getting the WRONG day of the week as an answer.

Try:

=TEXT(O10,"ddd")

where O10 contains an Excel date.  And Excel date is a number that represents the days since 12/31/1899 (displayed as 1/0/1900).

But you might not even need to use the TEXT function.  Try:

=O10

formatted as Custom ddd.

EDIT2....  However, in that case, note that the cell content is not Mon, Tue, etc.  So if the second formula above were in P10, you could not write elsewhere =IF(P10="Mon",...,...).  But you could write =IF(TEXT(P10,"ddd")="Mon",...,...).  Or better:  =IF(WEEKDAY(P10)=2,...,...).

EDIT1....

RV wrote:

> The original data was imported from MS Word.

I wonder if the date is being entered as text, not a number.

Be sure that TYPE(O10) returns 1, not 2.

If the latter, please post exactly how the date appears in O10.

Answer 4

I am not finding the format  Custom ddd Under Format Cells/Custom--there is not "ddd"

I did try the shortened  formula asn it worked in the one cell that I used it on.  But, if you re-read my  post, I added that Excel is acting funny.  Identical formulas will return one day in one workbook, but another in another workbook.

I don't know what is causing this.

Either way, where can I find format 'ddd' since I can't find it under Format/Custom/ddd?