WEEKNUM in pivot returns 1900-01-31

[martin]

Hi!

I'm sitting with an excel pivot that has one column (Weeks) which
originates from fields formatted like this: =WEEKNUM(G445;2). But in
the pivot, the field magically converts into 1900-01-31 (Swedish date)!
It looked alright to me until 1 Augusti, when the weeknumber changed
from 31 to 32. Then it restarted from 31 to 1

I've tried to change the original formula to =TEXT(WEEKNUM(G445;2);0),
but the pivot can't sort the list properly, thinking that 21 comes
after 1...

Any ideas? Thanks alot!
/Martin

 

[Dave Peterson]

If you just format that field (in the pivottable) as General, what do you see?

 

[martin]

Well... It corrects itself to the actual weeknumber. Until you refresh
the pivot table, that is, the it reverts to 1900-01-31. Is it possible
to save the General or Number column setting?
/Martin


Dave Peterson skrev:

 

[martin]

Actually, I found a solution in another question here. It included
disabling autoformat, enable preserver formatting, select in pivot menu
and THEN format cells
Thank you alot for pointing me to the right direction!!
/Martin

(e-mail address removed) skrev:

 

[Dave Peterson]

Thank goodness for Debra's post, huh, or, eh?

Actually, I found a solution in another question here. It included
disabling autoformat, enable preserver formatting, select in pivot menu
and THEN format cells
Thank you alot for pointing me to the right direction!!
/Martin

(e-mail address removed) skrev:

 

[martin]

I'm not sure it's Debras post I read or if I found it on a "normal"
Google search

Dave Peterson skrev: