Link to home
Start Free TrialLog in
Avatar of statpro
statproFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel date issue

Hi Experts,
I am having trouble with formatting excel dates. I have an FTP program that i have exported all the users and the last time they logged on to a CSV file.

The date is not formatted as i would like though.
An example is this:
- On the FTP program it is '27/08/2013 15:06:00'
- In Excel this would be '1377612360'

Is there a formula or something i could apply to this column to display the correct date. I don't mind if the time is included or not.

Many thanks
Nick
Avatar of TazDevil1674
TazDevil1674
Flag of United Kingdom of Great Britain and Northern Ireland image

If you are opening the CSV file in excel, you should get options like fixed width or delimited format, you then should be able to change relevant columns from General to Date.
Avatar of statpro

ASKER

If i set the column as 'Date' i'm given the option to set it as English UK or English US, and different sub options - i've tried them all but it always results in a string of hashes ##########.

nick
I do not know what is producing that number, but in Excel it should be 41513.6291666667 for the date and time shown!
The number of days since Jan 01 1900 (I think!)
Avatar of statpro

ASKER

Hi jboddy - i put your number in and i can see that it results in 08/27/2013 3:06pm.

Maybe it is the UK format of the number and then time in 24hr clock with seconds?
'27/08/2013 15:06:00'

Not sure if this can be translated?


thanks
nick
If you get a string of hashes, have you increased the colum width to accommodate the whole date/time?
Avatar of statpro

ASKER

Hi TazDevil - i tried that as well. I think it is more the case that which ever option i choose it does not exactly match the format of '27/08/2013 15:06:00'
Some options are close, but not exactly right so it is not able to give the correct output.

thanks
nick
Can you post the full string that you are working with from the ftp app?  I wonder if it is truncating the data.
Is it possible to attach a sample of the CSV file?
ASKER CERTIFIED SOLUTION
Avatar of Harry Lee
Harry Lee
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of statpro

ASKER

HarryHYLee - this works perfectly. Many thanks for your assistance. Also - many thanks to everyone else who contributed.
Regards,
Nick
You are welcome. I'm glad I can help.