Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I convert a "General" format date string into a date? 2

Status
Not open for further replies.
Hasit

Hasit

IS-IT--Management
Sep 13, 2001
1,061
GB
Yes - its the old "Date" management problem. I used to be quite good at excel, but this one has me stumped. The situation is this:

Some data has been imported and the date column has come in as a string in "General" format. The string is:

4/19/07 8:14:14 PM

I don't need the time, so I used the "LEFT" function to leave only the date. OK so far. But, if I then try and perform any calculations on it, it fails (of course it would, its not a date!)

So the question, how do I convert:

4/19/07

into a date.

And before you say "just change the format", I have tried that, and it says it has done it, but again, when performing calculations on it, it does not work - the result is a #VALUE!

I have also tried using the "Text to Columns" option, and again that does not result in a date value (I have used this option to change the format from mm/dd/yyyy to dd/mm/yyyy successfully in the past).

If it was a few dates, I would manually update them by re-entering

Suggestions?
 
Sort by date Sort by votes
How do you import the data?

Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
Upvote 0 Downvote
Its from the Remedy system (manage problem and incident tickets). There is a function in Remedy that allows you to dump to .csv.

Even if I force the format during import, it comes in as "General".
 
Upvote 0 Downvote
How do you "force the format"?

Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
Upvote 0 Downvote
By using the Text to column function (sorry, I should have stated that).
 
Upvote 0 Downvote
Text to Column will work if you separate the date from the time, and make sure that you specify M/D/Y for the date part.

Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
Upvote 0 Downvote
Glenn - tried that. Stripped off the time and then went through the Text to column process. Get the same result. The date remains in General format (and in US format) woth no change - i.e. I get 4/19/07 after going through the process.
 
Upvote 0 Downvote
Did you specify the column data format in the Wizard step 3, or leave it as General?

Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
Upvote 0 Downvote
In step 3, I specify DATE with DMY as the format.

No difference.
 
Upvote 0 Downvote
Read my posting earlier in the thread:
Text to Column will work if you separate the date from the time, and make sure that you specify M/D/Y for the date part.
Click to expand...

You are specifying the wrong date format.


Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
Upvote 0 Downvote
doh! Sorry Glenn, you are right. Typo, I should have said MDY is the format I use, but still no joy.
 
Upvote 0 Downvote
OK understood.

What are the contents of one of the cells after trying that? Can you check the length of the contents by doing =LEN(cell_ref) elsewhere in the sheet ... I'm wondering whether there's any strange characters coming in with the import.

Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
Upvote 0 Downvote
Good idea. Tried the LEN and 8. Which makes sense, because I used =LEFT(del_ref,8).

Now, I didn't mention this before, for fear of complicating things, but my first issue, I have resolved, by copying and pasting special just the value. Then performing the text to column process and using MDY, I get the date set correctly and everything is wonderful!

(Thanks Glenn for confirming the process!).

Heres the rub. About 6 months ago, Remedy went through an upgrade and whatever was implemented, changed the date format from mm/dd/yy hh:mmper my initial post to mm/dd/yyyy hh:mm:ss

BUT - the date format these dates come in as is a custom format dd/mm/yyyy hh:mm.

The problem is, they are still in US format. So using =LEFT(cell_ref,10), copying and pasting special just the value, then text to column, the format does not change. So a date of 02/12/2008 stays as 02/12/2008. Which clearly can't be the case, because it means that a ticket was raised on the 2nd of December - which has not occured yet!

Any thoughts Glenn?
 
Upvote 0 Downvote
One more thing, which I know will make a difference. When I perform the =LEFT process on the date format I mention in my last post, the date gets converted to a serial number, so 02/12/2008 06:58:01 is converted to 39784.2902. Its on the serial number that I perform the various things I have specified.
 
Upvote 0 Downvote
Its on the serial number that I perform the various things I have specified.
Click to expand...

You must do the things I've specified on the text string, and not on an Excel date serial number. The Text To Columns transforms text of the wrong format into the correct serial number for that format. If you mess with text to break it down, and it gets converted to serial number before you get to do Text To Columns, then you're going about it the wrong way.

Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
Upvote 0 Downvote
Glenn - its been about 18 months since I have seriously used Excel (change in job etc).

And like riding a bike, its slowly coming back.

You are correct. I was being a complete idiot with regards to Text to Columns. I was not only using the wrong options (i.e. not stripping out the time based on the TtC wizard, but I was also screwing around with =LEFT when it was unnecessary).

As usual - you have come up trumps.

While the problem may have been straightforward (for you), I am still awarding a star because you reminded me of the perils of messing with dates in Excel and the correct way of manipulating them.

Thanks.
 
Upvote 0 Downvote
Glad that I was able to help. :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.
 
Upvote 0 Downvote
Hi Hasit,

You can toggle the date formats with:
Code: 
Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Cells.Count = 1 Then
  Set DtRange = ActiveCell
Else
  Set DtRange = Selection
End If
With Application
  On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
  For Each oCell In DtRange.SpecialCells(xlConstants)
    oTxt = oCell.Text
    If UBound(Split(oTxt, "/")) = 2 Then _
      oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2))
  Next oCell
End With
End Sub
The output is in the form of date values that you can format as dates via Format|Cells|Number|Date. Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.

Cheers

[MS MVP - Word]
 
Upvote 0 Downvote
macropod - nice. I tried your code for giggles, and it works a treat. Thanks for taking the time.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Question
Excel Pivot Table - Combine Values into a single row
Replies
15
Views
924
combo
combo
techie131
  • Locked
  • Question
Excel Date format
2
Replies
20
Views
360
SkipVought
S
ladyck3
  • Locked
  • Question
Excel 365: [SOLVED ]Data Dump Date: "Jul 12 20:27:14 CDT 2022" convert to 07/12/2022 2
Replies
8
Views
131
ladyck3
ladyck3
ladyck3
  • Locked
  • Question
Excel 365 - Auto-Populating a cell from date in the next cell. 2
Replies
9
Views
181
ladyck3
ladyck3
E
  • Locked
  • Question
Extract lines from table into new table
Replies
2
Views
333
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top