number to time

[Charity]

I have excel file with time listed as 08:00:00 AM

 

when I import into time field I get .33333333333333331483 and it is set as a time field and there is no auto-enter on it.  I need to understand how to convert this number to the proper time.  Thank you for taking a look at my script need.

[comment]

Excel keeps time as the (decimal) fraction of a day, so that .33333333333333331483 is approximately 1/3 of 24 hours, that is 8 hours. Filemaker keeps time as the number of seconds, so just multiply the Excel value by 86400 and round the result to the nearest integer to (hopefully) offset any rounding errors accumulated in the process.

[Charity]

I did this

 

Round ( Self * 86400  ; 0 )

 

but it gave me same so I used 1 which did not make sense but it gave me

 

8:00:00

 

but it did not give me the am or pm and I need that.  So I set the display to include AM PM and it looks right, saying it is 8:00:00 AM.  How can I verify that I did it right except to ask you?  So when you say integer, google says it is whole number but what is a whole number I thought it should be round 0 for no decimals and not round 1.

 

So now I am unsure.  How can I be sure?  And thank you for listening.  Also when I did Round 0, it displayed midnight but had the AM so it was the opposite of midnight.  What is the morning midnight called in database terms?

[Charity]

I think it has to do with what you said about rounding errors accumulated and I should just trust if it looks right but I need to make sure if possible.

[Charity]

Never mind yet.  It was showing 8:00 AM when I first changed the display to AM but now it only shows 12:00:00 AM so I somehow broke it.  I will keep working on what I did wrong.  I mean after I tried importing again.

[Charity]

first time it was unchecked auto-enter to replace the value and that worked but I had to check it because people will be typing into that field and when unchecked and me type 8:00 AM, it gave incorrect long number so I had to check it.  I guess I will need another field to hold the answer.

 

Twilight.  Opposite of midnight.  I think.

 

I figured how to verify it was right by adding 60 in another calculation and it gave me 8:01:00 so I am pretty sure it works fine as we expected.

Edited October 25, 2014 by Charity

[comment]

1.

Round ( number ; 0 )

is the correct way to round to the nearest integer.

 

 

2. If you cannot set a field to auto-enter, then script the import and post-process the imported records as part of the importing script.

Although, you could set the auto-enter to only correct entries that are < 1.

 

 

3. AM/PM (12-hour clock) is a matter of display only. Not sure what this has to do with anything, but I believe the "opposite" of midnight (12 AM) would be noon (12 PM)?

[Charity]

1. thank goodness.  I felt confused when it did not work

2. I changed the time field to auto-enter unchecking so it replaces the value and used this

 

If ( Self < 1 ; Round ( Self * 86400 ; 0 ) ; Self )

 

and it works and it lets me enter regular times also. That is very clever Comment.

 

3. I had googled and it said opposite of midnight was noon. I didn't mean that but I realize I was being an idiot. I meant that I think of noon as 12:00AM because switching to the afternoon, the next number is 1:00PM. 12:00PM then to 1:00PM makes no sequential sense. I think of midnight as being 12:00PM because it seems to make more sense in the sequence of numbers when I view them.  Did that make sense?

 

So I was thinking 12:00PM was midnight and was asking what 12:00AM was, meaning when the time is all zeros and not 24:00:00 and all I could think to call it was twilight. It just seems all twisted how 12:00AM and 12:00PM are backwards but I think I am the one twisted.

 

I think I also know why it still worked when using round 1.  It is because of the extra set of :00 lower than seconds and FM does not see it anyway like Sequal does so it didn't make a difference in even a second to the FM calculation.  I think.  Thank you again for the perfect solution.

[comment]

why it still worked when using round 1.

 

It worked because the rounding error (in the case you tested) is very small, so even rounding to nearest tenth of second would have corrected it.

 

 

So I was thinking 12:00PM was midnight

 

You should read:

http://en.wikipedia.org/wiki/12-hour_clock#Confusion_at_noon_and_midnight

[Charity]

The article helped. Thank goodness I am not the only one who is confused by it.   I was feeling quite stupid  I still think the US post office 1953 had it right.  The chart on that page shows they thought like I did.  I will just have to remember to reverse whenever I think of noon or midnight and look at a time field with AM/PM.  I wish the world would just use military time.  I appreciate you taking the time to provide me that link.

[comment]

I wish the world would just use military time.

 

Most of the world does, at least when writing the time. But then you have the inconsistency of saying "five o'clock" and writing "17:00". And if you removed that, then you would also have to abolish the analog clock face, which would be a pity IMHO. There are many things like that - shaped more by their history than by pure logic.

[Charity]

i guess the world simply isn't exact.  Bummer.  Thank you for explaining.