Date & time field: Importing from excel

[DLM]

Hi guys -- I'm using FM 5.5v1, I want to import an excel file which has a column with many rows that have the date & time together, as follows:

 

If you just look at the excel column it looks like this:

 

5/11/2013  10:25

5/11/2013  18:19

5/11/2013  20:33

etc.

 

But if you click on each individual cell it looks like this:

 

5/11/2013  10:25:41 AM

5/11/2013  6:19:12 PM

5/11/2013  8:33:05 PM

etc.

 

Ideally, I would like to have it in FM under the 24 hour format as in the former but with the seconds included as in the latter, like this:

 

5/11/2013  10:25:41

5/11/2013  18:19:12

5/11/2013  20:33:05

etc.

 

The problem is that when I import it, FM completely deletes the time and just leaves the date, I tried setting the field to Text, Date and Time but same thing.

 

Any solution?

 

Best!

P.S. If it's not possible to do as I wish, how could I split the date & time so as to have them imported into two different fields?

[Lee Smith]

Are you really using v5? I can not use my v6, so I don't know what is there anymore for functions.

 

Does the Timestamp Function even exist in 5? If so,  just change the field to Timestamp, if not  Do GetAsDate and GetAsTime?

 

If so, then I'll call your existing field yourTSField, 

 

If these exist, GetAsDate ( yourTSField ) Result = date  and GetAsTime ( yourTSField ) should do it. Result = time

 

If not, maybe something like.

 

Date ( Month ( yourTSField) ; Day ( yourTSField ) ; Year ( yourTSField ) ) Result = date

Time ( Hour ( yourTSField ) ; Minute ( yourTSField ) ; Seconds ( yourTSField ) ) & " "  &  Right ( yourTSField ; 2 ) Result = text

 

HTH

 

Lee

Edited July 29, 2013 by Lee Smith
left a ( out of the Date calc.

[DLM]

Hi Lee!

 

Yes, I'm really using 5.5v1 (call me a freak). 

 

There's no "Timestamp" field type (just Text, Number, Date, Time, Container, Calculation, Summary and Global) nor function.

 

There's no "GetAsDate" nor "GetAsTime" functions either.

 

:-(

 

Any other options/ideas?

[Lee Smith]

Them try the two calculations I provided and let me know. Each will have to be their own field.

 

I wrote the calculations in v11, and they need to be altered to work in v5 by replacing the ; with ,  i.e. the semicolons with the commas. 

 

Date ( Month ( yourTSField) , Day ( yourTSField ) , Year ( yourTSField ) )

 

Time ( Hour ( yourTSField ) , Minute ( yourTSField ) , Seconds ( yourTSField ) )

 

Lee

Edited July 29, 2013 by Lee Smith
revised the two calc for v5

[DLM]

Hi Lee -- The problem is, as I explained on my original post, that when I import the data from excel FM completely deletes the time part and just leaves the date, so if on the excel formula bar it shows:

 

5/11/2013  10:25:41 AM

 

Then FM will only import this:

 

5/11/2013

 

I tried setting the field type to Text, Date and Time but same thing.

[doughemi]

You may have to export the excel to a csv format text file first.  Try it and see if the csv has the timestamp in mm/dd/yy hh:mm:ss format. If it does then import that  into a text field, and parse the text, using Position() on the slashes and colons to parse out the numbers.  

[Lee Smith]

import into a regular text field and then use these calc work on it, and let me knows

[DLM]

Hi doughemi -- The csv file does contain the timestamp but the problem is FM won't import csv (the file simply appears greyed out on the import window), that's why I saved the csv to xls... FM does import xls but when the import is done FM completely deletes the time part and just leaves the date, so if you would look at the excel formula bar it would show:

 

5/11/2013  10:25:41 AM

 

But FM will only import this:

 

5/11/2013

 

I tried setting the field type to Text, Date and Time but same thing.

[Lee Smith]

I'm having a hard time understanding what is happening, by chance is there a character being added by excel between the date and the time?

 

Can you send me, or attach a copy of the Raw data. 

 

One more thing, is this file a Tab, CSV, delimited file, or some other export file format.

 

Lee