Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
value time formula help please
A--------------------------B-------------------C
OLD DATA NewDate NewTime 8/19/2008 16:00 PDT 8/19/08 16:00 12/30/2008 0:00 PST 12/30/08 #VALUE! 12/31/2008 3:15 PST 12/31/08 #VALUE! 12/29/2008 23:15 PST 12/29/08 #VALUE! 12/30/2008 8:30 PST 12/30/08 #VALUE! NewDate formula=VALUE(LEFT($A2,FIND(" ",$A2,1))) NewTime formula=VALUE(MID($A2,(FIND(" ",$A2,1))+1,FIND(" ",$A2,1)-4)) Can someone help me with a formula that will return split up (column A --date/time) into two columns where the date is in column B and the time is in column C. The formula I am using is returning a value error for the time. Thank you for your help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
value time formula help please
B1=--SUBSTITUTE(A1,RIGHT(A1,4),"")
C1=TIME(HOUR(B1),MINUTE(B1),SECOND(B1)) "ferde" skrev: A--------------------------B-------------------C OLD DATA NewDate NewTime 8/19/2008 16:00 PDT 8/19/08 16:00 12/30/2008 0:00 PST 12/30/08 #VALUE! 12/31/2008 3:15 PST 12/31/08 #VALUE! 12/29/2008 23:15 PST 12/29/08 #VALUE! 12/30/2008 8:30 PST 12/30/08 #VALUE! NewDate formula=VALUE(LEFT($A2,FIND(" ",$A2,1))) NewTime formula=VALUE(MID($A2,(FIND(" ",$A2,1))+1,FIND(" ",$A2,1)-4)) Can someone help me with a formula that will return split up (column A --date/time) into two columns where the date is in column B and the time is in column C. The formula I am using is returning a value error for the time. Thank you for your help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
value time formula help please
Try these...
For the date: =--LEFT(A2,FIND(" ",A2)) Format as Date For the time: =MOD(LEFT(A2,LEN(A2)-3),1) Format as Time You could even do this without formulas... Select the range in question. Assume it's A1:A10. Make sure B1:B10 is empty to recieve the extracted time. You can enter a new empty column B if you need to. Then: Goto the menu DataText to Columns Select: DelimitedNext Select: SpaceNext In the Data Preview area click the word General above the last coulmn In Column Data Format select: Do not import Click Finish -- Biff Microsoft Excel MVP "ferde" wrote in message ... A--------------------------B-------------------C OLD DATA NewDate NewTime 8/19/2008 16:00 PDT 8/19/08 16:00 12/30/2008 0:00 PST 12/30/08 #VALUE! 12/31/2008 3:15 PST 12/31/08 #VALUE! 12/29/2008 23:15 PST 12/29/08 #VALUE! 12/30/2008 8:30 PST 12/30/08 #VALUE! NewDate formula=VALUE(LEFT($A2,FIND(" ",$A2,1))) NewTime formula=VALUE(MID($A2,(FIND(" ",$A2,1))+1,FIND(" ",$A2,1)-4)) Can someone help me with a formula that will return split up (column A --date/time) into two columns where the date is in column B and the time is in column C. The formula I am using is returning a value error for the time. Thank you for your help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
value time formula help please
In C1:
=TIMEVALUE(MID(A1,FIND(" ",A1)+1,5)) and format as time -- Gary''s Student - gsnu200823 "ferde" wrote: A--------------------------B-------------------C OLD DATA NewDate NewTime 8/19/2008 16:00 PDT 8/19/08 16:00 12/30/2008 0:00 PST 12/30/08 #VALUE! 12/31/2008 3:15 PST 12/31/08 #VALUE! 12/29/2008 23:15 PST 12/29/08 #VALUE! 12/30/2008 8:30 PST 12/30/08 #VALUE! NewDate formula=VALUE(LEFT($A2,FIND(" ",$A2,1))) NewTime formula=VALUE(MID($A2,(FIND(" ",$A2,1))+1,FIND(" ",$A2,1)-4)) Can someone help me with a formula that will return split up (column A --date/time) into two columns where the date is in column B and the time is in column C. The formula I am using is returning a value error for the time. Thank you for your help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
value time formula help please
Thank you for the help.. very much appreciated
"ferde" wrote: A--------------------------B-------------------C OLD DATA NewDate NewTime 8/19/2008 16:00 PDT 8/19/08 16:00 12/30/2008 0:00 PST 12/30/08 #VALUE! 12/31/2008 3:15 PST 12/31/08 #VALUE! 12/29/2008 23:15 PST 12/29/08 #VALUE! 12/30/2008 8:30 PST 12/30/08 #VALUE! NewDate formula=VALUE(LEFT($A2,FIND(" ",$A2,1))) NewTime formula=VALUE(MID($A2,(FIND(" ",$A2,1))+1,FIND(" ",$A2,1)-4)) Can someone help me with a formula that will return split up (column A --date/time) into two columns where the date is in column B and the time is in column C. The formula I am using is returning a value error for the time. Thank you for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formula (difference of predicted and actual time) | Excel Discussion (Misc queries) | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
convert from percentage of time to time using complex formula in . | Excel Worksheet Functions | |||
Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
template or formula for start time -finish time -total hours ple | New Users to Excel |