PDA

View Full Version : [SOLVED] Format Issue



blackie42
08-15-2017, 04:29 AM
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
08-15-2017, 04:51 AM
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
08-15-2017, 05:00 AM
=INT(A1) will give you the date value which you can format as required.

blackie42
08-15-2017, 05:56 AM
Apologies guys it's the time that I need not the date

mdmackillop
08-15-2017, 06:05 AM
=(A1)-INT(A1)

blackie42
08-15-2017, 06:11 AM
Thanks very much

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

regards
Jon

mdmackillop
08-15-2017, 06:27 AM
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.