Weird number instead for a date!

anderson_markus

New Member
Joined
Apr 5, 2013
Messages
3
I have one field that writes different values depending if you in a pivot table choosed year, month, week or day as a headline.
Everything but day works. It displayed weird value instead.

The code I´m using is:
=IF(ISNUMBER(B4);CONCATENATE("Year ";B4);IF(ISNUMBER(B3);VLOOKUP(B3;'Calculate Call Avoided.xlsx'!Month;2;0);IF(ISNUMBER(B2);CONCATENATE("Week ";B2);IF(ISNUMBER(B1);CONCATENATE("Day ";B1);""))))
The date is in folowing format 2013-02-04 (YYYY-MM-DD) and the datefield that the pivot table retrieves the dates from has Date format.
Allthough you will find here next how it looks when I´m trying to display the Day headline (This example is when I choosed the date 2013-02-04): Dag 41309.

How can this be fixed?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Instead of just CellReference replace it like this

Rich (BB code):
TEXT(CellReference,"YYYY-MM-DD")
 
Upvote 0
Thanks for quick reply.
I tried that but I recieved the standard error msg:
The formula you typed contains an error.
¤ For information about fixing vommon formula problems, click Help.
¤ To get assistance in entering a function, click Function Wizard (Formula tab, Function Library group).
¤ Ig you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or procede it with a single quotation mark (').

Then it highlights B1,'YYYY in CONCATENATE("Day ";TEXT(B1,"YYYY-MM-DD"));"")))).
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top