If you cannot read HTML in your email please go here: http://www.ozgrid.com/News/Archive.htm If you cannot read HTML in your email please go here: http://www.ozgrid.com/News/Archive.htm
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 :
**PLEASE DO NOT REPLY TO THIS ADDRESS **
All Excel Templates Normally an 80% saving at $299.00. Xmas Super Special $127.00! Best Value on the WWW More Hot Xmas Specials Here!
We Recommend SmartDraw for Flowcharts
 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 |
Excel HTML/JavaScript | Excel ASP.Net |Excel Java (J2SE/J2EE) | Excel J 2ME |Excel for Java Phones
MAIN INDEXWhat's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks
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.
Construct sure-fire Excel formulas.Free Tip Sheet makes it easy. Barcode, Fonts, ActiveX, DLL's, Labels and more!
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
ALSO SEE: Create an Excel Calendar Control | Excel Date and Times
Easy to use, well priced construction estimator for QuickBooks
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.
In cell B1 enter this formula;
=SUBSTITUTE(RIGHT(TRIM(A1),1)&A1,"-","",2)+0
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-
=RIGHT(TRIM(A1),1)&A1
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, Word, Access Password Recovery ||Corrupt Excel, Word, Access File Recovery
Are You Into Excel and Excel VBA?Our 4 Most Popular Bundled Savings
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.
| 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 |
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:
"=VALUE(MID(RC[1],3,2)&""/""&LEFT(RC[1],2)&""/""&RIGHT(RC[1],2))"
with the code generated.
| A | B | C | |
| 1 | Formula Used | Imported Date | Result |
| 2 | =VALUE(LEFT(B2,2)&"/"&MID(B2,3,2)&"/"&RIGHT(B2,2)) | 112303 | 11/23/03 |
| 3 | =VALUE(MID(B3,3,2)&"/"&RIGHT(B3,2)&"/"&LEFT(B3,2)) | 031123 | 11/23/03 |
| 4 | =VALUE(MID(B4,3,2)&"/"&LEFT(B4,2)&"/"&RIGHT(B4,2)) | 231103 | 11/23/03 |
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.
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
On Error GoTo 0
End Sub
Until next month, keep Excelling!
Office Ready Professional 3.0 |High Impact e-Mail 2.0 |Office-Ready Business Plans |Office-Ready Marketing Plans |e-Marketing Suite |Office Policy Manual |Ultimate Everyday Calculator |Ultimate Financial Calculator |Ultimate Marketing Calculator |Template Zone Home Page |Office Ready Stuff It |Ultimate Loan Calculator
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 :