Convert to Time Formats

[Harold Garcia]

NAME	¦ 7/01/2013¦	7/02/2013¦ <tbody> </tbody>	7/03/2013¦ <tbody> </tbody>	7/04/2013¦	<tbody> </tbody>
ID NO. DATE HIRED	============ <tbody> </tbody>	=========== <tbody> </tbody>	=========== <tbody> </tbody>	=========== <tbody> </tbody>
	¦ ¦	¦ <tbody> </tbody>	¦ <tbody> </tbody>	¦ <tbody> </tbody>
ROLLE, ROMELSON M	¦20:16 ¦	20:24 ¦ <tbody> </tbody>	¦ <tbody> </tbody>	¦ <tbody> </tbody>
05-0544 09/12/2005	¦ 5:56 ¦	5:49 ¦	¦ <tbody> </tbody>	¦ <tbody> </tbody>
	¦ ¦	¦ <tbody> </tbody>	Day-Off ¦ <tbody> </tbody>	Day-Off ¦ <tbody> </tbody>

<tbody>
</tbody>

How can i convert the numbers in time format (HH:MM am/pm)?

 

[marc j]

Good morning,

You can use the timevalue and substitute function.
(i used "¦20:16 ¦" but you can use a cell reference like A7.
You will need to format the cell as time, in format cell, time select the "1:30 pm" option.

=TIMEVALUE(SUBSTITUTE("¦20:16 ¦","¦",""))


From Excel Help

TIMEVALUE(time_text)The TIMEVALUE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):</pre>

• Time_text Required. A text string that represents a time in any one of the Microsoft Excel time formats; for example, "6:45 PM" and "18:45" text strings within quotation marks that represent time

Marc j

 

[Harold Garcia]

Wow it works... thanks...

how about adding an IF condition to return blank having a #VALUE...