-
Format Issue
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
-
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.
-
=INT(A1) will give you the date value which you can format as required.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Apologies guys it's the time that I need not the date
-
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Thanks very much
I had =TEXT(J9,"hh:mm:ss) which does appear to do the same job
regards
Jon
-
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules