Excel formula to convert imported dates or incorrectly formatted dates. Excel VBA macro for negatives

If you cannot read HTML in your email please go here: http://www.ozgrid.com/News/Archive.htm

OzGrid Excel and VBA Newsletter

You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!To no longer receive our newsletter, send an NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .Microsoft and Microsoft Excel are registered trademarks ofMicrosoft Corporation Read this issue and past issuesonline here :

SmartDraw is the quick and easy way to draw quality flowcharts, org charts, web graphics, and business presentations. You can try SmartDraw free for 30 days and see why it was voted "Best Business Program" two years in a row. For business charts and diagrams- ISO 9000 Flowcharts, Floor Plans, Circuit Diagrams, Flow Charts, Org Charts, VisualScript XML, Floor Plans, Business Forms, Network Diagrams, Circuit Diagrams, Engineering Diagrams, Flyers, Maps, Timelines, Clip Art, and Web Graphics and MUCH MORE

MAIN INDEX

What's New at OzGrid

This month has been another busy one at OzGrid. In addition to our upcoming book "100 Excel Hacks" we have been busy adding lots of free content to our site. Many of these are past questions and answers from our Free Excel Support Forum We have made up index pages for all of these and they can be viewed via Excel Questions and Answers and Excel Frequently Asked Questions and Answers These pages will be added to continuously and hopefully will become a valuable resource for all Excel users.

The other BIG NEWS is the very large drop in our training prices, these can be viewed here These price should remain until 2004 when we have completed the book.

Excel Tips and Tricks

If you work with Excel, sooner or later you will encounter a problem with dates. Particularly if these dates have been imported from another program. Let's look at some of the date formats that you may encounter and also how to convert these to standard Excel dates.

For all New Dates below I will use the format: mm/dd/yy with a date that is 11-23-03 The cells in Column C have been formatted with a format of mm/dd/yy US DATE FORMAT

	A	B	C
1	Old Date	Formula Used	New Date
2	112303	=VALUE(LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2))	11/23/03
3	031123	=VALUE(MID(A3,3,2)&"/"&RIGHT(A3,2)&"/"&LEFT(A3,2))	11/23/03
4	231103	=VALUE(MID(A4,3,2)&"/"&LEFT(A4,2)&"/"&RIGHT(A4,2))	11/23/03

For all New Dates below I will use the format: dd/mm/yy with a date that is 23-11-03 the cells in Column C have been formatted with a format of dd/mm/yy EUROPEAN DATE FORMAT

	A	B	C
1	Old Date	Formula Used	New Date
2	112303	=VALUE(LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2))	11/23/03
3	031123	=VALUE(MID(A3,3,2)&"/"&RIGHT(A3,2)&"/"&LEFT(A3,2))	11/23/03
4	231103	=VALUE(MID(A4,3,2)&"/"&LEFT(A4,2)&"/"&RIGHT(A4,2))	11/23/03

If these types of formulae are to be used a lot, it would be a good idea to perhaps create a macro to do the conversion for you. See: Excel VBA Tips and Tricks below. If you get stuck make use of our Free Excel Support Forum

Mirror Negatives

Anyone that has worked with imported numbers in Excel will often be left with a problem of imported negative numbers that have the negative sign to the right of the number, mirror negatives. This means Excel will not see it as a negative number and to make matters worse, it won't even see it as a valid number.

SAP is one such program that does this with negative numbers, e.g 200- instead of -200. Let's say you have a great long list of numbers you have just imported and some of them are those 'so-called negative numbers'. Your job is to convert these to valid negatives that Excel will recognize. For the purpose of the exercise we will make the range A1:A100.

Copy this down as many cells as needed and then copy them and select cell A1 and go to Edit>Paste Special-Values over the top of the originals.

To give you an idea on how the formula works enter this part of it in any cell where A1 has the text 200-

You will end up with; -200- The TRIM function simply ensure there are no space characters in the cell. As we end up with -200- we next need to remove the second occurrence of the negative sign. This is what; the SUBSTITUTE function is doing. We have told it to substitute the second occurrence of "-" with "" (empty text). Now, the result returned is actually text (as that is what the SUBSTITUTE function returns) so we simply use +0 and Excel will convert it to a valid number.

If doing this is a common task you should consider a macro to make the job easier.Below is one that will do the task at hand in no time.

Excel VBA Tips and Tricks

This month we will flow on from the Excel tips above and see just how we can automate these tasks. The first one we will look at is the date conversion format change.

The macro example I will use will be for the imported date in cell A2 and will be converting it to the European date format of dd/mm/yy. If your imported dates are in the format of cells B3 or B4 simplyrecord a macro entering the required formula (shown in table below) in cell A3 or A4 respectively. Then replace the code below;"=VALUE(MID(RC[1],3,2)&""/""&LEFT(RC[1],2)&""/""&RIGHT(RC[1],2))"with the code generated from entering the required formula.

'Set range to data cells only
Set rRange = Range("A1", Range("A65536").End(xlUp))
    'Insert spare Column.
    With rRange 'With Range Object
        'Add a column for functions
         .EntireColumn.Insert
        'Insert the Functions as relative. May need replacing!
         .Offset(0, -1).FormulaR1C1 = _
         "=VALUE(MID(RC[1],3,2)&""/""&LEFT(RC[1],2)&""/""&RIGHT(RC[1],2))"
        'Covert to values only and date format
         .Offset(0, -1) = .Offset(0, -1).Value
         .Offset(0, -1).NumberFormat = "dd/mm/yy"
        'Delete the original data
         .EntireColumn.Delete
    End With

If your imported dates are a US date format then simplyrecord a macro entering the required formula (shown in table below) in cell A2, A3 or A4 respectively and replace:

As we have completely avoided the use of a loop the code is very fast. It does however require that column "IV" has no data in it as it inserts a new column for the conversion.

Mirror Negatives

If doing this is a common task you should consider a macro to make the job easier. Below is one that will do the task at hand in no time. It will work on your Selection at the time of running the macro. It has been optimized for speed.Or, you could of course employ the same principles of the Date Conversion macro.

Sub ConvertMirrorNegatives()
Dim rCell As Range
Dim rRange As Range
Dim lCount As Long
Dim lLoop As Long

If Selection.Cells.Count = 1 Then
MsgBox "Please select the range to convert", vbInformation
Exit Sub
End If

On Error Resume Next
Set rRange = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)

If rRange Is Nothing Then
   MsgBox "No mirror negatives found", vbInformation
   On Error GoTo 0
   Exit Sub
End If

lCount = WorksheetFunction.CountIf(Selection, "*-")
Set rCell = Selection.Cells(1, 1)

     For lLoop = 1 To lCount
        Set rCell = rRange.Find(What:="*-", After:=rCell, _
         LookIn:=xlValues, LookAt:=xlPart, _
         SearchOrder:=xlByRows, SearchDirection:= _
         xlNext, MatchCase:=False)
        rCell.Replace What:="-", Replacement:=""
        rCell = rCell * -1
  Next lLoop

On Error GoTo 0
End Sub

You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :

Microsoft Excel Add-ins	Financial Add-ins and Software
Microsoft Excel Training & Tutoring	Conversion Software
Microsoft Excel Templates	Construction and Estimating Software
Excel, Word, Access Password Recovery	Financial Calculators
Corrupt Excel, Word, Access File Recovery	Business Planning
Excel & VBA books	Excel on the WWW
Windows & Internet Software	Downloadable Excel Training
Barcode, Fonts, ActiveX, labels and DLL's	OUR CURRENT SOFTWARE SPECIALS!
Chart Tools & Add-ins	The Analysis Add-ins Collection
Trading Software & Add-ins	TOTALLY FREE 24/7 EXCEL, VBA SUPPORT!

	A	B	C
1	Formula	Imported Date	Result
2	=VALUE(MID(B2,3,2)&"/"&LEFT(B2,2)&"/"&RIGHT(B2,2))	112303	23/11/03
3	=VALUE(RIGHT(B3,2)&"/"&MID(B3,3,2)&"/"&LEFT(B3,2))	031123	23/11/03
4	=VALUE(LEFT(B4,2)&"/"&MID(B4,3,2)&"/"&RIGHT(B4,2))	231103	23/11/03

Microsoft Excel Add-ins	Financial Add-ins and Software
Microsoft Excel Training & Tutoring	Conversion Software
Microsoft Excel Templates	Construction and Estimating Software
Excel, Word, Access Password Recovery	Financial Calculators
Corrupt Excel, Word, Access File Recovery	Business Planning
Excel & VBA books	Excel on the WWW
Windows & Internet Software	Downloadable Excel Training
Barcode, Fonts, ActiveX, labels and DLL's	OUR CURRENT SOFTWARE SPECIALS!
Chart Tools & Add-ins	The Analysis Add-ins Collection
Trading Software & Add-ins	TOTALLY FREE 24/7 EXCEL, VBA SUPPORT!