Hello,
I need assistance, please (anyone!!).
I have a spreadsheet where I have run a query and pulled an export to Excel. The report is very simple (below). Basically, I am trying to convert the second column from Day/Month/Year to Month/Day/YEAR with the allocated time listed. If I go and paste this information into excel (since it is an export) there are constraints in the cell that do not allow me to do the general format options and change the column into the correct format. Nothing works.
Any help here?
Created Updated Resolved
12/13/2011 13/12/11 16:07 12/13/2011 0:04:00
Sorry to make it clear...
my column is
A = 12/13/11
B = 13/12/11 16:07
C = 12/13/11
D= 0:04:00
The focus is column B -- It will not convert
Possibly there's an easier way...but you can add a temporary helper column formula:
=DATE("20"&MID(B2,7,2),MID(B2,4,2),LEFT(B2,2))+TIME(MID(B2,FIND(" ",B2)+1,2),RIGHT(B2,2),0)
copied down
then copy and paste special|values over the original.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Wow!!!
That worked.. so I have to do that into another cell... so that the dates are transformed!!
Wow!!!
Thank you!
Okay.. NOt working..
Help...
Excel is seeing that combination as a text string and therefore you can't "reformat" as date.
An alternative that you can try, if you don't mind splitting the date and time into 2 separate columns...
First, insert a column before column C.
Then select column B and go to Data|Text to Columns.
Select Delimited, click Next,
Select Space checkbox, and click Next,
Select Date radio button from the column data format area and then select DMY from the adjacent drop down
Click Finish.
You can re-combine the date and time again after, if desired.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Would it be easier to post a copy of the spreadsheet?
Try above, if feasible first, then if not, post the worksheet, void of confidential info.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
See attached.
The focus is Column J, K, L.
Should be easy but not sure why it isn't working.
When I open your workbook, all 3 cells show up as proper dates and time... all showing 9/12/2011 and a time.. and they are all formatted as custom: m/d/yyyy h:mm
Dates isn't one of my strong suits... especially in translation from UK to US formats... so not sure what the issue is.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
If still trouble and you don't want to do the Text to Columns, try helper formula adjusted:
=DATE(MID(K2,7,4),MID(K2,4,2),LEFT(K2,2))+TIME(MID(K2,FIND(" ",K2)+1,2),RIGHT(K2,2),0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
No... The dates are wrong... it is in DAY/MONTH/YEAR format...
I need them in
"MONTH" / "DAY" / "YEAR" format...
So what was mentioned 9/12/11 --- that should be December 9, 2011 --- the system is coverting it once it is extracted to Excel.
That is my entire problem.
Help!
I should have posted a different example
Please see attached.
Well this original formula worked for me to convert that to November 28th:
=DATE("20"&MID(K2,7,2),MID(K2,4,2),LEFT(K2,2))+TIME(MID(K2,FIND(" ",K2)+1,2),RIGHT(K2,2),0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I tried =DATE(MID(K2,7,4),MID(K2,4,2),LEFT(K2,2))+TIME(MID(K2,FIND(" ",K2)+1,2),RIGHT(K2,2),0)
in the spreadsheet and it does not work.
Please open the attachment and see if you are able to do it. It does not work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks