Excel Date showing as a number in display

[bruce_sorge]

Hello there,

I have a field in Excel that is a date, but with text. For instance, if a grant was extended, then the date looks like this:

October 2016 (Extended to October 2017)

Otherwise it's just month/day/year. When I add this field as a measure, it looks like this:

Regular date: 40756

Date with Extended it it:

October 2016,(Extended to October 2017)

What is the best way to just get this to show up like this:

10/1/2016 (Extended to October 2017)

In Sense, the property is set to Text.

Thanks

[Rodj]

Hi Bruce,

Excel sometimes does some funny things with dates, combined with the unique way Qlik deals with them it can get confusing at times. That said I suspect in this case though that all you need to do is wrap your date  in a date function to format it the way you want. To just get the month year as you've described try the following as your expression:

Date([Project End Date], 'MMM YYYY')

Try that to start with and we can go from there. If that doesn't work it would be handy if you could share an example file.

Cheers,

Rod

[bruce_sorge]

Hi rodj,

This formats the field fine, but the problem is that I need to see the extension information as well. I am attaching a couple example records to the corresponding web page so you see what the data looks like. Look at the Project End Date field.

Thanks

[fortaleza40]

I had that issue,  the problem  is how Qlik receives the date from database,  it fixed when i send the date as text but formatted in a format that Qlik can recognize as date:

TO_CHAR(fieldname::TIMESTAMP::DATE,''MM/dd/YYYY'')

Which date & time fields are automatically recognized ?

Date & timestamp fields will be recognized automatically based on your system locale settings. Additionally, the following formats are recognized:

• M/D/YYYY h:mm
• D/M/YYYY h:mm TT
• M/D/YYYY
• D/MM/YYYY
• YYYYMMDD
• YYYYMMDDhhmmss
• YYYYMMDDhhmmss.fff
• YYYYMMDDhhmmssK
• YYYY-MM-DD
• YYYY-MM-DDThh:mm:ss
• YYYY-MM-DD-Thh:mm:ss.fff
• YYYY-MM-DD-Thh:mm:ssK

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/Assets/date-time-fields...

[Rodj]

I'm not sure I'm understanding what you are after. Your initial question mentioned using the field in a measure, but from looking at your data that isn't going to work. If you want to strip the date out and then use that to calculate a measure that could work. to do something like that you could try:

Date(left([Project End Date], FindOneOf([Project End Date],'(') -1), 'MMM YYYY')

This would just pull the date portion out that you could then use in measures. Similarly you could strip out the extended  text and keep it. You could do this either in the data load or in your visualisations, though I'd be doing it in my data load and storing it in a new field. An expression such as the following would strip out the extension text:

Right([Project End Date], Len([Project End Date]) - FindOneOf([Project End Date],'('))

If you want to see the whole string as presented in the excel you could then join them back together if they aren't coming through properly.

Hope that helps somewhat.

[bruce_sorge]

Hi Rodj,

Yes, I guess I was not clear enough. I want to include the extended text, but this is being used in a dimension, not a measure. Again, if I was not clear I apologize. I have been able to successfully extract just the date and present it, so now onto the other part, the extended text.

Thanks

[Rodj]

Hey, no worries, hopefully the expressions I've given you put you on the path to achieve what you are after.

Cheers,

Rod