Excel Formulas Archives • My Online Training Hub

Relative Named Ranges

April 4, 2018 by Mynda Treacy 12 Comments

Excel Named Ranges is a vast topic that includes some simple techniques that we all can and should use to make our spreadsheets easier to build and maintain. Plus, some more advanced techniques like relative named ranges, which are good to know. Especially for that occasion when you inherit a workbook from an Excel Superuser […]

Excel ADDRESS Function

March 22, 2018 by Mynda Treacy 4 Comments

The Excel ADDRESS Function returns a cell address for specified row and column coordinates. Optional arguments allow you to specify the style of address (A1 or R1C1), the sheet name it refers to and whether the reference is absolute or relative. Excel ADDRESS Function Syntax Syntax: =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Note: Arguments in square […]

Extract Date or Time from a Date and Time

October 25, 2017 by Mynda Treacy Leave a Comment

We often need to extract the date or time portion from a date-time serial number for use in other functions. We can use the INT function to extract the date portion, and the MOD function to extract the time portion. See examples below. The INT function returns the integer or whole number portion of the […]

Excel MOD Function

October 25, 2017 by Mynda Treacy Leave a Comment

The Excel MOD Function is probably one of the most confusing to understand, unless you’re a math whizz, which I am not. After all this is why I use Excel. It’s my giant calculator of choice. Anyhow, just because I find MOD confusing, doesn’t mean it’s not one of the most useful and versatile functions […]

Excel DATE Function

October 25, 2017 by Mynda Treacy Leave a Comment

The Excel DATE function combines separate year, month and day values and converts them into a date serial number formatted as a date. Excel DATE Function Syntax Syntax: =DATE(year, month, day) year The value of the year argument can include one to four digits from 1900 to 9999. month A positive or negative integer representing […]

Convert Month Names to Numbers

October 25, 2017 by Mynda Treacy Leave a Comment

Often, we’ll have a list of month names that we want to convert to a date, or even just convert month names to numbers. We can use the MONTH function to do this; see examples below: How it works: By concatenating a 1 to the text month name in column B we provide the MONTH […]

Convert Dates to Text

October 24, 2017 by Mynda Treacy Leave a Comment

If you want to use a date in a chart label, or you want to concatenate a date with some other text, then you’ll want to convert dates to text first to ensure it displays correctly. We can use the TEXT function for this. The second argument of the text function allows us to specify […]

Excel NETWORKDAYS.INTL Function

October 19, 2017 by Mynda Treacy 2 Comments

The Excel NETWORKDAYS.INTL function (new in Excel 2010) returns the number of working days between two date serial numbers, excluding weekends and holidays. Weekend days can be specified. NETWORKDAYS.INTL function syntax: =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) start_date A date serial number or text end_date A date serial number or text [weekend] This optional argument allows you […]

Highlight Cells Referenced in Excel Formulas

September 19, 2017 by Mynda Treacy 6 Comments

In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells contributed to a result, like this: However, in financial modelling and reporting it’s often less obvious, like the example below: Let’s look at some ways we can easily identify which cells contribute to a result. […]

Excel Convert Dates to Fiscal Quarters and Years

July 27, 2017 by Mynda Treacy 8 Comments

If your company’s fiscal year runs in line with the calendar year then it’s easy to classify your dates into quarters and years for reporting. Convert dates to quarters with this formula (where cell A2 contains your date): =ROUNDUP(MONTH(A2)/3,0) And if you want to prefix it with ‘Q’, then use this formula: =”Q”&ROUNDUP(MONTH(A2)/3,0) Convert dates […]

Relative Named Ranges

Excel ADDRESS Function

Extract Date or Time from a Date and Time

Excel MOD Function

Excel DATE Function

Convert Month Names to Numbers

Convert Dates to Text

Excel NETWORKDAYS.INTL Function

Highlight Cells Referenced in Excel Formulas

Excel Convert Dates to Fiscal Quarters and Years

Other Info

Blog Categories

Resources

Affiliate Program