Extract time from date
 

Hi all, I have formated cell A1 in "dd\mm\yyyy hh:mm" and value
appearing is "26/08/2009 10:00". I need some formula in cell B1 to
extract just the time bit from cell A1 value. I know I can copy cell
A1 value into cell B1 and change the format into "hh:mm" and i'll get
what i want but i dont want to do it that way. I need some thing like
"RIGHT(A1,5)". I tried this but its not working. Please can any
friend can help.

Extract time from date
 

Use this...

=MOD(A1,1)

and format the cell as Time.

--
Rick (MVP - Excel)


"K" wrote in message
...
Hi all, I have formated cell A1 in "dd\mm\yyyy hh:mm" and value
appearing is "26/08/2009 10:00". I need some formula in cell B1 to
extract just the time bit from cell A1 value. I know I can copy cell
A1 value into cell B1 and change the format into "hh:mm" and i'll get
what i want but i dont want to do it that way. I need some thing like
"RIGHT(A1,5)". I tried this but its not working. Please can any
friend can help.

Extract time from date
 

Hi,
=text(a1,"hh:mm")

if this helps please click yes thanks

"K" wrote:

Hi all, I have formated cell A1 in "dd\mm\yyyy hh:mm" and value
appearing is "26/08/2009 10:00". I need some formula in cell B1 to
extract just the time bit from cell A1 value. I know I can copy cell
A1 value into cell B1 and change the format into "hh:mm" and i'll get
what i want but i dont want to do it that way. I need some thing like
"RIGHT(A1,5)". I tried this but its not working. Please can any
friend can help.

Extract time from date
 

Just so the OP knows, your formula as written, returns text, not a number.
If the OP needs a number, then he should use this...

=--TEXT(A1,"hh:mm")

--
Rick (MVP - Excel)


"Eduardo" wrote in message
...
Hi,
=text(a1,"hh:mm")

if this helps please click yes thanks

"K" wrote:

Hi all, I have formated cell A1 in "dd\mm\yyyy hh:mm" and value
appearing is "26/08/2009 10:00". I need some formula in cell B1 to
extract just the time bit from cell A1 value. I know I can copy cell
A1 value into cell B1 and change the format into "hh:mm" and i'll get
what i want but i dont want to do it that way. I need some thing like
"RIGHT(A1,5)". I tried this but its not working. Please can any
friend can help.

Extract time from date
 

thanks Eduardo