Pushing my luck

[Guest]

Hmmmm is it possible for a formula to just extract the time aspect?
30/03/2005 14:17:00

 

[Guest]

Use
=mod(date_time value,1)
& format it as time

If it is a text value, use
=TIMEVALUE(RIGHT(text value,8))
& format it as time

 

[Guest]

The first one worked but i cannot get the second too. Do i substitue right
for the cell location?

 

[Guest]

try

=TIMEVALUE(RIGHT("30/03/2005 14:17:00",8))

and see what you get

 

[Guest]

I must be being a total dork as all i get is a #value
=TIMEVALUE(RIGHT(A64,8))

 

[Guest]

The 2d formula works only if your date & time value is in the cell as text.
If it's there as a serial number, you'll get #VALUE.

I suggested using the actual text in the formula because it is possible that
the cell is text, but contains extra spaces to the right of the digits,
meaning the RIGHT(A64,8) will bring back something that Excel can't translate
into a time, something like ":17:00 " instead of "14:17:00"

If the formula works just with the text, then check cell A64 to see if it is
a serial number or if there are trailing spaces that would gum up the works.
You can try the TRIM function to get rid of unwanted spaces or the CLEAN
function to get rid of non-printing chars

 

[Guest]

Got it. Thanks Duke

The 2d formula works only if your date & time value is in the cell as text.
If it's there as a serial number, you'll get #VALUE.

I suggested using the actual text in the formula because it is possible that
the cell is text, but contains extra spaces to the right of the digits,
meaning the RIGHT(A64,8) will bring back something that Excel can't translate
into a time, something like ":17:00 " instead of "14:17:00"

If the formula works just with the text, then check cell A64 to see if it is
a serial number or if there are trailing spaces that would gum up the works.
You can try the TRIM function to get rid of unwanted spaces or the CLEAN
function to get rid of non-printing chars