Import time format H:mm:ss

[thegreek]

Hi people

I am trying to import some records that have H:mm:ss format.When I import them i get the following format 02/01/1900 02:00:00 which is not usable in my case.I import the file from excel.

Can you guys help me out with this?

[LaRetta]

It sounds like your field is set up as a timestamp instead of time. Even if you imported a timestamp into a time field, all you would get is the time. If it isn't your field itself, can you provide an example of the excel sheet? :^)

Also what is your OS and FM version? You might want to also update your profile with this info since it helps us provide better input.

[thegreek]

I get this format 02/01/1900 02:00:00 in the import dialog in Filemaker.I also i tried to troubleshoot it in excel but no luck.I use it to monitor aircraft hours(most of them are way more that 24:00 ,usually around 1000:00).The field in filemaker is a time field 100%.And in Excel the fields are showing properly.The weird thing is in excel when you click on a field, in the formula bar you get the timestamp format 02/01/1900 02:00:00.

This is driving me crazy because I have to import a lot of records from excel.

I am using 10.7 OS and filemaker 11

[LaRetta]

Try the import anyway on a backup copy. It will only import the time portion of it. Check that the time is correct after import. If this is the same OS setting that your file was created under then it should import fine.

[comment]

in Excel the fields are showing properly.The weird thing is in excel when you click on a field, in the formula bar you get the timestamp format 02/01/1900 02:00:00.

That is because Excel understands times and dates differently than Filemaker. In Excel, 26 hours is recorded as 1.083 days. Then it's only a matter of formatting whether this is displayed as 26:00:00, 2:00:00 AM, or 02/01/1900 02:00:00 (Excel's first day being Jan 1, 1900 or 1904, depending on the settings).

Filemaker, OTOH, calculates time as the number of seconds since midnight, while dates start from Jan 1, 0001.

If you cannot adjust the source spreadsheet, you will need to post-process the imported data. First thing, try to import into a timestamp field and see if the data is interpreted as a valid timestamp.

[LaRetta]

I knew Excel was not the best import source but I did not know about this. Thank you for catching my incorrect response!

[thegreek]

It's not working i still get odd numbers.I went throughout the import but no luck.Any other recommendations?

That is because Excel understands times and dates differently than Filemaker. In Excel, 26 hours is recorded as 1.083 days. Then it's only a matter of formatting whether this is displayed as 26:00:00, 2:00:00 AM, or 02/01/1900 02:00:00 (Excel's first day being Jan 1, 1900 or 1904, depending on the settings).

Filemaker, OTOH, calculates time as the number of seconds since midnight, while dates start from Jan 1, 0001.

If you cannot adjust the source spreadsheet, you will need to post-process the imported data. First thing, try to import into a timestamp field and see if the data is interpreted as a valid timestamp.

How to process them to get correct values?

[comment]

It's not working i still get odd numbers.

Can you be more specific? Perhaps even post a sample spreadsheet?

[thegreek]

this the file I am trying to import.

test.zip

[comment]

Not sure which column we are supposed to pay attention to. What I do see is that different rows of the same column have different formatting. it will be very difficult to have Filemaker process this automatically.

[thegreek]

We try to import column j or k.These are hours in format [h]:mm.I can not figure out the calculation.I am thinking to subtract the excel timestamp value as a number.01/01/1900.

1900x365x24x3600 but i get odd results.

I find this very strange that yet the developers of Filemaker haven't figured out how to import timestamps from excel,and have them shown correctly.

[comment]

See if this works for you:

Import.zip

[thegreek]

Almost there .It's like 48 hours sort from what I can see.

[thegreek]

Case ( f10 ; (GetAsTimestamp ( f10 ) + Time ( 48;0;0 )) - Timestamp ( Date ( 1; 1 ; 1900 ) ; 0 ) )

f10 will be the field with the excel timestamp.

You put this code as a calculation to the field you want.

Thanks a lot for the solution

[comment]

I think you could simplify that to:

Case ( f10 ; GetAsTimestamp ( f10 ) - Timestamp ( Date ( 12 ; 30 ; 1899 ) ; 0 ) )

Though I am still puzzled how your data providers managed to produce such result.

[thegreek]

Yes it's strange,but this is what i have to work with..

I have one more question.Can you think a logical function to keep the date field as is(in column k).I am thinking a logical function :if field is date format and not timestamp, copy that date to another field .Is this possible?I can not find the right function.

[comment]

I am not sure I follow. The field that you import to is a Text field. I see that some of the imported values look like timestamps, some look like times, some are numbers, a few are text and some appear to be dates in the current century. That's what I had in mind when I said "it will be very difficult to have Filemaker process this automatically".

Perhaps something like the attached could help? Note that this is already very close to GIGO. IMHO, the true solution lies in cleaning up the supply path.

Import2.zip