Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Todd F.
 
Posts: n/a
Default 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


  #2   Report Post  
Biff
 
Posts: n/a
Default

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




  #3   Report Post  
Todd F.
 
Posts: n/a
Default

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





  #4   Report Post  
Todd F.
 
Posts: n/a
Default

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





  #5   Report Post  
Todd F.
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
time interval calculations in excel Krishna Excel Discussion (Misc queries) 6 April 8th 05 02:57 PM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 1 December 1st 04 06:55 AM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 1 December 1st 04 04:52 AM
split combined Time Date cells Mark Ada Excel Discussion (Misc queries) 2 December 1st 04 04:06 AM
Date and Time Macro m.j.anderson Excel Discussion (Misc queries) 1 December 1st 04 01:35 AM


All times are GMT +1. The time now is 12:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"