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
statproAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TazDevil1674Commented:
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.
0
statproAuthor Commented:
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
0
jboddyCommented:
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!)
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

statproAuthor Commented:
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
0
TazDevil1674Commented:
If you get a string of hashes, have you increased the colum width to accommodate the whole date/time?
0
statproAuthor Commented:
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
0
jboddyCommented:
Can you post the full string that you are working with from the ftp app?  I wonder if it is truncating the data.
0
TazDevil1674Commented:
Is it possible to attach a sample of the CSV file?
0
Harry LeeCommented:
statpro,

The date format in your CSV file is in Unix Time Format. That's is 1377612360 seconds since UTC January 1, 1970 00:00:00.

So if you want to convert that back into common time format, in Excel, you will have to do =25569 + (1377612360 / 24 / 60 / 60). The formula is 01/01/1970 00:00:00 + (Your Time Stamp / 24 hr per day / 60 min per hour / 60 Secs per min).

Then, custom format the cells to mm/dd/yyyy hh:mm:ss format.

Since the Unix Time in from UTC, your Day Light Saving Time is the issue why it will convert the date and time to 1 hour less than what you expect, since UTC has no DST.

If you are good with no DST factor, you are good to go; otherwise, will have to do some more work using IF to determine the correct Date/Time defining the DST start day and DST end day.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
statproAuthor Commented:
HarryHYLee - this works perfectly. Many thanks for your assistance. Also - many thanks to everyone else who contributed.
Regards,
Nick
0
Harry LeeCommented:
You are welcome. I'm glad I can help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.