Time as Number

[LaRetta]

We have changed phone companies. This new company only gives us an Excel spreadsheet with multiple worksheets. It is a mess with multiple rows of header information, detail with headers and blank rows between ... it goes on. I've worked through it all but their Time field is a number as:

130747

211100

Of course this is 24-hour timestamp. Luckily, they have padded minutes and seconds with leading zero. Hour is NOT padded with leading zero. I want it in a time format but nothing I try feels very elegant. I am also concerned because it would break if OS time format is changed (I think). If I change to text, the result in time field (in first example) is:

130747:00:00

Here is my thoughts currently but I can only make this work by using text functions. Any ideas on tricking it into compliance (realizing it's a time field) without having to horse it?

Let (

[

h = Left ( numtime ; Length ( numtime) - 4 ) ;

m = Middle ( numtime ; Length ( h ) + 1 ; 2 ) ;

s = Right ( numtime ; 2 )

] ;

Time ( h ; m ; s )

)

Of course, the Let() isn't necessary but it's helping me keep it straight right now. Don't get me going on what I think of computer people that haven't sense enough to know that the companies probably are importing into normalized systems and they produce junk for export.

Update: I import into text field currently (during this test) but I can change that.

LaRetta

Edited April 8, 2007 by Guest
Changed a bunch

[comment]

What does their version of 0:00:01 look like?

[LaRetta]

The lowest value I have in their data-file is 13619. Reviewing our other phone data (from 3 different carriers), we do have time at 12:01 AM (00:01). I have no answer. I don't believe anyone would care if it was a fraction of a fraction off but I'm unsure that helps you either, Michael.

[LaRetta]

But if it remains consistent (which it appears to), it would be 0001.

[comment]

I think that would be worth checking. Because if it happens to be "1" or "120001" it will break your calc.

Assuming it's "1" or "0001", I would do either:

Let ( [

pad = Right ( "000000" & numtime ; 6 ) ;

h = Left ( pad ; 2 ) ;

m = Middle ( pad ; 3 ; 2 ) ;

s = Right ( pad ; 2 )

] ;

Time ( h ; m ; s )

)

or:

Let ( [

h = Div ( numtime ; 10000 ) ;

m = Div ( Mod ( numtime ; 10000 ) ; 100 ) ;

s = Mod ( numtime ; 100 )

] ;

Time ( h ; m ; s )

)

[LaRetta]

Well it can't be 0001. It is a number in Excel and it drops leading zeros also. In this case, my calc will break as well. I tried calling them but 1) they didn't know; I even talked to two supervisors and 2) they wouldn't let me talk to a computer person. None of the time fields start with zero. I think we're in trouble on this one.

Maybe I'll have to pad the zeros back first. I truly wish I COULD speak with their computer people but it might be better if I don't ... I doubt I'd have very nice things to say. :smirk:

[LaRetta]

I shall work with this! Your second calc is where I wanted to go because it would be matching data type. I think I need more data than one month here; I'll see how I go with it. Thank you very much! :wink2:

[LaRetta]

Number calc is perfect! And I don't think it'll break no matter what! And it is less evaluations as well!

[Raybaudi]

Hi LaRetta

I don't think that that is a timestamp, but a date..

If I remember correctly, Excel first day

starts from 01/01/1900 00:00:00 as 1

and

ends at 01/01/1900 23:59:59.999 as 1.99999999

So, if those numbers haven't any decimals, they are only dates starting from 01/01/1900.

If all this is correct, 13619 is:

04/15/1937 00:00:00

BTW: if those numbers are decimals, then those numbers are 24 hour timestamp and you can try this CF:

/*

ExcelNumberToTime custom function

Author:

Daniele Raybaudi

Format:

ExcelNumberToTime ( ExcelNumber)

Parameter:

ExcelNumber - number - the number representation of time in Excel from 0 to 0.99999999

aka from 00:00:00.000 to 23:59:59.999

Returns the time in correct format for FM

*/

Let([

par = .0000000115740740922925242163;

hours = Int ( excelNumber / 3600000 * par );

minutes = Int ( excelNumber / 60000 * par ) - hours * 60;

seconds = Round ( excelNumber / 1000 * par ; 3 ) - minutes * 60 - hours *3600

];

Time ( hours ; minutes ; seconds )

)

and .13619 is: 3:16:06.816

[LaRetta]

There is already a date field which says DATE as 20070309 and these records are from March, 2007. The field in question is labelled TIME. Besides, you wouldn't catch this gal with THAT kind of mistake; been doin' this (migrating data between programs) for far too long.

Besides ... why would I want to pull telephone records from 1937? I think it would be a good bet that records weren't even stored back then. Uhm, you might want to also check Comment's second calc above. Understanding it can help you improve your numeric calculation (Div/Mod principles) which (from what I can tell) doesn't need to be a Custom Function at all. Straight math can handle it quite easily.

Are you itchin' to overcomplicate and just create another Custom Function again?

LaRetta

[LaRetta]

The Excel date screwiness in which you refer can be referenced right here on FM Forums and doesn't require a Custom Function either.

I keep getting ALL records when I search (so I can't give you the link) but it's Post#206164 by Comment. I've been aware of that weirdness with Excel.

[comment]

:offtopic:

LOL, I didn't know they kept records from 1937. Besides, your first post mentioned "211100" - that would put us somewhere in the year 2477 (or 2481 in Excel's 1904 setting).

The post you refer to deals with Excel dates, but your hunch is correct - Daniele's calculation could be reduced to:

86400 * Mod ( ExcelDate ; 1 )