convert interval to various separate date , time, hr, minutes
 

I have a interval ( date & time combined) coming out of imrpomtu via a save
to excel format as follows

In Impromptu it is in the follwoing format:

000 00:00:00.000
days hr, min, seconds, milla seconds


In excel I am getting the follwing in my columns:

10/25/05 13:50 ( visually in cell)

The format is custom m/d/yy h:mm

I need to get clean grabs of just the time in hours and minutes and just the
date

I can get the date out in text string so date is secondary in concern.

I need to do a great deal of time calcs , filters, and pivots - all on time

I also need to calc betwen dates with ands with out time.

Any suggestions on how to get this cleaned up into the best usable format
would be great - I do not mind extra fields.

thanks todd

Hi!

You can get the numeric date by using:

=INT(A1)

Format as DATE

To get the time and strip off the seconds and decimal seconds:

=TIME(HOUR(A1),MINUTE(A1),0)

Format as TIME 13:30

Biff

"Todd F." wrote in message
...
I have a interval ( date & time combined) coming out of imrpomtu via a save
to excel format as follows

In Impromptu it is in the follwoing format:

000 00:00:00.000
days hr, min, seconds, milla seconds


In excel I am getting the follwing in my columns:

10/25/05 13:50 ( visually in cell)

The format is custom m/d/yy h:mm

I need to get clean grabs of just the time in hours and minutes and just
the
date

I can get the date out in text string so date is secondary in concern.

I need to do a great deal of time calcs , filters, and pivots - all on
time

I also need to calc betwen dates with ands with out time.

Any suggestions on how to get this cleaned up into the best usable format
would be great - I do not mind extra fields.

thanks todd

thanks

"Biff" wrote:

Hi!

You can get the numeric date by using:

=INT(A1)

Format as DATE

To get the time and strip off the seconds and decimal seconds:

=TIME(HOUR(A1),MINUTE(A1),0)

Format as TIME 13:30

Biff

"Todd F." wrote in message
...
I have a interval ( date & time combined) coming out of imrpomtu via a save
to excel format as follows

In Impromptu it is in the follwoing format:

000 00:00:00.000
days hr, min, seconds, milla seconds


In excel I am getting the follwing in my columns:

10/25/05 13:50 ( visually in cell)

The format is custom m/d/yy h:mm

I need to get clean grabs of just the time in hours and minutes and just
the
date

I can get the date out in text string so date is secondary in concern.

I need to do a great deal of time calcs , filters, and pivots - all on
time

I also need to calc betwen dates with ands with out time.

Any suggestions on how to get this cleaned up into the best usable format
would be great - I do not mind extra fields.

thanks todd

I need to get a clean whole number day when subtracting close interval from
create interval

if I simply subtract the two fields I get a gooffy time format that I cannot
clean up to give me a solid day count numnber so then can do another calc on

thanks

"Biff" wrote:

Hi!

You can get the numeric date by using:

=INT(A1)

Format as DATE

To get the time and strip off the seconds and decimal seconds:

=TIME(HOUR(A1),MINUTE(A1),0)

Format as TIME 13:30

Biff

"Todd F." wrote in message
...
I have a interval ( date & time combined) coming out of imrpomtu via a save
to excel format as follows

In Impromptu it is in the follwoing format:

000 00:00:00.000
days hr, min, seconds, milla seconds


In excel I am getting the follwing in my columns:

10/25/05 13:50 ( visually in cell)

The format is custom m/d/yy h:mm

I need to get clean grabs of just the time in hours and minutes and just
the
date

I can get the date out in text string so date is secondary in concern.

I need to do a great deal of time calcs , filters, and pivots - all on
time

I also need to calc betwen dates with ands with out time.

Any suggestions on how to get this cleaned up into the best usable format
would be great - I do not mind extra fields.

thanks todd

the integer does not give me a clean number to do calcs off of - it is still
a wierd date format even after using your INT it shows upas date and if you
change to number it goes crazy

??????

"Biff" wrote:

Hi!

You can get the numeric date by using:

=INT(A1)

Format as DATE

To get the time and strip off the seconds and decimal seconds:

=TIME(HOUR(A1),MINUTE(A1),0)

Format as TIME 13:30

Biff

"Todd F." wrote in message
...
I have a interval ( date & time combined) coming out of imrpomtu via a save
to excel format as follows

In Impromptu it is in the follwoing format:

000 00:00:00.000
days hr, min, seconds, milla seconds


In excel I am getting the follwing in my columns:

10/25/05 13:50 ( visually in cell)

The format is custom m/d/yy h:mm

I need to get clean grabs of just the time in hours and minutes and just
the
date

I can get the date out in text string so date is secondary in concern.

I need to do a great deal of time calcs , filters, and pivots - all on
time

I also need to calc betwen dates with ands with out time.

Any suggestions on how to get this cleaned up into the best usable format
would be great - I do not mind extra fields.

thanks todd