Strange Excel Problem: WEEKDAY function is 1 day off

Discussion in 'Mac Apps and Mac App Store' started by sammich, Jul 17, 2007.

  1. macrumors 601

    sammich

    Joined:
    Sep 26, 2006
    Location:
    Sarcasmville.
    #1
    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
     
  2. Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #2
    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?
     
  3. macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #3
    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.
     
  4. thread starter macrumors 601

    sammich

    Joined:
    Sep 26, 2006
    Location:
    Sarcasmville.
    #4
    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
     

    Attached Files:

  5. thread starter macrumors 601

    sammich

    Joined:
    Sep 26, 2006
    Location:
    Sarcasmville.
    #5
    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.
     

    Attached Files:

  6. macrumors newbie

    Joined:
    Jul 1, 2009
    #6
    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?
     
  7. thread starter macrumors 601

    sammich

    Joined:
    Sep 26, 2006
    Location:
    Sarcasmville.
    #7
    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.

    Code:
    =TEXT(A122, "DDDD")
     
  8. macrumors newbie

    Joined:
    Jul 18, 2009
    #8
    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!!
     
  9. macrumors newbie

    Joined:
    Dec 21, 2013
    #9
    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
     

Share This Page