Audimation Services has been acquired by Caseware International Learn More.


Blog Image

Importing Date Fields

There are times when you import a date field and it just does not work out as expected. Here are ways to address the problem before or after the import.
REPORT READER import requires a MASK when you classify a field as a date. If the data shifts slightly, you need to configure the field as character in order to import. If the date is formatted as MM/DD/YYYY (2-digit month and day) it is easy enough once imported to MODIFY FIELD converting it from character to date by supplying the correct “mask”.
However – there are times that it varies, mixed 1 and 2 digits for month and day.

Check out the CaseWare Passport for Custom Functions for IDEA.

Convert Date – DMYYYY to IDEA Date Format
Converts a string in D/M/YYYY or D-M-YYYY format to the IDEA date format (YYYYMMDD).
D can be one or two digits (1 or 01). M can be one, two or three characters or digits (01, 1, Jul). Any date prior to 19000101 is invalid.

Download the custom function file: DMYYYYToIdea.ideafunc and save to the CUSTOM FUNCTIONS.ILB folder of your current project. Equation to APPEND a new DATE field would be:


EXCEL imports can cause a different problem. IDEA scans the data in each column for a fixed number of records to determine the data type. If you forget to check FIRST ROW IS FIELD NAMES, all columns are imported as CHARACTER. If you fail to check IMPORT EMPTY NUMERIC CELLS AS 0, columns with blank rows are imported as CHARACTER. In addition, for some reason, the mask for the imported dates is DD/MM/YYYY if you choose to MODIFY FIELD to convert from character to date by supplying the correct “mask.”

Import Assistant - Import Options for an Excel Spreadsheet

Want to avoid the need to append or modify a field? Simply check both options prior to import. Both options are automatically checked starting with IDEA 10.4.

Import Assistant - Option to Import Empty Cells as 0

Both columns imported as date fields!

View of Update Date Fields in IDEA

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 [email protected]

CaseWare IDEA , Tech Tip

Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.

Related Posts
Computer Security
Jan 01 An area where IDEA has been used with particular success is in conducting security audits. Normally on a security audit, the various controls over access are ev...
Overcoming Multiple Systems Disorder with Data Analytics
Nov 11 Analysts estimate the world’s data will grow 50 fold in the next five years. Federal government agencies already produce and house massive amounts of data...
Oct 27 Utilize these plugins to enhance and/or improve the automation of your workflow.   Word Find   This plugin creates a summary of all the words from...

This website has been designed for modern browsers. Please update. Update my browser now