Helen Bradley continues her series on the hidden and undocumented functions of Excel 2003.
By Helen Bradley
This article continues on from part 1 of Helen’s series on the not so obvious in Excel 2003.
EVALUATE A STATEMENT
There are some hidden or undocumented functions you can use in Excel. For example, an old Excel 4.0 macro function called Evaluate can be used to calculate the result of a text expression such as 365*43 entered in an Excel cell. Information about the function is difficult to find but here, in a nutshell, is how to use it.
Begin by typing the text expression in a cell, for example into B1 you can type 365*43 and, in cell A1 type a label such as Expression to evaluate: so you know what is what. Now choose Insert, Name, Define and type the word answer in the Names in workbook area and in the Refers to area type =Evaluate($B$1) and click Add and then Ok. Now, in the cell where you want the answer to the calculation 365 * 43 to appear type =answer.
You can replace the expression in cell B1 with any valid expression of the type that Excel can calculate such as 9^3 to find the result of 9 cubed.
Why use this? For starters it is handy for teaching purposes – you can display a cell formula as text in one cell and the result in a neighboring cell knowing that the display text will always match the result.
DIFFERENCE BETWEEN DATES
Another function which is undocumented is Datedif which calculates the difference between two dates. So, for example, if you were born on the 18th September 1963 you can use this function to calculate how old you are in days, or months or years or a combination of these.
To test the function, type the dates that you want to find the difference between in cells B1 and C1 – the date in B1 should be earlier (older) than the date in C1. Into D1 type one of these functions:
=datedif(B1,C1,”d”)
Number of days between the two dates – “d” returns the difference in days.
=datedif(B1,C1,”m”)
Number of months between the two dates – “m” returns the difference in months.
=datedif(B1,C1,”y”)
Number of years between the two dates = “y” returns the difference in years.
=datedif(B1,C1,”y”)&” years “&datedif(B1,C1,”ym”)&” months”
Years and months between two dates
- “ym” returns the difference in months as if the two dates were in the same year – effectively disregarding the years.
There are two other entries you can use
- “yd” returns the difference in days as if the two dates were in the same year – effectively disregarding the years
- “md” returns the difference in days as if the two dates were in the same month – effectively disregarding both years and months.
You can replace C1 with today() in the formulas to calculate the difference between a start date and today’s date.
Using a combination of these it is possible to calculate the difference between two dates in any combination of days, months and/or years.
