Mighty F9 for formula auditing

Formula auditing tool like trace precedent/dependent are very helpful in tracing cell links in a big excel file. 

There is one more trick which can make your formula auditing little easier and that is F9 key.

In a formula press F2 to to show the cell links, then select the particular cell address and press F9.

It will show you the value of the cell

Image

 

If you select the formula within the formula of cell it will show its result.

go play….

Advertisements

Text Date to Value Date

Recently I was going through a set of data in excel which had dates in dd.mm.yy format e.g. 03.06.12
The dates did not allow to do any calculation on them as they were imported as text format.
Doing find and replace “.” With “/” with Ctrl+H command didn’t help as it changes the date format to mm.dd.yy with dates as months and vice versa, in some cases dates greater than 12 produced error.

I used the formula
=DATE(RIGHT(A1,2)+100,MID(A1,4,2),LEFT(A1,2))

RIGHT(),MID() and LEFT() extracts the dates year, month and date value
100 is added to RIGHT() as year belong to 2000’s

DATE() put them in order

Now I was able to format and do calculations on dates