Check formula in CI2. Does it not appear right?
Alternatively have a look at how something similar was resolved here: http://www.excelforum.com/excel-gene...te-format.html
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.
Yes, this formual works : DATE("20"&MID(B4,7,2),MID(B4,4,2),LEFT(B4,2))+TIME(MID(B4,FIND(" ",B4)+1,2),RIGHT(B4,2),0)
However, it takes out the time? How can I add the time in the column back in there?
To addk to the above...
when I use: DATE("20"&MID(B4,7,2),MID(B4,4,2),LEFT(B4,2))+TIME(MID(B4,FIND(" ",B4)+1,2),RIGHT(B4,2),0)
it works in some fields and in others it does not... because the dates show up as 1/12/11 but it should be 12/1/11. Also, the times do not populate.
Is there a way that it can get populated correctly.
I basically have 500+ rows.. where dates are listed as DD/MM/YY--- and some are showing up as 1/12/11 but it should show up as 12/1/11 - when I use DATE("20"&MID(B4,7,2),MID(B4,4,2),LEFT(B4,2))+TIME(MID(B4,FIND(" ",B4)+1,2),RIGHT(B4,2),0) it tells me I have an error in my cell. Also, my time range is not being captured.
Is it possible to fix?
another attachment with the two examples.
Sorry!
I appreciate everyone's help here.
Just need to figure out how to solve this.. and carry the time's in the same cell.
You can use a UDF with a helper column:
Option Explicit Public Function Reformat(ByVal DTUpdate As String) As Date Dim TimeFrag As String, _ DateFrag As Variant 'copy the time string TimeFrag = Mid(DTUpdate, InStr(DTUpdate, " ") + 1) 'split the day, month and year into an array at the slash characters DateFrag = Split(Mid(DTUpdate, 1, InStr(DTUpdate, " ") - 1), "/") 'rebuild the date from the pieces Reformat = CDate(DateFrag(1) & "/" & DateFrag(0) & "/" & DateFrag(2) & " " & TimeFrag) End Function
---
Ben Van Johnson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks