Dave Bruns

Excel handles dates and times using a system in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date (June 1...Read more

Excel handles dates and time using a scheme in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date portion...Read more

In this example, the goal is to get the last day of the month based on any valid date. This problem can be solved most easily with the EOMONTH function. However, it can also be solved with the DATE function as explained below.

The EOMONTH function...Read more

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates. 

For example, in...Read more

In this example, the goal is to return a number that represents quarter (i.e. 1,2,3,4) for any given date. In other words, we want to return the quarter that the date resides in.

ROUNDUP formula solution

In the example shown, the formula in cell C5 is:...Read more

This formula takes advantage of the fact that dates are just sequential numbers in Excel. It determines the last day of the previous year and subtracts that value from the original date with this formula:

=B5-DATE(YEAR(B5),...Read more

In this example, the goal is to calculate the number of years between a start date in column B and an end date in column C. An easy way to solve this problem is to use the YEARFRAC function, which returns the number of years between any two dates as a decimal number.

YEARFRAC function...Read more

The DATE function creates a valid date using three arguments: year, month, and day:

=DATE(year,month,day)

In cell C6, we use the LEFT, MID, and RIGHT functions to extract each of these components from...Read more

In this example, the goal is to get the workbook path without the workbook name. For example, given a workbook called fruits.xlsx saved to:

C:\examples\fruits.xlsx

We want the path only like this:

...Read more

In this example, the goal is to return the name of the current worksheet (i.e. tab) in the current workbook with a formula. This is a simple problem in the latest version of Excel, which provides the TEXTAFTER function. In older versions of Excel, you...Read more