Date & Time Cells to Be Splitted

**Reyan123** · 03-05-2009, 02:54 AM

Hi All !

Can anybody helps me to split date/time into two cells. For example,

In Cell A1 the value is "03/03/2009 13:56:00". I want to split date in cell B2 & Time in cell C2.

Currently this value in A1 is showing the format general. I changed it to date/time or even in time function but I do not know any formula currently to split this as per my requirement.

Needs urgent help.

**JBeaucaire** · 03-05-2009, 03:32 AM

I know these two formulas work, but we see a lot of instances when people say their data is one thing, but it's not. So, if the value in cell A2 is REALLY a complete set of date and time stamp data, the formula to extract each piece is:

Date: =INT(A2) (format the cell as Date: preferred format)
Time: =A2-INT(A2) (format the cell as Time: 13:30)

**JBeaucaire** · 03-05-2009, 03:40 AM

If the data in cell A2 is actually NOT a time stamp but "text" with the exact appearance you noted

"03/03/2009 13:56:00"

...quotes and all, then these two alternate formula will still pull the text out and convert it to actual date/time info.

B2 =DATEVALUE(MID(A3,2,10)) (format the cell as Date: preferred format)
C2 =TIMEVALUE(LEFT(RIGHT(A3,LEN(A3)-FIND(":",A3)+3),8)) (format the cell as Time: 13:30)

**Reyan123** · 03-05-2009, 04:21 AM

Thanks !

Problem solved

**JBeaucaire** · 03-05-2009, 04:35 AM

Which set of formula ended up being your solution?

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]