Forum Discussion

Copper Contributor

Oct 24, 2019

Solved

Not all date fields are showing up in Timeline slicer option

I have two fields (reqd date and promisedshipdate in table. Both are formatted as date. When a do a Pivot table of this table and try to create a timeline slicer the promisedship date does not show �...

excel

SergeiBaklan
Oct 24, 2019
IWT_GG

You may format cells as dates but actually have texts within it. You may check by ISTEXT() if so or not.

IWT_GG

Copper Contributor

Oct 25, 2019

AAbdou

Dear Abdelrahman thank you for responding to my post / query.

I tried refreshing all data connections, as well closing and reopening the workbook. Unfortunately it did not work.

The reason was because some date were in Text format. So even thought the fields / column was formatted but due to a few bad formatted data it was not pulling.

I have given details of this in another post. If you could help me on addressing the root cause that would be greatly appreciated as well.

Thank you very much for your valuable insights and time.

AAbdou

Copper Contributor

Oct 26, 2019

Hi IWT_GG

can you provide the link to the other post?

also, as SergeiBaklan mentioned, the issue is that the fields are not numbers formatted as dates, so you need to make sure they are numbers formatted as dates and not text. You could do that using the DATEVALUE function for example.

Abdelrahman Abdou,

Certified Microsoft Excel Expert, Consultant.

Founder, Owner @ ExcelBonanza.com

http://www.excelbonanza.com/ |

https://www.linkedin.com/in/abdelrahman-abdou-60240150 | https://www.youtube.com/channel/UC2-D2unZHustY_f6V7cj03Q | https://www.facebook.com/ExcelBonanza/

IWT_GG
Copper Contributor
Oct 26, 2019
Yes problem solved. I used date(0,1,1) to ensure all default values are in Date format

Thank you for your help
- SergeiBaklan
  Diamond Contributor
  Oct 28, 2019
  IWT_GG
  
  You may simplify your formula and instead of DATE(0,1,1) simply return 1. If your cell is formatted as date it returns Jan 01, 1900 - in Excel dates are just sequential integers starting from 1 which is equivalent of 1900-01-01. Assuming you are on Windows default settings, on Mac dates start from year 1904.
- AAbdou
  Copper Contributor
  Oct 27, 2019
  HiIWT_GG ,
  I'm glad that your problem has been solved.
  
  Abdelrahman Abdou,
  Certified Microsoft Excel Expert, Consultant.
  Founder, Owner @ ExcelBonanza.com
  
  http://www.excelbonanza.com/ |
  https://www.linkedin.com/in/abdelrahman-abdou-60240150 | https://www.youtube.com/channel/UC2-D2unZHustY_f6V7cj03Q | https://www.facebook.com/ExcelBonanza/