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
No Image
Jul 20 The acquisition of client data, more than any other single factor, will determine the success or failure the audit. As custodians of system data, the IT departm...
Tech Tip: Other Ways to Get IDEAScripts
Sep 18 The ability to automate a process can be very helpful in making your job easier. In IDEA, this means creating macros or IDEAScripts that automatically perform m...
The Next Frontier in Data Analytics
Aug 17 by: Norbert Tschakert, CPA/CFF/CITP, Ph.D.; Julia Kokina, CPA, Ph.D.; Stephen Kozlowski, CPA, CGMA, Ph.D.; and Miklos Vasarhelyi, Ph.D. As technology continu...

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