Pushing my luck
 

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

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

"gb_S49" wrote:

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

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

"Duke Carey" wrote:

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

"gb_S49" wrote:

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

try

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

and see what you get


"gb_S49" wrote:

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

"Duke Carey" wrote:

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

"gb_S49" wrote:

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

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

"Duke Carey" wrote:

try

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

and see what you get


"gb_S49" wrote:

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

"Duke Carey" wrote:

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

"gb_S49" wrote:

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

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


"gb_S49" wrote:

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

"Duke Carey" wrote:

try

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

and see what you get


"gb_S49" wrote:

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

"Duke Carey" wrote:

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

"gb_S49" wrote:

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

Got it. Thanks Duke

"Duke Carey" wrote:

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


"gb_S49" wrote:

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

"Duke Carey" wrote:

try

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

and see what you get


"gb_S49" wrote:

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

"Duke Carey" wrote:

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

"gb_S49" wrote:

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