In honor of the 40th anniversary of J.K. Rowling’s seminal classic, Harry Potter, this post magically exposes a hidden excel function!
Ever wonder if your spreadsheet is keeping something from you?
DATEDIF is an under-documented function. It allows you to calculate the difference between two dates. Although it doesn’t appear in the Function Wizard (it exists to insure backward compatibility with Lotus and earlier Excel versions) it is a powerhouse when it comes to calculating date differences.
Using DATEDIF function:
Format: =DATEDIF ( date1, date2, “interval”)
date1must be the earlier of the two dates or an #NUM error is returned.
Ever wonder what your birthday looks like expressed in roman numerals? Want to display your travel expenses using Thai?
Use these functions to convert numbers to their respective lingo.
Converts number to Thai text
สี่ร้อยห้าสิบเอ็ดบาทถ้วน (Four Hundred and Fifty-one)
Convert Roman Numeral to Arabic
Convert Number to Roman Numeral
=ROMAN(1984 ,  )*
*ROMAN, has an optional argument, format . When format is included, the number is expressed differently, ranging from classic (0) to simplest (4). When this argument is omitted, the classical format is displayed.
Excel is great at splitting text across multiple columns. For basic text separation (e.g. putting first and last name in separate columns) the LEFT and RIGHT functions work fine. But these functions fall short when applied to data that follow an inconsistent pattern. In the below example, the errant inclusion of a middle name wrecks havoc with our formula results.
Using the SUBSTITUTE function one can identify and replace the last space in the cell with a uniquely identifying character (e.g., an underscore). This character can then be used as a delimiter to separate Lastname from full name.
SUBSTITUTE: replace character(s) within a cell with specified character(s)
= SUBSTITUTE ( text, old text, new text, instance)
Note, instance is optional; when omitted, every occurrence of old text is replaced with the new.
Linking the worksheet name to a cell in the spreadsheet is easily accomplished using Excel’s CELL function. Once joined by the MID and SEARCH functions, you need only change the sheet’s name and the linked cell will update to match.
Displaying the Sheet Name in a Cell
Type (or copy and paste) the following formula into a cell