Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
time interval calculations in excel | Excel Discussion (Misc queries) | |||
Split combined date time data | Excel Discussion (Misc queries) | |||
Split combined date time data | Excel Discussion (Misc queries) | |||
split combined Time Date cells | Excel Discussion (Misc queries) | |||
Date and Time Macro | Excel Discussion (Misc queries) |