LinkBack URL
About LinkBacks
I have a program that exports files to excels, but excel doesn't recognize the time date format it uses. Example: 8/5/2013 20:29:43. I would like to reformat it into something that I can search through. Any help would be appreciated.
I have a program that exports files to excels, but excel doesn't recognize the time date format it uses. Example: 8/5/2013 20:29:43. I would like to reformat it into something that I can search through. Any help would be appreciated.
That date/time format looks fine, it's basically kind of the standard format.
How is Excel not recognising it?
Are you 'telling' Excel to treat that column as a date/time?
If posting code please use code tags, see here.
I believe so. I currently have the cells formatted to mm/dd/yyyy hh:mm:ss, and every time I attempt to do a calculation based on the cell I get #VALUE
I was actually thinking more of when you are importing the data.
How are you importing?
PS You can check if Excel is recognising the 'date' using ISNUMBER, if that returns TRUE it is a date, FALSE it isn't
PPS Might be worth checking for trailing/leading/errant spaces etc
Maybe the imported data has spaces before and after the data and it is possible that the space between the date and time is not really a space but another character that looks like a space.
Select the space in the date/time string and copy it. Paste that into say M1. In N1 enter =CODE(M1) If the code isn't 32 then the space isn't a space.
If the date and time are entered in column A enter this in column B and copy down the length of your data.
If the code in N1 isn't 160, use that number in the CHAR() function. Format the date and time the way that you want (dd/mm/yyyy hh:mm:ss)Formula:
Please Login or Register to view this content.
The above will trim off any leading or trailing spaces and "fix" any strange spaces if you found a strange code for the space. A normal space between the date and time will be left alone.
Last edited by newdoverman; 08-06-2013 at 03:41 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thanks fro the advice folks, but neither of those solutions have worked. I have attached the offending original spreadsheet. Column E is the one that is giving me problemsaugust 1 - 6.xlsaugust 1 - 6.xls
I multiplied the date by 1 then copied the result back into the date column formatted as dd/mm/yyyy hh:mm:ss.
Workbook with calculation enclosed.
I tried exporting the file fro the program again, this time without using it to pre-analyze the data. The raw data works flawlessly. Thanks for you time folks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks