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
Beyond Financials: Five Great Uses for Data Analytics in Manufacturing
May 20 At times, any manufacturing enterprise can feel like a web of mysteries and riddles. Just keeping track of all the moving parts—from supplier relationship...
Take a Lean Six Sigma Approach Using Data Analytics
Oct 16 Henry Ford once said that if he had asked people what they wanted, they would have said faster horses. Constant innovation keeps businesses thriving. Regar...
Tech Tip: Fuzzy Join
Sep 24 CaseWare IDEA 11.1 provides a robust plugin to perform a fuzzy join between IDEA databases, searching for any overlap or possible duplicates between the two dat...

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