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
Microsoft Excel

Avatar of undefined
Last Comment
Harry Lee

8/22/2022 - Mon
TazDevil1674

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.
ASKER
statpro

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
jboddy

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!)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
statpro

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
TazDevil1674

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

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jboddy

Can you post the full string that you are working with from the ftp app?  I wonder if it is truncating the data.
TazDevil1674

Is it possible to attach a sample of the CSV file?
ASKER CERTIFIED SOLUTION
Harry Lee

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
statpro

HarryHYLee - this works perfectly. Many thanks for your assistance. Also - many thanks to everyone else who contributed.
Regards,
Nick
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Harry Lee

You are welcome. I'm glad I can help.