Better Business Through Data Analysis & Monitoring My Account

Importing Date Fields

Tech Tip to Steps for Dealing with Date Fields

A common problem with importing any file into IDEA is dealing with date fields.  The import assistant reads the field and does its best to determine what kind of data is being imported and what type of field to create.  If there is anything in the field that doesn’t look like a date, it will either create a Numeric or Character field.  Fortunately, there are ways to either correct this at the time of import or in the database after the file has been imported.

Correcting the Date Field When the File is Imported

Certain types of imports allow users to specify field types during the import process. For example, when you import a text file, the field is created and the data type is assigned. In the example below, the Import Assistant wanted to import this date field as a Character field.

Import Assistant - Field Details Dialog Box

To correct that, select the field; then use the drop down next to “Type:” to change it to a Date field. When the type is changed to Date, you will be prompted to enter a date mask.

Import Assistant - Field Details Date Mask Dialog Box

In this case, the mask “YYYYMMDD” was used because it matches the format of the data in the file.

Report Reader imports also allow you to change the field type during the import process. When importing the same file as above, Report Reader identified that field as a Numeric Field.

Screenshot of Where to Edit Field Type Info in Report Reader

To change the type to a date field, select the field and use the drop down next to “Type” to change it to a Date field. As before, you will be prompted to enter a matching mask for the field. Update the mask and save the layer.

It’s important to note that some import types do not allow you to specify the field type. Most commonly, Excel imports read the data in the field and make a best guess as to what it is that you are trying to import. Even if the cell is listed as Date in Excel, the Import Assistant may read the incoming data as a Character or Numeric field. In those cases, it’s usually best to go ahead and import the file and change the field type in the resulting database.

Changing the Field Type in IDEA

If you’ve already imported the file and find that your date field is not using the Date field type, you can still make that correction in IDEA. First, click the Data tab then click Modify.

Modify Field Function on Data Tab

The Modify Field Window will open. Use the drop down next to “Field type:” to change the field to Date. Enter the appropriate date mask in the Parameter field.

Dialog Box for Modify Field

Then click the OK button to make the update

In all cases, the data must be formatted consistently to use the method of changing the data type to date. For example, in some files, dates can be listed as 1/15/2017 or 1/4/2017 or 10/15/2017. The “mm/dd/yyyy” format is not consistent. In this situation, the field should be imported as a character field and reformatted inside IDEA using @functions.

Reformatting Dates in IDEA

Date masks have been mentioned multiple times in this article. It’s very important to note that the date mask you enter must exactly match the format of all the dates in your field for the update to work correctly. If you update the field and you see red errors listed instead of a date, the most likely cause is that the mask doesn’t match what is in the field.

Date Field Errors Due to Field Mask Not Matching Exactly

The mask will need to be updated. Make sure to include any slashes, dashes, spaces or any other special characters in your date.

It’s not uncommon for a month to be listed as an abbreviation instead of a number (JAN for January, MAR for March, etc…). In these cases, you can use three M’s for the month. For example, the mask for 17 JUL 2016 would be “DD MMM YYYY”. In this example, IDEA is smart enough to know that JUL is July.

When dates are not formatted consistently in the same file, a single date mask will not work for all of the dates. For example, the date mask for 10/20/2015 is “MM/DD/YYYY”. This same mask won’t work for a record with a date of 3/10/2015. The problem is that the second date only has a single digit for the month instead of two indicated by the mask. If you had both of these dates in a field, that mask would work on the first one, but would return an error on the second. To format dates in this case, the best thing to do is append a new Date field and write an equation to convert the date. The following equation would test the length of the first and second segments of the date and make updates as necessary.

@Ctod(@if(@Len(@Split(@Alltrim(DATE),"","/",1))=1,"0"+@Split(@Alltrim(DATE),"","/",1),@Split(@Alltrim(DATE),"","/",
1))+"/"+@if(@Len(@Split(@Alltrim(DATE),"","/",2))=1,"0"+@Split(@Alltrim(DATE),"","/",2),@Split(@Alltrim(DATE),"","/",
2))+"/"+@Split(@Alltrim(DATE),"","/",3),"MM/DD/YYYY")

Note that this equation will work in this instance, but may need to be edited to account for variations in your date fields.

If you have questions about this or other IDEA functions, please give our help desk a call at:
888-641-2800 Option 4
Or email us at helpdesk@audimation.com

Print

Number of views (2803)/Comments (0)

Brad Newman

Brad Newman

Other posts by Brad Newman
Contact author

Please login or register to post comments.

Name:
Email:
Subject:
Message:
x
The Power of Field Statistics
7 March 2018

The Power of Field Statistics

IDEA Tech Tip

IDEA’s Field Statistics can save you a wealth of time. It should be the first thing you go to after importing a file. Why?

Read More

Using Data Analytics to Prepare for Fieldwork
20 February 2018

Using Data Analytics to Prepare for Fieldwork

Automating High-Risk Transactions with IDEA

See how a global corporation used IDEA to develop an automated process to prepare auditors for on-site work.

Read More

People. Processes. Tools.
5 February 2018

People. Processes. Tools.

Successful Integration of Data Analytics

Expert IDEA users share their experiences and advice for building and sustaining a successful data analytics program.

Read More

Planning & Scoping Your Audit with Data Visualization
1 February 2018

Planning & Scoping Your Audit with Data Visualization

Data-Driven Insights Save Hours of Effort

Here are some ways IDEA can help you plan and scope an audit using the data visualization features within IDEA.

Read More

Classroom Collaboration at University of Missouri - St. Louis
30 January 2018

Classroom Collaboration at University of Missouri - St. Louis

Student Learn to Develop Analytic Tests

Masters students attending the University of Missouri - St. Louis share their results using data analytics to assess risk, test 100% of the data population.

Read More

Do One Thing Every Day that Scares You
30 January 2018

Do One Thing Every Day that Scares You

Virginia Tech Students Gain Confidence Using Data Analytics

Learn how Associate Professor of Practice Nadia Rogers, CPA, introduced students to IDEA, including types of audit tests that can be performed within the software and various documentation aspects of an audit engagement.

Read More

New Features in IDEA 10.3
29 January 2018

New Features in IDEA 10.3

IDEA now runs faster and offers more flexibility than ever before

The latest version of IDEA introduced a host of new features and improvements.  IDEA now runs faster and offers more flexibility than ever before.

Read More

5 Ways Data Analytics Saves Time
26 January 2018

5 Ways Data Analytics Saves Time

Turn Hours of Work into Minutes

5 time-saving ways data analytics can help you shrink your audit time, while improving the quality of your work.

Read More

Tired of Wrangling Cumbersome Client Data?
24 January 2018

Tired of Wrangling Cumbersome Client Data?

10 Ways Our Importing Services Make it Easy

10 reasons to try our data importing services, which save you time, effort and undue frustration. 

Read More

A Journey in Using Data Analytics
24 January 2018

A Journey in Using Data Analytics

Testimonial from Brian Cullum

Read how Brian Cullum used IDEA to help identify a multi-million-dollar fraud based on the overstatement of the borrower’s lending base.

Read More

Planning & Scoping Your Audit
15 January 2018

Planning & Scoping Your Audit

With Data Visualization

Data visualization is changing the way auditors are communicating with their audiences. Easy planning, find risk areas and prioritize your scope of work.

Read More

Infusing IDEA into Your Organization
16 November 2017

Infusing IDEA into Your Organization

Building a successful and sustainable data analytics program requires a mix of people, processes and products...it all starts with collaboration.

Read More

Using Nested @Functions()
16 November 2017

Using Nested @Functions()

IDEA Tech Tip

Combining @functions in creative ways can open a wealth of new capabilities that you may not have known your IDEA software is capable of.

Read More

IDEA 10.3 Now Available
15 November 2017

IDEA 10.3 Now Available

CaseWare Analytics Launches Update

Newest release of CaseWare IDEA introduces key features and enhancements to help auditors, accounts and other finance professionals improve their audits.

Read More

RSS