How to extract hour and minte from cell

[djaenike]

Hello,

I have a spreadsheet that gets sent to me every day. The time values are displayed as follows: "Sat 12:00 AM", "Sat 12:01 AM", etc. I would like to figure out a way to extract just the time (get rid of the "Sat"). I've tried using the conventional time extraction formulas, however it looks like the "Sat" portion throws everything off. Is there a way I can extract just the time?

Thanks!

 

[AhoyNC]

Try (format cells for Time):

Excel Workbook
	A	B
1	Sat 12:00 AM	12:00 AM
2	Sat 12:01 AM	12:01 AM
3	Sun 1:00 pm	1:00 PM
4	Sun 2:30 pm	2:30 PM
Sheet

 

[jtakw]

Hi,

If the data that was sent to you are Real time values, you can just change the formatting, otherwise, if they are Text, you can extract it, here're some samples:

Book1
	A	B	C	D	E
1	Sat 12:01 AM	A1 formatted ddd hh:mm AM/PM
2	12:01 AM	A2 formatted hh:mm AM/PM
3	Sat 12:01 AM	A3 formatted General/Text		12:01 AM	D3 formatted as Time
Sheet3

Cell Formulas
Range	Formula
D3	=MID(A3,5,255)+0