I'm running into an issue with data I've imported into Excel. One of the columns of data is date/time. Some rows are readable by Excel, while other rows are unreadable. I've tried the tricks I know including LEN, MID, LEFT, RIGHT etc, but nothing seems to work.
I've attached a small sample of this data, hoping someone can help me figure out a formula which will display both types of timestamps in the same format.
(the workbook also shows examples of the datetimes I am hoping to get)
Thanks.
You have a specification problem.
Cell B3 contains 12/03/2020 20:40:00, and your 'correct' value should be 12/3/2020 20:40, but that obscures things. The formula =TEXT(C3,"yyyy-mm-dd hh:mm:ss") returns 2020-12-03 20:40:00, which means USING YOUR LOCAL SETTINGS the 12/03/2020 in cell B3 is being treated as 3 Dec 2020. In contrast, cell B5 contains 13/03/2020 03:22. Under your local settings, presumably US standard, the 13 as month is invalid, which is why you can't convert it directly, but you convert it manually as 13 Mar 2020, which is very likely correct.
The specification problem is whether cell B3 is MM/DD/YYYY or DD/MM/YYYY like cells B5 and B6. If B3:B9 all came from someone else, especially from someone outside the US, then I'd figure ALL the dates were in DD/MM/YYYY format, so B3 should be 12 Mar 2020 rather than 3 Dec 2020. Is your Raw Data actually raw data? Do B3:B9 come from a text file?
I do not see your problem.
Your formula in C3 filled down works for both data types. =B3*1
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Try:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi, rows 5 and 6 give me a #VALUE error when I *1 them.
Hi Trevor, when I try that I also get a #VALUE error.
Not sure how I can help then. That works for me with your data
(replying to hrlngrv)
Hi, yes, unfortunately that is what I was getting when I tried this. The data is all from the same source... about 2,000 rows of it in a .csv format (actually ; seperated values). The datetimes are in sequential order, (which I can tell because there is another column with different dates, and they are in sequential order). This inconsistency is how I discovered the problem, because I noticed that the dates weren't in proper order after I had "converted" them using LEFT, RIGHT, MID functions. Another oddity in this info is that the LEN function gives me inconsistent results. Row 7 gives me 10 while Row 8 give me 16 and Row 9 is 11!
Last edited by jrtaylor; 11-12-2020 at 07:03 PM.
In any case I appreciate everyone's help. I think the data is simply corrupted.
As far as I can see, the entries that work with your "conversion" formula, that is, =B3*1 are already dates/times. I know this because, when I change the format to General, I see a number like 44512.44. The two that don't work are text entries. Hence, two parts to my formula.
Maybe try:Formula:Please Login or Register to view this content.
Since the original data is in a CSV, the only effective way to handle this is to change that file's extension to .TXT (either rename it using a .TXT extension, or copy the .CSV file to a .TXT file), then open the .TXT file. That triggers the Text to Columns wizard. Select Delimited and click Next, select comma as the delimiter and press Next, then in the area in the bottom of Step 3 of 3, find and select the date column, and in the upper left section select Date and in the drop-down to the right of Date, select DMY. This will have Excel treat that entire column as dates formatted as day, month, year.
The alternative involves formulas like
Formula:Please Login or Register to view this content.
ADDED: Excel is quite obtuse when it comes to CSV files. Excel will only open them as-is using your local settings. In your case, receiving CSV files from someone with different date settings, you have a lot of extra work. Opening .TXT file copies of .CSV files reduces that extra work. It would be nice for Excel to have the option of launching Text to Columns for CSV files as well as TXT files, but MSFT is extremely loathe to admit it ever makes mistakes, even tacitly, so don't hold your breath waiting for Excel to work like LibreOffice Calc. MSFT simply doesn't care enough about Excel users to provide such an option.
Last edited by hrlngrv; 11-12-2020 at 07:34 PM. Reason: addendum
For US region setting as M/d/yy
Please try at D3
=--(MID(TEXT(B3,"mm/dd/"),4,3)&REPLACE(TEXT(B3,"mm/dd/yyyy hh:mm"),3,3,))
or Use Power Query
Please Login or Register to view this content.
Last edited by Bo_Ry; 11-13-2020 at 03:29 AM.
Thank you Bo Rey, that does work. I appreciate your help!
Thank you hrlngrv!Originally Posted by hrlngrv
Since the original data is in a CSV, the only effective way to handle this is to change that file's extension to .TXT (either rename it using a .TXT extension, or copy the .CSV file to a .TXT file), then open the .TXT file. That triggers the Text to Columns wizard. Select Delimited and click Next, select comma as the delimiter and press Next, then in the area in the bottom of Step 3 of 3, find and select the date column, and in the upper left section select Date and in the drop-down to the right of Date, select DMY. This will have Excel treat that entire column as dates formatted as day, month, year.
The alternative involves formulas like
Formula:Please Login or Register to view this content.
ADDED: Excel is quite obtuse when it comes to CSV files. Excel will only open them as-is using your local settings. In your case, receiving CSV files from someone with different date settings, you have a lot of extra work. Opening .TXT file copies of .CSV files reduces that extra work. It would be nice for Excel to have the option of launching Text to Columns for CSV files as well as TXT files, but MSFT is extremely loathe to admit it ever makes mistakes, even tacitly, so don't hold your breath waiting for Excel to work like LibreOffice Calc. MSFT simply doesn't care enough about Excel users to provide such an option.
If you're local date settings are D/M/Y you won't see any problems. This is much more a configuration problem due to the OP's standard US date settings, namely M/D/Y. IOW, this is a class of problem for a US user which is likely only to be discernable by another US user.
Ain't computing fun?!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks