Start Free Trial
Come for the solution, stay for everything else.
Start Free Trial
Excel date issue
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.
8/22/2022 - Mon
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.
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 ##########.
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
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?
Not sure if this can be translated?
If you get a string of hashes, have you increased the colum width to accommodate the whole date/time?
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.
to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Log in or sign up to see answer
Become an EE member today
7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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
HarryHYLee - this works perfectly. Many thanks for your assistance. Also - many thanks to everyone else who contributed.
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.
You are welcome. I'm glad I can help.
Plans and Pricing
Certified Expert Program
© 1996-2022 Experts Exchange, LLC. All rights reserved. Covered by US Patent