convert date and time text to 2 cells

[Jbooze]

I have an import that includes the date and time as one string 2013/04/16/00. The would be the date of 4/16/2013 and the time of 0:00. I can pull the date out with:

=DATE(LEFT('Chat Split Skill Summary'!B5,4), MID('Chat Split Skill Summary'!B5,6,2), MID('Chat Split Skill Summary'!B5,9,2))

I need to pull the two digit time out and format it as 00:00. I tried =HOUR(RIGHT('Chat Split Skill Summary'!B5,2)) but it does not recognize the the two digits as the hour. It seems to think it is the minutes. When I drag it down one field to get 01, I still get a value of 0:00.
Can someone get this to recognize the hour?

Thanks,

Jeff

 

[AlphaFrog]

The HOUR function returns the hours only from a time value. It doesn't make a time value.

Try this...
=TIME(RIGHT('Chat Split Skill Summary'!B5,2),0,0)

 

[taprico]

Sir

I have a similar problem - I get date and time in a cell that updates from net - the format is YYYYMMDD.HHMMSS

I want the date and time split in two cells - MM/DD/YYYY and HH:MM:SS

I have been searching the site an googling it but no success - I have tried many functions but failed.

Have tried Text to Column function but as there are decimals in other cells so it dosent work. Then in others the '0' disappears from the time value.

Kindly help,

A 20130919.091618 <tbody> </tbody>	B 6100 <tbody> </tbody>	C 82750 <tbody> </tbody>
20130919.091619 <tbody> </tbody>	6100.1 <tbody> </tbody>	64200 <tbody> </tbody>
20130919.091626 <tbody> </tbody>	6100.05 <tbody> </tbody>	139700 <tbody> </tbody>

<tbody>
</tbody>

Thanks

 

[AlphaFrog]

Format the cells in columns B and C with any Date and Time formats you like.

<br />

Book1
	A	B	C
1	20130919.091618	9/19/2013	9:16:18
2	20130919.091619	9/19/2013	9:16:19
3	20130919.091626	9/19/2013	9:16:26
Sheet1

Cell Formulas
Range	Formula
B1	=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))
C1	=TIME(MID(A1,10,2),MID(A1,12,2),MID(A1,14,2))

 

[taprico]

Wow thats perfect ALPHAFROG - thanks man I have been breaking my head over this for quite a few days now.

Thanks man