Format Issue

[blackie42]

Hi

Downloading a report and column J is formatted as follows

d-mmm-yyyy hh:mm:ss

e.g 14-Aug-2017 01:00:46

Trying to copy this to adjacent cells in column N and using RIGHT function to isolate the date however it just returns a number.

Copying the original and reformatting to hh:mm:ss does show the time but clicking the cell it's just hiding the date i.e. click back on the cell and
the date is still there.

Any way to get the cell just to contain the trimmed date?

thanks
Jon

[offthelip]

EXCEL stores date/times as a number where the integer is the date and decimal fractional part is the time so
you can use the Rounddown() function, to just get the date part.

[mdmackillop]

=INT(A1) will give you the date value which you can format as required.

[blackie42]

Apologies guys it's the time that I need not the date

[mdmackillop]

=(A1)-INT(A1)

[blackie42]

Thanks very much

I had =TEXT(J9,"hh:mm:ss) which does appear to do the same job

regards
Jon

[mdmackillop]

It depends on what you want to do with the result. If you just need the text, OK. If you need the time difference between cells or other calculation using the result then the cell needs to keep its numerical value.