XLSX Date Field Import Problem

[Bert Leithold]

Brendon,

First, congrats on a stellar set of apps. I purchased the Mac, iPad, and iPhone versions earlier this afternoon and have Dropbox syncing going between them. After playing around for a while, I have just started importing some of my old Excel Spreadsheets into Tap Forms, and am running into a problem importing a .XLSX type file.

On my Mac, I point the import records dialog to the file. I set the Date Format in the dialog to “Short” (I’ve tried others with same result). I then change the Form field where I have the date field in the spreadsheet to a “Date” format within Tap Forms. While all other rows and columns import fine, the Tap Form date field in every record is always blank upon conversion. An example:
— Within Excel, I have a cell formatted as a date with “03/14/01” (mm/dd/yy).
— In the Tap Forms import records dialog, the field shows as “33383”, even when the the field has a date format of “MM/dd/yy – 06/14/2015” if I’ve predefined the form. (All dates as I scan them in the Tap Forms dialog appear with these sort of large numbers, not as a recognizable date.)
— Once I import the records into the new Form, 100% of the date fields from this spreadsheet column are blank within the new Form.

I suspect Excel is storing the date as some sort of number offset from a starting date (like 1/1/1900?) which Tap Forms isn’t handling correctly upon import of an XLSX file. You would know better. Can you suggest what I may be doing wrong or a workaround? The only thing I can think of trying is to perhaps first convert each Excel Spreadsheet to a CSV, then importing that into Tap Forms — but I’ll wait for your input before proceeding down that longer path.

Thanks again for some great apps, and sorry to bug out of the gate. I’ve read everything online and in all 3 help systems I can, but am at a loss if I’m maybe missing something. I look forward to continued enhancements and a lot of use of your wonderful apps, and am amazed at what you’ve accomplished so far! Bert

[Brendan]

Hi Bert,

I’m sorry about this problem with Excel dates. You are correct that they are an offset from a starting date. And you’re right that Tap Forms is not handling them properly. Is there any way you can convert the dates into Text format in Excel before you export the template? So that the dates are in a human readable format instead of those large numbers? That may help. Otherwise, the CSV option will do it too.

It’s something I need to add support for still.

Thanks,

Brendan

[Bert Leithold]

Thanks for getting back to me.

I’ll proceed with a multi-step conversion approach and ditch using XLSX directly. Bert

[Author]

Posts