G
gb_S49
Hmmmm is it possible for a formula to just extract the time aspect?
30/03/2005 14:17:00
30/03/2005 14:17:00
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Duke Carey
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
=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
G
gb_S49
The first one worked but i cannot get the second too. Do i substitue right
for the cell location?
for the cell location?
D
Duke Carey
try
=TIMEVALUE(RIGHT("30/03/2005 14:17:00",8))
and see what you get
=TIMEVALUE(RIGHT("30/03/2005 14:17:00",8))
and see what you get
G
gb_S49
I must be being a total dork as all i get is a #value
=TIMEVALUE(RIGHT(A64,8))
=TIMEVALUE(RIGHT(A64,8))
D
Duke Carey
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
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
G
gb_S49
Got it. Thanks Duke
Duke Carey said: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