EXTRACTION OF DATES IN DD/MM/YYYY FORMAT FROM EXCEL

Chocobo88

Forums

Hello. I have a project which requires me to extract a series of dates from a field named TERM_DATE from an uploaded Excel spreadsheet into Idea. The instruction I was given was to use TERM_DATE <> " in the equation editor, which is supposed to extract terminations not equal to blank but returns a Mismatch field type error. The dates are in the dd/mm/yyyy format as they appear in the database, but Idea is set to yyyy/mm/dd format. The extracion fails, I suspect it has to deal with the differnet date formats, and I've tried several times to append the date field to no sccess. I've done extractions before, but none involving dates, or had to deal with the issue of date formats. This is for a school assignment, I wouldn't be asking for help, but I've contacted the prof., and have receicved no guidance on the issue. I've included a copy of the Excel spreadsheet.

Brian Element Sun, 08/14/2022 - 14:41

Hi Chocobo88,

When you did the excel import did it bring it in as a date field?  If you look in the field manipulation does it show up as a date field?

If this is a date field then the blanks should show as 0 so your equation would be the following:

TERM_DATE == "00000000"

In a date field what you see on your screen is based on your computer's regional settings but in the database IDEA stores the information as YYYYMMDD so if you are doing a search for a particular date or using dates in your analysis then you always want the date to be in the YYYYMMDD format.

Chocobo88 Mon, 08/15/2022 - 16:32

Thanks for your help. It didn't quite work, but it put me on the right path. It ended up returning 00/00/0000 dates. I worked on it a bit, and wrote TERM_DATE <> "00000000", which returned the dates I needed. Thanks again.