Excel Tips and Tricks

Owned by Gillian Javetski
Last updated: Mar 21, 2024 by Krinda Wernicke

Time Calculations from form/case export

This is particularly useful for calculating the length of time it took to complete a form.  The form submission export contains the "timeEND" and "timeSTART" values, but not in a format that's easy to use for calculations. To change the format of those two values use this excel formula:

For the time (hh:mm:ss) portion of the data, use this: 

=IF(ISERROR(VALUE(LEFT(cell_reference,10))),0,VALUE(MID(cell_reference,12,8)))

For the date (mm/dd/yyyy) portion of the data, use this: 

=IFERROR(VALUE(LEFT(cell_reference,10)),"-")

  • Example: =IFERROR(VALUE(LEFT(a2,10)),"-") 

If you create an additional column next to the "form end time" column, you can put this formula into the first cell, and then copy it down the entire range. 

Serialized Dates between Mac and Windows

Excel stores dates as sequential numbers that are called serial values (days since a certain starting date). Note that there are two different starting dates that might be used, one for Windows (Jan 1st, 1900) and one for Mac (Jan 2nd, 1904). You may experience difficulty if you are uploading dates between these Operating Systems. 

For more information about changing between the date systems and correcting shifted dates, see http://support.microsoft.com/kb/180162.

Hints for Working with CSV

Keeping All Digits/Avoiding Rounding Errors

The maximum number of digits you can have for a number in Excel is 15. When you convert csv to xls if you are importing a number that is greater than 15 digits Excel will round the number 15 digits. In order to work around this, you have to tell Excel to mark the bank account column as text instead. You can do this in the Text Import Wizard by setting the Column Data Format to Text.

Support for Unicode 

There are known issues for Excel support of Unicode characters. If you are finding that Excel replaces your text with strange characters make sure you are following the directions here: https://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0.