Strange Excel Problem: WEEKDAY function is 1 day off

[sammich]

I'm trying to 'digitise' a fuel log for my car. You know, dates, fill amount, cents per litre (metric here people), total cost etc.

However, there is a strange problem in MS Excel 2004. I'm trying to make excel give me the day of the week a that date fell on using the WEEKDAY() function. But the day it gives me is one early, ie Monday should be Tuesday. (see attachment)

So I figure out that the ENTIRE "WEEKDAY()" function is exactly one whole day off for every single date (from 1904 right through to 2008, using iCal).

Is there anyway to fix this problem (Googling yielded no results)? I tried doing '=WEEKDAY("date") +1' which fixes the problem, only now, Sunday doesn't exist. And, I tried using a custom cell format "DDDD" with the date itself (no formulas, it'll give me the correct weekday, but other problems arise with the fact that the format in that cell is no longer a integer, but still a date format.

Any help?

Cheers,
Sam

 

[robbieduncan]

There is no attachment!

It sounds like you are expecting Monday to be 1 and Sunday to be 7? Have you considered that Sunday could be 1?

 

[Veldek]

I had a similar idea. In the US the week begins with Sunday while in Europe it begins with Monday. Perhaps you have to set the date format somewhere in the options.

 

[sammich]

Oops, the attachment is here.

Nah, I've thought of that one already, and the excel function allows to to fix it with an extra parameter. But those parameters only makes the result worse. Try it yourself, try say

(the below is partly shown in the attach.)

01/01/1904 <say cell "A1" <-- day is Friday
=WEEKDAY(A1, 1) --> gives 6, which is Thursday
=WEEKDAY(A1, 2) --> gives 5, which is a Wednesday

 

[sammich]

This second image shows the changing of the parameters properly. The dates are the same, but only the parameters have changed. As you can see, the days are moving further away from the right day.

 

[cheppo]

I have the same problem. There is a bug with using =TEXT(WEEKDAY(cell),"ddd").

The Mac version of Excel returns one day off or the previous day.

Did you figure out a workaround. So annoying?

 

[sammich]

I have the same problem. There is a bug with using =TEXT(WEEKDAY(cell),"ddd").

The Mac version of Excel returns one day off or the previous day.

Did you figure out a workaround. So annoying?

The problem is the 'WEEKDAY' function I think, so you can just drop it out. This is what worked for me. I found it under custom formats.

=TEXT(A122, "DDDD")

 

[dicejam]

I too had the same issue using Excel Mac '08. I think the issue is the default manner in which dates are stored or indexed.

I got around this issue by changing excel preferences.
1) Excel Preferences
2) Choose tab/option Formulas and Lists | Calculation | Workbook Options
3) Uncheck 'Use the 1904 date system' ( it was checked by default)

This should solve the issue. However, I am unsure about its other side-effects!!

 

[scubasteev]

I was having the same problem and followed dicejam's advice and everything worked. The TEXT function is old Excel and won't work in newer versions on Windows platforms. I searched why macs use the 1904 system and here is an explanation by Microsoft. Overall I don't think there are any "side affects".

Differences between the 1900 and the 1904 date system in Excel
http://support.microsoft.com/kb/214330