We Can't Create a Timeline for This Report because it doesn't have a field formatted as Date

J Man

New Member
Joined
Sep 24, 2014
Messages
8
Hi,

Hoping some one can help with the above.

I am trying to create a timeline on a pivot table where I have a lot of call data, exported from CMS (Avaya CMS Supervisor) into a table in excel.

The date column has been formatted to Date and 14/03/2012 but I still get the above error. Within the pivot table if you select the date column in row's it displays all the dates in the correct format.

I also get the same error when I do an export from a workforce management tool called Teleopti.

Has anyone got any ideas how make the time line pick up the date format?

Many thanks

Jonathan
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
It sounds like excel is reading the date as text.
Are the dates always in a dd/mm/yyyy format (10 characters long)?

If so, you can probably just use a formula to convert it, and then use that within your pivots. This tests well for me using your example:
Code:
=DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))+0
(formatting your formula cell as date)
 
Upvote 0
It sounds like excel is reading the date as text.
Are the dates always in a dd/mm/yyyy format (10 characters long)?

If so, you can probably just use a formula to convert it, and then use that within your pivots. This tests well for me using your example:
Code:
=DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))+0
(formatting your formula cell as date)


Hi,

I have tried the above and it still brings up the same error message.

Any other ideas?

Regards

Jonathan
 
Upvote 0
Scan over your new date field. If any of the dates in the data are errors (or not dates) then my understanding is that will cause that error.

One way is to sort the table by date, place an autofilter and scan down the unique entries.
 
Last edited:
Upvote 0
Scan over your new date field. If any of the dates in the data are errors (or not dates) then my understanding is that will cause that error.

One way is to sort the table by date, place an autofilter and scan down the unique entries.

Sorry the above makes all the dates wrong

Hi,

I found out the issue, which was a data issue, in 45K rows of data we had 4 cells that were not a date value which I would normally delete. Once these were deleted it got rid of the error
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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