Go Back   MacRumors Forums > Apple Applications > Mac Applications and Mac App Store

Reply
 
Thread Tools Search this Thread Display Modes
Old Jul 17, 2007, 05:11 AM   #1
sammich
macrumors 601
 
sammich's Avatar
 
Join Date: Sep 2006
Location: Sarcasmville.
Strange Excel Problem: WEEKDAY function is 1 day off

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
__________________
Official MR IRC
Look at me, I'm own a bunch of things and everyone must know about every single one.
sammich is online now   0 Reply With Quote
Old Jul 17, 2007, 05:31 AM   #2
robbieduncan
Moderator
 
robbieduncan's Avatar
 
Join Date: Jul 2002
Location: London
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?
robbieduncan is offline   0 Reply With Quote
Old Jul 17, 2007, 05:35 AM   #3
Veldek
macrumors 68000
 
Veldek's Avatar
 
Join Date: Mar 2003
Location: Germany
Send a message via ICQ to Veldek Send a message via AIM to Veldek Send a message via MSN to 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.
__________________
Never ask a man what computer he uses. If it's a Mac, he'll tell you. If it's not, why embarrass him?
T. Clancy
PM G5 2x2.5 / Penryn MBP 2.4 / X.6
Veldek is offline   0 Reply With Quote
Old Jul 17, 2007, 05:44 AM   #4
sammich
Thread Starter
macrumors 601
 
sammich's Avatar
 
Join Date: Sep 2006
Location: Sarcasmville.
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 Images
 
__________________
Official MR IRC
Look at me, I'm own a bunch of things and everyone must know about every single one.
sammich is online now   0 Reply With Quote
Old Jul 17, 2007, 05:49 AM   #5
sammich
Thread Starter
macrumors 601
 
sammich's Avatar
 
Join Date: Sep 2006
Location: Sarcasmville.
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 Thumbnails
Click image for larger version

Name:	Picture 4.png
Views:	21
Size:	17.1 KB
ID:	79660  
__________________
Official MR IRC
Look at me, I'm own a bunch of things and everyone must know about every single one.

Last edited by sammich; Jul 17, 2007 at 05:56 AM.
sammich is online now   0 Reply With Quote
Old Jul 1, 2009, 01:33 PM   #6
cheppo
macrumors newbie
 
Join Date: Jul 2009
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?
cheppo is offline   0 Reply With Quote
Old Jul 4, 2009, 09:23 AM   #7
sammich
Thread Starter
macrumors 601
 
sammich's Avatar
 
Join Date: Sep 2006
Location: Sarcasmville.
Quote:
Originally Posted by cheppo View Post
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.

Code:
=TEXT(A122, "DDDD")
__________________
Official MR IRC
Look at me, I'm own a bunch of things and everyone must know about every single one.
sammich is online now   0 Reply With Quote
Old Jul 18, 2009, 12:29 PM   #8
dicejam
macrumors newbie
 
Join Date: Jul 2009
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!!
dicejam is offline   0 Reply With Quote
Old Dec 21, 2013, 02:45 PM   #9
scubasteev
macrumors newbie
 
Join Date: Dec 2013
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
scubasteev is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Applications > Mac Applications and Mac App Store

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
Function Keys Problem hjerpsted MacBook Air 0 Nov 20, 2013 11:54 AM
Clear Pivot Function in Excel Mac lostnhre Mac Basics and Help 1 Jan 26, 2013 06:02 PM
Excel / Google Spreadsheets function to live-update number of Twitter followers? jent Mac Applications and Mac App Store 1 Nov 20, 2012 03:19 PM
Resolved: MacFan0_65 problem; function keys not working shakzilla Windows, Linux & Others on the Mac 0 Jul 25, 2012 07:43 PM
Excel problem The Mercurian Mac Applications and Mac App Store 2 Jul 18, 2012 06:12 AM

Forum Jump

All times are GMT -5. The time now is 03:46 PM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC