DAY-Function fails

Excel canīt count anymore:

C5 is given a date (german:18.4.2011, englisch:2011-04-18)

A2 is set to: =TAG(C5) (englisch: DAY(C5))

Result in A2: 19

No joke! See att. Any ideas?

P.S.: Excel 2004 for Mac (11.5.6). At an other worksheet result is correct.

Please, rename your title according to the rules:

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

So you got correct result in other sheets in same workbook?

What happen when you change date in the cell? Do you have correct result?

If you review the Options which Date System are you using ?
Presumably given Mac you're using 1904 Date System ?

I can't replicate your issue on PC (ie both date systems would return 18), however, I don't have a Mac.
The difference between the 1900 & 1904 Date system is 4 years and 1 day.

If you format C5 as General what is the Serial Number ?

Originally Posted by johntrav2004

Note, that creating a new table WITHIN the same excel-file and filling in the same formula as described gives correct results without changing the options.

Yes, this was the point I was alluding to regards not being able to replicate your issue.

The key to remember is the difference between the 2 systems equates to 4 years and 1 day - eg:

0 on 1900 system is 0-Jan-1900 (default for PC)
0 on 1904 system is 1-Jan-1904 (default for Mac)

It follows that if you copy Date values (serials) across files using different date systems the value of the Date Serials will alter, eg:

2 files
Book1: 1900 system
Book2: 1904 system
[Book1]Sheet1!A1 = 18-Jun-2004 (Serial No. 38156)
If we copy the above to Book2 the date in Book2 will "transform" to: 19-Jun-2008 (Serial No. 38156)

What I could not replicate was how your date value in Book2 was showing as 18th of Month and DAY was returning 19 - this may be a Mac specific characteristic.

hmm... could it be alse due to leap year?

Is that definitely a date in C5? It would be interesting to see what you get if you format C5 to general (as DonkeyOte says)

I note that you also have Montag In C2 - that's correct for 18th April 2011 - do you have a formula in that cell? Can you post the actual workbook rather than a screenshot?

Originally Posted by johntrav2004

Yes, C5 ist a date, formatting C5 to general format didīt work.

If C5 is a date then when you format to general you should get a "date serial number", e.g. assuming you have 1904 date system you'd see 39189 for 18th April 2011. If you change the format to general and you still see the same value, i.e. 18.4.2011 then C5 isn't a true date, it's text.

DAY function should still cope with a text formatted date but that might have something to do with the error......

If you go back to the original setup (with original data in C5 and 1904 date system) then what do you get with these formulas?

=YEAR(C5)

=YEAR(C5+0)