day for date does not match

Question

Remember - This is a public forum so never post private information such as email or phone numbers!

Ideas:I tried to get day, that is sunday through saturday, for a range of dates.  The day and date do not match, for ex. Oct. 04, 2010 is shown as saturday while it is monday.  The mistake is not uniform.  In respect of 1986-Oct 12, instead of Sunday it is displayed as monday.  My windows clock and system displays correct day and date.

• Programs you are having problems with
• Error messages
• Recent changes you made to your computer
• What you have already tried to fix the problem

Answer 1

Thanks for interest. I could resolve it of my own.  Just for your help I am giving the resolved method.

=TEXT(WEEKDAY(A1,1),"dddd") and in A1 I entered =date(2010,10,05) format for yyyy/mm/dd format.  It resolved the issue.

May be that is a crude method but it solves my problem.

Answer 2

What you really want is =TEXT(A1,"dddd"). The TEXT function in this context expects an actual date, not the WEEKDAY of a date.

---

Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com

Answer 3

Whats the formula which you inserted for day of date.

Vijay

Answer 4

Cell format does not appear to be the cause.  The cell returns day or month or year properly.  It simply does not match the day with date.  I used the format =date(yyyy,mm,dd) then in the next cell inserted the formula for day of the date.

Answer 5

How have you formatted the cells where the date and day appear?

Say your date of Oct 4, 2010 is in A1 (formatted as date) then custom format B1 as dddd and enter formula =A1

What result do you get?