© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022
A. MurrayAdvanced Excel Formulashttps://doi.org/10.1007/978-1-4842-7125-4_6

6. Working with Dates and Time

Alan Murray1  
(1)
IPSWICH, UK
 

It is almost inevitable that you will work with dates and time in Excel, regardless of your job role or profession. Excel contains more than 20 awesome functions for you to perform the date and time calculations that you need.

This chapter begins with a guide to how dates and time work in Excel. It is very important that we understand the calendar system of Excel before we begin writing our formulas.

We then proceed into explanations of the most used date and time functions with examples using them to solve a myriad of typical business tasks.

Note

I am based in the UK, so all dates shown are entered in a dd/mm/yyyy format unless specified otherwise. I will often refer to dates using the month name, for example, 1st September 2021, to avoid any confusion with translation.

Understanding Dates and Time

File

dates-and-time.xlsx

Working with dates and time in Excel can certainly get interesting. To effectively work with the formulas of Excel, we must first understand how dates and time are stored.

Dates and the Calendar System in Excel

Dates in Excel are stored as numbers. These numbers are known as serial numbers. You will often see this term when using the functions of Excel. Figure 6-1 shows a reference to a serial number in the description of the WORKDAY function.
Figure 6-1

Serial number mentioned in the WORKDAY function description

When a date is entered into Excel, it is converted to a serial number based on the 1900 date system and formatted as a date. The 1900 date system begins on 1st January 1900. For Excel, this is the beginning of time. All dates are a sequential number beginning from this date.

So, 1st January 1900 is the serial number 1 and the date 2nd September 2021 is the serial number 44441. This is shown in Figure 6-2. The values are formatted in a general format to display the serial number.
Figure 6-2

The serial number of dates shown in Excel

Excel actually supports two different date systems, the 1900 date system and 1904 date system. The 1904 date system starts on 1st January 1904.

The 1900 date system is the default, so we do not need to concern ourselves with the 1904 date system, although we will see an example of its use later in this chapter.

Note

Windows and Mac Excel now have the same date system, but Excel 2008 for Mac and earlier Excel for Mac versions used the 1904 date system as the default.

Calculate the Difference Between Two Dates

As dates are stored as serial numbers, calculating the difference between two dates is a simple case of subtracting one from the other.

In Figure 6-3, the following formula is used to return the difference between the dates in cells A2 and B2:
=B2-A2
Figure 6-3

Calculating the difference between two dates

Days begin at midnight, so when calculating the date difference in this example, the day of 10/09/2021 (10th September 2021) is not included.

There is also a DAYS function in Excel that achieves this task. The DAYS function takes two arguments – the start date and the end date:
=DAYS(end_date, start_date)
This function performs the same calculation of end date – start date. Figure 6-4 shows the DAYS function returning the same result.
Figure 6-4

Date difference using the DAYS function

Note

If you enter the start and end dates in the incorrect order (start date first), you will receive a negative value as the result.

Time in Excel

So, a date in Excel is referred to as a serial number and represents the value of 1. Time is part of a day, so it is represented as a decimal value.

For example, the time 06:00 is 25% of a day, or the value 0.25 in Excel, while the time 14:00 is 0.58 (rounded to two decimal places).

Now, I do not recommend telling your colleagues in the office that the time is 0.58. You may find yourself excluded from the tea round (is that just a British thing?).

These values would be formatted as time so that they are understood. However, it is very important that we understand how time is stored in Excel when using formulas.

Figure 6-5 shows the general format of cells containing time and also date and time.
Figure 6-5

Time and date and time shown in a general format

The method for entering the time can depend on your regional settings. In the UK, the colon “:” is used to separate hours, minutes, and seconds, for example, 08:30 or 09:35:12.

Other delimiters such as using a period or dot “ . ” to separate the hours, minutes, and seconds would not be accepted in the UK regional settings. However, they may be allowed in other regions.

Calculate the Difference in Two Times

As with dates, the difference in two times can be calculated by simply subtracting the start time from the end time.

The following formula has been used to calculate the duration in Figure 6-6:
=B2-A2
We will see further calculations with time later in this chapter. This includes calculating time difference across dates and when the duration is over 24 hours.
Figure 6-6

Calculating the difference in two times

The TODAY and NOW Functions

Availability: All versions

The TODAY function in Excel returns the current date, while the NOW function returns the current date and time. The results of these formulas will update every time the worksheet calculates.

These functions are useful if you need to display the current date or date-time in a cell or perform calculations that require the current date or current date and time.

The syntax of these functions is super simple, as they require no arguments. The syntax for the TODAY function is
=TODAY()
And the syntax for the NOW function is
=NOW()

The functions will return the date and time information from the system clock of your computer. Figure 6-7 shows the TODAY and NOW functions returning values to a cell for display.

When using Excel for the Web , the date and time information is taken from the regional settings of where the workbook is located. Ensure your regional settings in OneDrive, the SharePoint site, or wherever the workbook may be stored are correct when using these functions.

Note

The current date can be entered quickly in Excel with the Ctrl + ; keyboard shortcut. This is a faster alternative to typing the date and does not update like the TODAY function does.

Figure 6-7

TODAY and NOW functions displaying values in a cell

The TODAY function is useful for calculations that require the current date, for example, calculating the number of days until a deadline or due date. The results would need to update with time.

The following formula is used in Figure 6-8 to calculate the number of days until the due date of a payment:
=F3-TODAY()
Figure 6-8

Calculating the number of days until a due date

Note

The result may be returned in a date format. Strange, but true. To correct this, format the cell in the General format.

Convert Text to Date

The method for entering dates into Excel depends on your regional settings. I am based in the UK, so entering the date delimited by a slash “/” or a hyphen “-” is accepted, for example, 04/09/2021 or 04-09-2021. These are entered in the dd/mm/yyyy format. The name of a month can also be used, for example, 04 September 2021.

I would not be able to enter a date in the structure 04.09.2021 (although it can be formatted that way). It would be stored as text. However, in regions such as Germany, that structure would be accepted.

In the modern world, with data coming from a large variety of sources, dates can appear in a workbook in all shapes and sizes. When your Excel cannot translate a date as a date, you will need to convert it. We cannot work with the data until it is cleaned.

There are two main functions for this, DATEVALUE and DATE.

Note

This is an Excel formulas book, so our focus is on formula solutions. They are often the perfect solution, as they are automated and can be combined with other functions. However, the Text to Columns and Power Query tools of Excel are also good options for converting date values.

The DATEVALUE Function

Availability: All versions

The purpose of the DATEVALUE function is to convert dates stored as text into dates. Or to be more specific, it converts them to their serial number, and then you would apply a date format to present them correctly.

The DATEVALUE function requires one argument – the date that is currently in a text format:
=DATEVALUE(date_text)
The following formula is used in Figure 6-9 to convert the text dates into serial numbers. A variety of date structures have been entered in column A to demonstrate the effectiveness of DATEVALUE in recognizing them.
=DATEVALUE(A2)
Figure 6-9

DATEVALUE converting text dates to serial numbers

If the year is omitted from the text date, then the DATEVALUE function will apply the current year to the converted date. Figure 6-10 shows some examples of the DATEVALUE function doing this.

Note that the serial numbers match those shown in Figure 6-9.
Figure 6-10

Converting text dates that are missing the year

Now, you can also convert the text dates by using them in a simple mathematical operation. The DATEVALUE function is unnecessary.

The following formula also achieves the required task (Figure 6-11):
=A2*1
Figure 6-11

Converting text dates with a multiply operation

I am demonstrating this to show what is possible and not to downplay the use of the DATEVALUE function . The better approach is probably to use DATEVALUE, especially if others are using your spreadsheets. It is much more descriptive to others who are trying to understand what that formula does.

The VALUE Function

Availability: All versions

We saw the VALUE function in the previous chapter converting text to numeric values. It is worth mentioning again here as it can also convert text to date but has the additional ability to also convert time.

In Figure 6-12, the DATEVALUE function has been used to try and convert text values into date and time values. However, you can see that DATEVALUE strips the time from the result.
Figure 6-12

DATEVALUE function removing time from the converted text values

This is good if you want the time removed. Otherwise, let’s turn to the VALUE function .

The following formula has been used to convert the text values into the serial number for date and time (Figure 6-13):
=VALUE(A2)
Figure 6-13

VALUE function converts text values to date and time

The cell values can then be formatted to present the date and time correctly. Excel does not have a date and time number category, so it requires finding or creating a custom number format:
  1. 1.

    Select the range to format.

     
  2. 2.

    Open the Format Cells window by pressing Ctrl + 1 or by right-clicking the selected range and clicking Format Cells.

     
  3. 3.
    Click the Custom category and then scroll through the list of formats to find a date and time format to use (Figure 6-14).
    Figure 6-14

    Formatting values as date and time

     
Performing a mathematical operation on the text values works again as an alternative method of converting them to date and time values. With this formula, Excel automatically applies a date and time format (Figure 6-15):
=A2*1
Figure 6-15

Converting text values to date and time easily

Note

Although I demonstrate the example of multiplying the text values by one, other mathematical operations that do not change the number’s value also work. For example, you could use A2/1, A2+0, or A2-0.

The DATE Function

Availability: All versions

The DATE function returns the serial number that represents a date from a given year, month, and day.

The syntax of the DATE function is
=DATE(year, month, day)

  • Year: A number that represents the year

  • Month: A number that represents the month

  • Day: A number that represents the day

The DATEVALUE and VALUE functions are great when the text value is structured as a date. However, that is not always the case.

Another common structure is to receive date values in the structure yyyymmdd. This may be stored as a text value or a numeric value. It does not matter; the DATE function along with some text functions from the previous chapter can fix this.

The following formula creates a date from the number values in column A (Figure 6-16). It uses the LEFT, MID, and RIGHT functions to extract the different parts of the number that correspond to the year, month, and day:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Figure 6-16

DATE function creating a date from a number

Another scenario could be that the day, month, and year values are in different columns, when you import data from another source. This is a perfect scenario for the DATE function.

The following formula is shown in Figure 6-17:
=DATE(F2,E2,D2)
Figure 6-17

Creating a date from three separate values

And for a final example, the date can sometimes form part of a reference . I have a client I have worked with for years who store booking references in the format “21-08-02 AM.” This represents 2nd August 2021 with only two digits used to represent the year.

The following formula can be used to extract the values and create the date from the booking reference (Figure 6-18):
=DATE(
"20"&LEFT(I2,2),MID(I2,4,2),MID(I2,LEN(I2)-4,2)
)

This formula concatenates the “20” to the two digits of the year to create the full year. Without this, it would likely convert 21 into 1921, instead of 2021.

The LEN function is used to help calculate the starting position of the day value.

Using these text functions to extract the different values of the date for the DATE function will remind you of the fun we had with text functions in the previous chapter.
Figure 6-18

Extract and create a date from a booking reference

So, the DATE function is a great function for converting values into a date. Another scenario for its use is to enter fixed dates into formulas. You cannot type dates directly into formulas due to Excel requiring the serial number.

In the following formula, the DATE function is used to enter a fixed date for the IF function condition (Figure 6-19):
=IF(A2<DATE(2021,9,1),2021,2022)
The formula displays the year 2021 if the cell values are earlier than 1st September 2021; otherwise, 2022 is displayed. It is common in areas such as academics, finances, and sporting seasons to have years that span different years.
Figure 6-19

Using DATE to enter a fixed date into a formula

Extract Date from a Date-Time Stamp

Often, when getting data from other sources such as a database, you will receive the date and time information.

There are several ways that you can extract the date from the date-time stamp if the time is not required.

We have already seen that the DATEVALUE function can achieve this with text values (Figure 6-12), but it wouldn’t work with values that are recognized as date and time. In this instance, the DATEVALUE function returns the #VALUE! error (Figure 6-20).
Figure 6-20

#VALUE! error returned by the DATEVALUE function

To extract the date only from date-time values, we can use the INT or TRUNC functions.

The INT Function

Availability: All versions

The INT function rounds a number down to the nearest integer. This is perfect for the task at hand, as the time is the decimal part of the value, and INT will effectively nullify that.

The INT function requires only one argument – the number that you want to round:
=INT(number)
In Figure 6-21, the INT function is used to round the date-time values down to the nearest integer or date. The cells require a date-only format to remove the time from the display of the values.
=INT(A2)
Figure 6-21

INT function extracting date from a date-time value

The TRUNC Function

Availability: All versions

The TRUNC function removes the decimal part of a number leaving only the integer. Once again, this is perfect for the task of removing the time from a date-time value.

The syntax for the TRUNC function has two arguments:
=TRUNC(number,[num_digits])

  • Number: The number to truncate.

  • [Num digits]: A number to specify the precision of truncation. If omitted, 0 is used to remove the decimal part of the number.

The TRUNC function does not round values like INT and other functions. It simply chops the numbers off at the specified precision.

In this scenario of extracting a date from a date-time stamp, it makes no difference if you use INT or TRUNC, the result is the same.

The following formula strips the time from the date-time values in column A (Figure 6-22). Once again, a date-only format would need to be applied to remove the display of the time.
=TRUNC(A2)
Figure 6-22

TRUNC function extracting date from a date-time value

Note

Although the official description of TRUNC is that it removes the decimal part of a number, this is only its default behavior.

For example, if you have a value of 728.91 in cell A2 and you use the formula =TRUNC(A2,-1), the value returned is 720 as it truncates one position to the left of the decimal. Or, if the formula was =TRUNC(A2,1), the value returned is 728.9 as it truncates to one position to the right of the decimal.

Extract Time from a Date-Time Stamp

To extract the time from a date-time stamp, we need to extract the decimal part of the value.

There are a few techniques for this. For example, we could subtract the extracted date part from the value (Figure 6-23). This would leave us with only the time element.
=A2-TRUNC(A2)
Figure 6-23

Extracting the time by subtracting the date element

Let’s look at how to extract the time element with the MOD function.

The MOD Function

Availability: All versions

The MOD function returns the remainder after dividing a number by a specified divisor. It requires two arguments – the number and the divisor:
=MOD(number, divisor)

  • Number: The number to divide

  • Divisor: The number that you want to divide the given number

In Figure 6-24, the MOD function is used to extract the time by dividing the date-time value by one:
=MOD(A2,1)
Figure 6-24

Extracting the time with the MOD function

After extracting the time using this method , you may need to format the cells to show the time only:
  1. 1.

    Select the cells to format.

     
  2. 2.

    Open the Format Cells window by right-clicking and click Format Cells or press Ctrl + 1.

     
  3. 3.

    On the Number tab, click Custom and then select the time format from the list (Figure 6-25). The Custom category has been used instead of time to specify only hours and minutes.

     
Note

The MOD function is a really cool function. I have personally utilized it over the years in several creative ways including to sum the values from every nth row.

Figure 6-25

Formatting the values to show time only in hours and minutes

The YEAR, MONTH, and DAY Functions

Availability: All versions

The YEAR, MONTH, and DAY functions are simple, yet very useful. They each extract their relevant part from a given date. Year will extract the year and so on.

Each function requires only the date in which to extract their part from. This could be a cell value, entered text, or the result of a function such as TODAY.
=YEAR(serial_number)

In Figure 6-26, the YEAR, MONTH, and DAY functions have each been used to extract their element from the dates in column A.

The formula to extract the year is shown in the image. The MONTH and DAY functions are written the same.
=YEAR(A2)
Figure 6-26

YEAR, MONTH, and DAY functions

This example demonstrates how the functions work but is not a hugely practical example. We will see these functions being used later in this chapter to help calculate financial years and quarters. This will provide a greater insight to their use.

Let’s see another quick example before we leave them though. These functions can be used to extract part of a date for another function to use, for example, the IF function.

In this example, we want to apply a different rate dependent upon the time of the year. Dates from September onward require a different rate to other times of the year.

The following formula (Figure 6-27) uses the MONTH function to extract the month of the date so that the IF function can test it and apply the required rate of 11% or 9%:
=IF(MONTH(F2>=9,11%,9%)
Figure 6-27

MONTH working with IF to test the month of a date

The NETWORKDAYS Function

Availability: All versions

The NETWORKDAYS function returns the number of whole workdays between two dates. Weekends (Saturday and Sunday) are excluded, and an argument is provided to specify additional non-working days that you want excluded.

This function is great for calculating the number of days worked on a task or project.

The following is the syntax of the NETWORKDAYS function:
=NETWORKDAYS(start_date, end_date, [holidays])

  • Start date: The start date to be used in the calculation.

  • End date: The end date to be used in the calculation.

  • [Holidays]: A range or array of dates that are considered non-working days. These could be national holidays or any other reason that the date is non-working. This is an optional argument.

It is important to note that the NETWORKDAYS function returns the number of whole workdays between two dates (Figure 6-28).
Figure 6-28

NETWORKDAYS counts whole workdays

Figure 6-29 shows the number of days difference between the two dates in cells G2 and H2. The formula in cell I2 is a simple =H2-G2 date difference calculation.

The NETWORKDAYS function in cell J2 returns a different result as both dates in the formula are working days, and NETWORKDAYS returns the number of whole workdays.
Figure 6-29

NETWORKDAYS returning a different result

Calculate the Working Days Difference Between Two Dates

Let’s see a practical example of the NETWORKDAYS function. In this example, we need to calculate the number of days worked on the tasks of a project.

The following formula is entered into column D (Figure 6-30). This formula omits the Holidays argument, so only weekend dates are excluded from the calculation:
=NETWORKDAYS(B2,C2)
Figure 6-30

Calculating working days difference with NETWORKDAYS

Let’s specify additional non-working days for the formula to exclude. In Figure 6-31, the additional non-working dates have been entered into range L2:L6 and have been named [rngHolidays].

The following formula is entered into column E. You can see the difference in the results of columns D and E when the additional non-working days are added.
=NETWORKDAYS(B2,C2,rngHolidays)
Figure 6-31

NETWORKDAYS with additional non-working days

The NETWORKDAYS.INTL Function

The NETWORKDAYS.INTL function performs the same role as NETWORKDAYS but with an additional argument to specify custom weekend parameters.

The following is the syntax of the NETWORKDAYS.INTL function. The custom weekend is the third argument:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Figure 6-32 shows the weekend options list provided by the weekend argument of NETWORKDAYS.INTL.
Figure 6-32

The weekend options in the NETWORKDAYS.INTL function

Let’s use the NETWORKDAYS.INTL function on the same range of dates and apply the Sunday only option for the weekend. This is known as option 11, as shown in Figure 6-32.

The following formula is used in column D to calculate the working days difference between two dates (Figure 6-33). It also uses the [rngHolidays] named range for additional non-working days to exclude:
=NETWORKDAYS.INTL(B2,C2,11,rngHolidays)
Figure 6-33

NETWORKDAYS.INTL with the Sunday only weekend option applied

Example 1: Customized NETWORKDAYS.INTL Function

The weekend argument of the NETWORKDAYS.INTL function can be customized to specify which days of the week are working and which are non-working. This provides much more flexibility in specifying non-working days than are offered by the weekend argument’s list of options.

The weekend argument can be entered as a text string of seven 1s and 0s. The first character represents a Monday and the other six for the remaining days of the week. A 1 is entered to indicate a non-working day, and a 0 is entered for a working day.

The following formula specifies that only Monday-Thursday are working days (Figure 6-34):
=NETWORKDAYS.INTL(B2,C2,"0000111")

With this customization of the NETWORKDAYS.INTL function, it is easy to specify that you may only work on a project on Mondays and Tuesdays ("0011111") or that you may work Saturday only ("1111101") on a specific task.

The holidays argument can still be used for additional non-working days such as state holidays or interference by bad weather.
Figure 6-34

Specifying a working week of Monday-Thursday

Example 2: Conditional Holiday Ranges

Creating customized weekend parameters and specifying holiday ranges is great for accurate working days difference calculation. However, you may require different parameters or holiday ranges dependent upon a location or an individual.

Take this example where we have two holiday ranges to cater for two different locations (Figure 6-35). Each range has a defined name assigned to it – [rngLocation1] and [rngLocation2].
Figure 6-35

Different non-working days by location

The following formula uses the SWITCH function (discussed in Chapter 2) to test the location in column B and provide the required holiday range (Figure 6-36):
=NETWORKDAYS(C2,D2,
SWITCH(B2,"Location 1",rngLocation1,"Location 2",rngLocation2)
)
Figure 6-36

Conditional holiday range with NETWORKDAYS

The SWITCH function can handle many more than two criteria if required. And this same approach can be used to apply a conditional weekend parameter with NETWORKDAYS.INTL (Figure 6-37):
=NETWORKDAYS.INTL(C2,D2,
SWITCH(B2,"Location 1","0100111","Location 2","1111000")
)
Figure 6-37

Conditional weekend parameters with NETWORKDAYS.INTL

The first example utilizes defined holiday ranges, and the second example works with entered strings to specify the working and non-working days.

For a final example, we imagine that the non-working days are entered in a single list and that there are more than just the two locations. Figure 6-38 shows this list formatted as a table named [tblNonWorking].
Figure 6-38

Table of non-working days by location

The following formula uses the NETWORKDAYS function along with a function named FILTER (Figure 6-39). The FILTER function filters the table to return only the non-working dates for the specified location.
Figure 6-39

FILTER function returning the non-working dates for NETWORKDAYS

Note

The FILTER function is covered in detail in Chapter 13. It is only available in Excel for Microsoft 365, Excel 2021, and Excel for the Web.

Example 3: Number of Fridays Between Two Dates

A unique use of the customized weekend parameter in NETWORKDAYS.INTL, shown in example 1, is to count the number of instances of a specific weekday between two dates. In this example, we will count the number of Fridays, but it could be any weekday or weekdays.

The following formula counts the occurrences of Fridays between the dates in cells A2 and B2 (Figure 6-40):
=NETWORKDAYS.INTL(A2,B2,"1111011")
Figure 6-40

Formula to count the number of Fridays between two dates

This can also be achieved using the weekend parameters provided with the NETWORKDAYS.INTL function. Option 16 is the Friday only option.

The formula in Figure 6-41 also returns the count of Fridays between two dates. A 1 is added at the end of the formula, as the NETWORKDAYS functions always return an extra day, due to their count of whole days:
=B2-A2-NETWORKDAYS.INTL(A2,B2,16)+1
Figure 6-41

Using the Friday only weekend option with NETWORKDAYS.INTL

The provided weekend options are limited. The text string offers more flexibility if you need multiple days, for example, Mondays and Thursdays between two dates.

The YEARFRAC Function

Availability: All versions

The YEARFRAC function returns the number of years and its fraction from a given start and end date. This can be very useful when calculating ages, proportions for annual benefits such as entitled leave, or financial interest between two dates.

It requires only the two dates with which to calculate the difference as a fraction. There is also an optional argument to specify the type of day count to use:
=YEARFRAC(start_date, end_date, [basis])

  • Start date: The start date for the calculation.

  • End date: The end date for the calculation.

  • [Basis]: A number that represents the day count basis (Figure 6-42). If omitted, 0 is used to apply the US (NASD) 30/360 basis. This means that each month of the year has 30 days.
    Figure 6-42

    Day count basis options in YEARFRAC

The YEARFRAC function is great for calculating the number of years between two dates. And a great example for this task is calculating the age of someone or something.

Excel does not have a worksheet function to calculate age. This is quite a surprising omission. However, YEARFRAC makes it easy.

In Figure 6-43, the following YEARFRAC function is entered in column C. It returns the number of years with decimals to represent the proportion of the year that has passed between the date in column B and today’s date:
=YEARFRAC(B2,TODAY(),1)

The day count basis has been specified as 1; this is the actual day count basis. So, it uses the actual number of days in each month. This is the most common basis to use. The other options apply for specific business uses.

The TRUNC function can be added to the formula to remove the decimal part of the value and display the number of years only (Figure 6-43):
=TRUNC(YEARFRAC(B2,TODAY(),1))
Figure 6-43

Calculating years of service with YEARFRAC

Equally, the fractional part of the result could have been used to calculate proportional interest.

The DATEDIF Function

Availability: All versions

The DATEDIF function calculates the difference between two dates in a specified unit such as years, months, or days.

The DATEDIF function is unique in that it is undocumented in Excel. When you type the DATEDIF function in Excel, no information is provided (Figure 6-44).

It remains for compatibility with older workbooks from Lotus 1-2-3. However, it can still come in useful.
Figure 6-44

DATEDIF function with no arguments showing

The syntax for the DATEDIF function is
=DATEDIF(start_date, end_date, unit)

  • Start date: The start date of the calculation.

  • End date: The end date of the calculation.

  • Unit: Letters that represent the unit difference that you want to calculate. The characters must be entered within double quotes.

The following lists the units and details the calculation performed by each unit entry.

  • “Y”: The difference in complete years

  • “M”: The difference in complete months

  • “D”: The difference in days

  • "MD": The difference in days, ignoring years and months

  • "YM": The difference in months, ignoring years and days

  • "YD": The difference in days, ignoring years

Figure 6-45 shows examples of DATEDIF performing the more common unit entries.

Excel does not have a function for months difference, so the calculation of months difference and months difference ignoring years is of special interest.

The following formula is entered into cell G2. Notice the result of 11. The difference between 10th March and 9th March is one day short of 12 months.
=DATEDIF(B2,C2,"ym")
Figure 6-45

DATEDIF function examples

Note

Microsoft does not recommend the use of the “MD” unit. This is because it can produce an inaccurate result when calculating the remaining days after the last completed month. It is also why an example is omitted from this book.

DATEDIF function results can be combined to calculate the date difference in a string such as the number of years and months.

The following formula combines the results of two DATEDIF functions (Figure 6-46), one for the years difference and another for months difference, ignoring years. Text is added to present the result nicely:
=DATEDIF(J2,K2,"y")&" years, "&DATEDIF(J2,K2,"ym")&" months"
An IF function or an alternative can be added to prevent the display of results such as the 0 months in cell L6.
Figure 6-46

Date difference in years and months

The WORKDAY Function

Availability: All versions

The WORKDAY function is used to return the date a given number of workdays in the future or before a specified date. This function is very useful for calculating due dates, such as estimated task completion dates or expected delivery dates.

The WORKDAY function works in a similar way to the NETWORKDAYS function that we covered earlier. Weekend dates are excluded from the calculation, and there is an optional Holidays argument, where a range of additional dates to exclude can be provided.
=WORKDAY(start_date, days, [holidays])

  • Start date: The start date for the calculation.

  • Days: The number of working days before or after the start date. Enter a negative value for days before the start date and a positive number for days after the start date.

  • [Holidays]: An optional argument that specifies additional non-working dates to be excluded from the calculation. They can be provided as a range of dates on a worksheet or an array returned by another function such as FILTER or XLOOKUP.

Let’s see some examples of the WORKDAY function in action.

Calculate a Due Date from a Starting Date

In this example, we have a list of tasks and their duration in working days. Based on this duration, we will use the WORKDAY function to calculate the estimated due dates.

In Figure 6-47, the following formula is entered into column D. This is a simple WORKDAY function to exclude weekends only from the calculation:
=WORKDAY(B2,C2)
Figure 6-47

Calculating estimated due dates for a list of tasks

Additional non-working dates can be specified by referencing a range containing the dates, as shown in Figure 6-48:
=WORKDAY(B2,C2,$F$2:$F$4)
Figure 6-48

WORKDAY function including a holidays range

Remember, the WORKDAY function can also return a date a specified number of working days in the past. To do this, a negative value would be entered for the Days argument.

Although a less common scenario, the WORKDAY function could be used to calculate the start date from a given end date and number of working days.

The WORKDAY.INTL Function

Along with the WORKDAY function , there is also a WORKDAY.INTL function. This is a more flexible alternative to WORKDAY.

It can be used to customize the weekend parameter using a list of predefined options or to specify the working days of a week by entering a string of seven 1s and 0s.

The syntax of WORKDAY.INTL is
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
The list of weekend options is the same as that provided by the NETWORKDAYS.INTL function (Figure 6-49).
Figure 6-49

Weekend options with WORKDAY.INTL

In the following formula (Figure 6-50), option 11 for Sunday only is specified. The additional range of dates supplied in range F2:F4 is also excluded from the calculation:
=WORKDAY.INTL(B2,C2,11,$F$2:$F$4)
Figure 6-50

NETWORKDAYS.INTL function used to specify Sunday only

You can go beyond the standard options provided with WORKDAY.INTL by entering a text string of seven 1s and 0s. This provides us with a way of specifying whatever working week we require.

The first day in the string is a Monday. A 1 indicates a non-working day, and a 0 is a working day.

In Figure 6-51, the formula specifies that only Tuesday, Wednesday, Thursday, and Friday are working days. The three dates in range F2:F4 are also excluded again:
=WORKDAY.INTL(B2,C2,"1000011",$F$2:$F$4)
Figure 6-51

Entering a text string for the weekend argument of WORKDAY.INTL

Note

The conditional holiday ranges and weekend options demonstrated in the section “The NETWORKDAYS Function” of this chapter can also be applied here to WORKDAY and WORKDAY.INTL.

Calculate First Working Day of a Month

It can be useful in some data analysis scenarios to calculate the next working day from specific date. In this example, we will return the first working day for the months of the year.

Figure 6-52 shows the names of the months in column H. However, each cell contains a date for the first day of each month; they are just formatted to show month name only.
Figure 6-52

Dates presented as a month name

The following formula uses the WORKDAY.INTL function to specify a working week of Monday-Thursday and adds one working day to the date returned by the DATE function (Figure 6-53):
=WORKDAY.INTL(DATE(YEAR(H2),MONTH(H2),0),1,"0000111")
The DATE function creates a date from the same year and month as the date in column H, but for day 0. The formula therefore returns the first working day for that month.
Figure 6-53

Calculating the first working day of a month

We will see how to calculate the last working day of a month, when we visit the EOMONTH function next.

EDATE and EOMONTH

Availability: All versions

The EDATE and EOMONTH functions return a date a specified number of months ahead or prior to an origin date. EDATE returns a date with the same day of the month as the origin date, and EOMONTH returns a date with the last day of the month.

Both functions require the same two arguments – the start date and the number of months to shift the date:
=EDATE(start_date, months)
or
=EOMONTH(start_date, months)

  • Start date: The start date for the calculation.

  • Months: The number of months after or before the start date that you would like to offset the date. Enter a positive number to return a future date and a negative number to return a past date.

Calculate Contract End Dates

These functions are perfect for calculating future end dates such as contract finish dates.

Use EDATE if the end date is the same day of the month as the start date and EOMONTH if you need the last day of the month for the end date.

In Figure 6-54, the following formula uses the EDATE function to return the date 12 months from the start date:
=EDATE(B2,12)
Figure 6-54

EDATE returning the date 12 months from the start date

And in Figure 6-55, the EOMONTH function has been used. This time, the contract is 18 months long, and notice that the EOMONTH function returns the last day of the month:
=EOMONTH(B2,18)
Figure 6-55

EOMONTH returning the contract end date in 18 months’ time

Last Day of a Month

Instead of calculating a date a specified number of months into the future, maybe you want to return the date of the last day of a given month.

The following formula uses a 0 for the months argument to return the date on the last day of the month for the return date in column F (Figure 6-56):
=EOMONTH(F2,0)
The following formula is entered in column H to calculate the number of days for the hired item. A 1 is added to the days difference as I wanted to include full days in the result:
=DAYS(G2,F2)+1
Figure 6-56

Calculating the last day of a given month

Number of Days in a Month

In a variation to the previous formula, maybe you do not want to return the date, but simply “how many days are in the month of a given date?”.

To do this, the DAY function is combined with EOMONTH (Figure 6-57):
=DAY(EOMONTH(J2,0))
Figure 6-57

Returning the number of days in a given month

Last Working Day of a Month

By using the WORKDAY function and the EOMONTH function together, we can calculate the last working day of a month.

The following formula (Figure 6-58) returns the first day of the following month by adding 1 to the EOMONTH function result. WORKDAY then returns the date of the previous working day by using –1 in its months argument:
=WORKDAY(EOMONTH(M2,0)+1,-1)
Figure 6-58

Calculating the last working day of a month

First Working Day of Next Month

Following the previous example, returning the first working day of the next month may appear straightforward. Once again, the WORKDAY and EOMONTH functions are combined.

EOMONTH returns the date for the last day of the given month, and the WORKDAY function then returns the first working day after that date (Figure 6-59):
=WORKDAY(EOMONTH(M2,0), 1)
Figure 6-59

Returning the first working day of the next month

Note

The last two examples using WORKDAY were based on a working week of Monday-Friday. Remember, the working week can easily be customized to our requirements.

Returning Week Numbers

A common task in Excel is to return the week number for the year (calendar or fiscal), a project, or some other timeline.

Excel provides a couple of functions to calculate week numbers for years. For other scenarios, we can combine functions or be a little creative with our formulas.

The WEEKNUM Function

Availability: All versions

The WEEKNUM function returns the week number in the year. By default, it begins with the week that contains 1st January, but it can also return the week number according to the ISO 8601 standard.

The syntax for the WEEKNUM function is
=WEEKNUM(serial_number, [return_type])

  • Serial number: The date for the calculation.

  • [Return type]: A number that determines which day the week begins. By default, the week begins on a Sunday.

There are two systems for the return type. These are shown in Figure 6-60.

  • System 1: The week containing 1st January is the first week of the year. You can specify on which day the week begins.

  • System 2: This system uses the methodology specified in ISO 8601. This is a universal standard for communicating dates internationally, removing any confusion. The week containing the first Thursday is week 1. Only return type 21 is used for this system.
    Figure 6-60

    Return type options with WEEKNUM

In Figure 6-61, the WEEKNUM function is using the default return type – week begins on a Sunday:
=WEEKNUM(A2)
The date of 4th January 2021 has been identified as week 2, because week 2 begins on Sunday 3rd January 2021.
Figure 6-61

WEEKNUM using the default return type

The following formula specifies that Friday is the first day of the week. This is return type 15 (Figure 6-62):
=WEEKNUM(A2,15)
In this example, 4th January 2021 is identified as being in week number 1 of the year. Week 2 does not begin until Friday 8th January 2021.
Figure 6-62

WEEKNUM with the start of a week set as a Friday

Let’s now see the WEEKNUM function applying the ISO standard for week numbering. The following formula specifies the return type of 21 (Figure 6-63):
=WEEKNUM(A2,21)

The ISO 8601 standard defines the first week of a year as the week that contains the first Thursday. An ISO week begins on a Monday.

4th January 2021 is recognized as the first date of the first week of the year. This is because the first Thursday is 7th January 2021, and 4th January 2021 is the beginning of that week by ISO standards.

1st January 2021 is recognized as being in week 53 of the previous year.
Figure 6-63

WEEKNUM using the ISO standard for week numbering

What Is ISOWEEKNUM?

The ISOWEEKNUM function returns the week number of the year as defined by the ISO standards for week numbering.

But didn’t we just accomplish this with the WEEKNUM function? Yes, we did. The ISOWEEKNUM function is redundant because WEEKNUM has option 21 that defines the use of the ISO standard.

However, I prefer the use of the ISOWEEKNUM function because its name is more descriptive than a function that defines option 21. Most people will not know what 21 means in the WEEKNUM function.

Figure 6-64 shows the ISOWEEKNUM function returning the week numbers as defined by the ISO 8601 standards:
=ISOWEEKNUM(A2)
Figure 6-64

ISOWEEKNUM function returning week numbers

Return the Week Number for Any Given Date

Instead of returning the week number in the year, you may want to calculate the week number in a project, fiscal cycle, or maternity period. For this, we can write a simple little formula.

In Figure 6-65, the following formula has been entered into cell J2 to return the number of weeks since the date in cell F2:
=TRUNC((I2-$F$2)/7+1)
Figure 6-65

Returning the week number from any given date

The formula first finds the difference between the two dates in days. This result is then divided by 7 to return the number of weeks. Finally, 1 is added to the result. The TRUNC function has been used to remove the decimal part of the value.

So, for the date of 17th June 2021, the difference between this and the start date is 9. When divided by 7, the result is 1.28. 1 is then added to get 2.28. And TRUNC strips of the decimals.

Week Number in a Month

Another scenario could be to return the week number in a month instead of years. The WEEKNUM, or ISOWEEKNUM, function can be used within a formula to calculate the week number within a month.

The following formula uses two WEEKNUM functions (Figure 6-66). Using the date in column L, the first returns the week number in the year, and the second returns the year’s week number for the first day of that month.

The latter is subtracted from the former, therefore nullifying the year leading up to the first of that month. 1 is added to return the week number in the month.
=WEEKNUM(L2,2)-
WEEKNUM(DATE(YEAR(L2),MONTH(L2),1),2)
+1

The WEEKNUM function is using return type 2. This defines Monday as the first day of the week.

For example, 4th October 2021 is week number 41, and the first of that month (1st October 2021) is week number 40. 41 – 40 equals 1, so a 1 is added to return week 2 of that month.
Figure 6-66

Returning the week number within a month

The WEEKDAY Function

Availability: All versions

The WEEKDAY function returns the day of the week of a given date. The day of the week is returned as an index number.

The function prompts for two arguments – the date and on which days a week begins and ends:
=WEEKDAY(serial_number, [return_type])

  • Serial number: The date of which to return the weekday.

  • [Return type]: A number that determines which day the week begins. By default, the week begins on a Sunday. So, Sunday = 1 and Saturday = 7.

The week can begin on any day of the week, and the list of options is provided as you type the formula (Figure 6-67).
Figure 6-67

Return type options available for the WEEKDAY function

In Figure 6-68, the WEEKDAY function is used to return the day of the week for the dates entered in column A. Return type 2 has been used. This specifies the start of the week as Monday, so Monday = 1 and Sunday = 7.
Figure 6-68

WEEKDAY function returning the day of week as a number

The dates in column A of Figure 6-68 have been formatted to show the day of week name so that we may see the accuracy of the formula easily. It is not necessary for the formula.

Rate of Pay Determined by Day of Week

Let’s now see a practical example of using the WEEKDAY function. In this example, we would like to apply a rate of pay that is dependent on the day of week when the hours were worked.

In this example, the staff will receive an increase in pay when they work on a Saturday or a Sunday. By setting the WEEKDAY function to return type 2, Monday = 1 and Sunday = 7, we can easily identify a Saturday or Sunday as their index number would be greater than 5.

The following formula (Figure 6-69) uses a simple IF function to test if the result of the WEEKDAY function is >5. It multiplies the hours worked by the Sat-Sun rate if it is, and multiplies the hours worked by the Mon-Fri rate if not:
=IF(WEEKDAY(D2,2)>5,E2*$I$3,E2*$I$2)
Figure 6-69

Applying a different rate of pay dependent on the day of week worked

Note

The WEEKDAY function is not required for tasks such as displaying the day of week name or sorting by day of week. The display of the weekday name in a cell can be achieved with some simple number formatting, and sorting by day of week in Excel is typically achieved using Custom Lists.

Calculating Financial Years and Quarters

For many countries, the financial year (also known as the fiscal year or business year) does not run from January-December, but from alternative start and end dates.

Let’s explore how we can calculate time periods such as financial years and quarters for some of the different financial years in operation in countries across the globe.

Fiscal Years

We will calculate the financial years for three different countries (India, Australia, and the UK) to get an understanding for the techniques involved. We will display the financial year as FY YYYY-YYYY, for example, FY 2021-2022.

Let’s dive into the formulas used for each calculation. The results for all three formulas are shown in Figure 6-70.

Indian Financial Year

The Indian financial year starts on 1st April and ends on 31st March. The following formula uses an IF function to test if the month of the date is later than March.

If it is, a text string is created from the current year to the next year. And if not, a text string is built from the previous year to the current year.
=IF(MONTH(A2)>3,
"FY "&YEAR(A2)&"-"&YEAR(A2)+1,
"FY "&YEAR(A2)-1&"-"&YEAR(A2)
)

Australian Financial Year

The Australian financial year runs from 1st July to 30th June of the following year.

This is similar to the Indian financial year, so requires only a simple adjustment. We will change the IF function to test if the month of the date is greater than 6, instead of the 3 we used for the Indian financial year.
=IF(MONTH(A2)>6,
"FY "&YEAR(A2)&"-"&YEAR(A2)+1,
"FY "&YEAR(A2)-1&"-"&YEAR(A2)
)

UK Financial Year

The financial year of the UK starts on 6th April and ends on 5th April. So, testing the month number of the date is not enough.

The following formula uses the DATE function to create a date from the year of the date being tested, month of April, and day six. If the date is later than 6th April of that year, then the financial year is that year to the following year. Otherwise, it is the previous year to the year of the date.
=IF(A2>DATE(YEAR(A2),4,5),
"FY "&YEAR(A2)&"-"&YEAR(A2)+1,
"FY "&YEAR(A2)-1&"-"&YEAR(A2)
)
Figure 6-70

Financial year formulas for India, Australia, and the UK

Fiscal Quarters

The simplest way to calculate quarters for different company financial years is to use the CHOOSE function in Excel. So, we will start with an example using the CHOOSE function and then demonstrate an alternative method.

CHOOSE returns a value from a list of values based on an index number. This makes it perfect for quarter calculation. We can extract the month number from a date and use that as the index value to return the required quarter from a list.

Note

The CHOOSE function is explained in detail in Chapter 11.

Let’s look at two different fiscal quarter formulas using CHOOSE. The results of both formulas are shown in Figure 6-71.

This first formula calculates the quarters for a year beginning on 1st April. The MONTH function returns the month number of the date in F2. CHOOSE then returns the number from its list that matches the month number. The string "Q" is added as a prefix to the quarter number:
="Q"&CHOOSE(MONTH(F2),4,4,4,1,1,1,2,2,2,3,3,3)
This second formula calculates the quarters for a year beginning on 1st October:
="Q"&CHOOSE(MONTH(F2),2,2,2,3,3,3,4,4,4,1,1,1)
Figure 6-71

Fiscal quarter calculations with CHOOSE

In this second method, shown in Figure 6-72, the following formula uses a combination of the EDATE and CEILING functions along with a few mathematical operations. This first formula returns the fiscal quarter for a year beginning in April:
="Q"&CEILING(MONTH(EDATE(F2,-3)),3)/3
And the following formula returns the fiscal quarter for a year beginning in October:
="Q"&CEILING(MONTH(EDATE(F2,-9)),3)/3
Let’s dive into how these formulas work.
Figure 6-72

Fiscal quarter calculations with CEILING and EDATE

The EDATE function is used to offset the date a specified number of months. The number of months to offset is dependent upon the first month of the fiscal year.

The number of months to offset is one less than the first month number of the fiscal year. If the year starts in April (4), then EDATE returns the date three months prior. And if the year starts in October (10), then EDATE returns the date nine months prior.

The MONTH function extracts the month number from the date returned by EDATE. The purpose of the CEILING function is to round a number up to a given multiple. In these formulas, CEILING rounds the month number up to the multiple of 3. And this result is divided by 3 to return the fiscal quarter.

Let’s see an example in action. We have the date of 1st July 2021 in cell F4. When returning the fiscal quarter for a year beginning in April, the EDATE function returns the date three months prior, so 1st April 2021 is returned. MONTH returns number 4 and CEILING rounds this up to a multiple of 3, which is 6. Then 6 divided by 3 is 2. The result is Q2.

For the same date but the fiscal year beginning in the month of October, EDATE returns the date nine months prior to 1st July 2021. The result is 1st October 2020. MONTH extracts the number 10, and CEILING rounds this value up to 12 (multiple of 3). 12 divided by 3 is 4, so the result is Q4.

An advantage of this formula over CHOOSE is that the number of months to offset by EDATE could be linked to a cell and be made more dynamic. It is nice to have different formula options. You can choose
your preference.
Note

The CEILING function is used for the rounding task in these formulas. This function is compatible with all Excel versions. In a formula example shortly, the CEILING.MATH function (an updated version of CEILING) will be used.

Working with Time

At the beginning of this chapter, we defined how time is stored in Excel and how it is structured. We also performed some simple calculations to find the difference between two times.

Let’s now dive into further important techniques to know when working with time in Excel.

Convert Text to Time

When receiving data from different sources, data does not always come in the format that we require.

To perform calculations and analysis on time data, it is imperative that Excel recognizes it as time. There are two main functions in Excel to convert text values into correct time values – TIMEVALUE and TIME.

The TIMEVALUE Function

Availability: All versions

The TIMEVALUE function returns the time that is represented by text. It is quite simply the time alternative to the DATEVALUE function discussed earlier.

It requires only one argument, and that is the text representation of time that needs to be converted:
=TIMEVALUE(time_text)
In Figure 6-73, the following formula is used to convert the different text representations of time. Notice that TIMEVALUE only returns the time part of the date-time value in cell A5.
=TIMEVALUE(A2)
The values are displayed in their raw state – a decimal value representing the time of day. They need to be formatted as time to become readable.
Figure 6-73

TIMEVALUE function converting text to time

When formatting time, the standard time formatting options all assume that you want to present time to the granularity of seconds (Figure 6-74).
Figure 6-74

Time formatting options all include seconds

If you want a time format of hours and minutes only, specify a custom format (Figure 6-75).
Figure 6-75

Formatting time to show hours and minutes only

The results of the TIMEVALUE function are now exactly as we expected them (Figure 6-76).
Figure 6-76

TIMEVALUE results correctly formatted

The TIMEVALUE function can only convert time that is stored as text. If the value you ask it to convert is not text, the #VALUE! error is returned.

Times stored as text can also be converted to time by using the VALUE function or by using a simple mathematical calculation that does not change the cell value.

Figure 6-77 shows the VALUE function converting the same text values. Notice that the VALUE function returns the date and time from the date-time value in cell A5. TIMEVALUE only returned the time. The better method depends on whether you want the date included or not.
=VALUE(A2)
Figure 6-77

VALUE function converting text values into time

In Figure 6-78, the same conversion has been achieved using the following formula. Any mathematical calculation that does not change the cell value can be used to convert text to number.
=A2+0
Figure 6-78

Simple mathematical operation to convert text to time

The TIME Function

Availability: All versions

The TIME function returns the decimal value that represents a time from a given hour, minute, and second.

This function is ideal when you need to convert to time from its different parts. For example, the hour, minute, and second may be in different columns on a sheet or extracted by different text functions from a value.

The TIME function requires three arguments – hour, minute, and second. All arguments are mandatory:
=TIME(hour, minute, second)

If the cell that the TIME result is returned to is formatted as General, a time format is automatically applied. This format can be changed or applied in advance.

In the first example of the TIME function (Figure 6-79), the hours and minutes in the text values are separated by a period, or dot. My UK regional settings do not recognize these values as a legitimate time format; therefore, the TIMEVALUE function would not convert them. It produces the #VALUE! error instead.

The following formula uses the LEFT and RIGHT functions to extract the hour and minute from the value. These are passed to the TIME function to create the time value.

The second is a mandatory argument, so a 0 is entered. Although it is not shown in the initial value or the result, the TIME function demands it.
=TIME(LEFT(D2,2),RIGHT(D2,2),0)
Figure 6-79

TIME function converting time stored as text

Although this section is about converting text to time, it is worthwhile seeing an example of time being stored as an incorrect numeric value.

In this example, a spreadsheet has been generated from an export, and the time values contain the period or dot separator again. However, this time, my regional settings have forced Excel to assume that the dot is the decimal separator.

Because of this, they have been stored as incorrect numeric values. The following formula has been used to convert them to the correct time (Figure 6-80):
=TIME(
   LEFT(G2,FIND(".",G2)-1),
   IF(
       LEN(RIGHT(G2,LEN(G2)-FIND(".",G2)))=1,
       RIGHT(G2,LEN(G2)-FIND(".",G2))&"0",
       RIGHT(G2,LEN(G2)-FIND(".",G2))),
   0)
The number of digits for the hour and minute is irregular, so the FIND and LEN functions are used to help calculate the number of digits in each part of the time value.
Figure 6-80

TIME fixing incorrect numeric values

Note

Text functions such as FIND and LEN are covered in detail in Chapter 5.

For the minute argument of TIME, an IF function is nested to test if the number of characters representing the minute is only one character, for example, 14.5. If it is, then a “0” is appended to the minute string. So, 14.5 would become 14.50; otherwise, instead of being converted to 14:50, it would become 14:05.

In this formula, the calculation to return the minute is used three times. In Chapter 15, we will cover the LET function. This function helps us to create more efficient and meaningful formulas, and this formula is a good use case for LET (available in Excel for Microsoft 365, Excel 2021, and Excel for the Web versions only).

The following is an adapted version of the formula including the LET function (Figure 6-81). The hour and minute calculations are stored as variables named hour and minute and then used in the final calculation:
=LET(
hour,LEFT(G2,FIND(".",G2)-1),
minute,RIGHT(G2,LEN(G2)-FIND(".",G2)),
TIME(hour,IF(LEN(minute)=1,minute&"0",minute),0)
)
Figure 6-81

Convert decimal number to time including the LET function

Note

The TEXTBEFORE and TEXTAFTER functions, available in modern Excel and covered in Chapter 5, could also have been used to simplify the formula. However, the original formula is compatible for all Excel versions.

For the final TIME function example, we see another common representation of time, which is to display the hours and minutes without any separator (Figure 6-82). The values are stored by text in Excel and need converting.

The following formula is the same as the first TIME formula example. Once again, it achieves the objective of converting the text values to time:
=TIME(LEFT(J2,2),RIGHT(J2,2),0)
Figure 6-82

Converting text values with TIME

Rounding Time

There may be situations when you need to round time values. Time can be rounded to any multiple of significance – hour, 30 minutes, 15 minutes, etc.

There are three key functions for rounding time: MROUND, CEILING.MATH, and FLOOR.MATH. Let’s quickly cover how each of these functions operates before we see them in practice rounding time.

MROUND, CEILING.MATH, and FLOOR.MATH

The MROUND, CEILING.MATH, and FLOOR.MATH functions all round values to a multiple of significance. The function you use depends on how you wish to round.

The MROUND function rounds up or down to the nearest multiple. It requires the number to round and the multiple to round to:
=MROUND(number, multiple)

The CEILING.MATH function always rounds up to the nearest multiple. It also requires the number to round and the multiple of significance.

There is also an optional Mode argument to specify how to round negative values. Because time is rarely negative, we will omit this argument from these examples.
=CEILING.MATH(number, [significance], [mode])
The FLOOR.MATH function always rounds down to the nearest multiple. Its syntax is the same as CEILING.MATH:
=FLOOR.MATH(number, [significance], [mode])
Note

The CEILING.MATH and FLOOR.MATH functions were first released with Excel 2013 as improved functions to their predecessors – CEILING and FLOOR. If you use a version before Excel 2013, the CEILING and FLOOR functions work perfectly well too.

Round Time to the Nearest Hour

In Figure 6-83, the three functions have each been used to round time to the nearest hour.

The following MROUND function is used in column B to round time up or down to the nearest hour:
=MROUND(A2,"1:00")
The CEILING.MATH function is entered in column C to force the value to round up to the next hour:
=CEILING.MATH(A2,"1:00")
The FLOOR.MATH function is entered in column D to force the value to round down to the previous hour:
=FLOOR.MATH(A2,"1:00")
Figure 6-83

Rounding time to the nearest hour

Round Time to the Nearest 15 Minutes

Figure 6-84 shows further examples of these three functions rounding time values. In these examples, time is rounded to the nearest 15 minutes.

The following MROUND function is entered in column B to round up or down to the nearest 15 minutes:
=MROUND(A2,"00:15")
The CEILING.MATH function is used in column C to round up to the next 15 minutes:
=CEILING.MATH(A2,"00:15")
The FLOOR.MATH function is used in column D to round down to the nearest multiple of 15 minutes:
=FLOOR.MATH(A2,"00:15")
Figure 6-84

Rounding time to the nearest 15 minutes

Time Difference Past Midnight

Calculating the difference between two times in Excel is actually very simple. It is the formatting of the results that can be frustrating.

When the time difference is over 24 hours, the formatting can be even more tricky. In Figure 6-85, the results of the formula =G2-F2 are shown in column H.

These results are correct, but they need to be presented more effectively.
Figure 6-85

Time difference in General format

Some of the durations span multiple days, so let’s format the cell values to be presented in the format “x days, x hours,” for example, 2 days, 13 hours.
  1. 1.

    Select the range H2:H6 and press Ctrl + 1 to open the Format Cells window.

     
  2. 2.

    On the Number tab, click the Custom category.

     
  3. 3.

    Type the following into the Type field (Figure 6-86):

    d "days," h "hours"
     
  4. 4.
    Click OK.
    Figure 6-86

    Applying custom formatting to show days and hours

     

The duration values are now more legible (Figure 6-87). Custom formatting is an incredibly powerful tool in Excel, and it can be used to format values exactly how we want.

In this example, the results are shown in days and hours. You could also show minutes if required or the results as just the total hours, which we will look at next.
Figure 6-87

Time difference formatted in days and hours

You may notice in the duration results that three of the five results do not exceed 24 hours.

So, let’s set a condition in our formatting rule to format the results that are over 24 hours in the format “x days, x hours” and results less than 24 hours in the format “x hours.”

Select the range H2:H6 and open Format Cells to edit the existing custom number formatting code.

Replace the code with the following number formatting code (Figure 6-88). The condition is enclosed in square brackets [>1], and a semicolon is used to separate the different formats:
[>1] d "days," h "hours";h "hours"
Figure 6-88

Setting a condition in your custom number formatting

The results of this conditional number formatting rule are shown in Figure 6-89. This is a cool technique demonstrating a little of what is possible with custom number formatting.
Figure 6-89

Formatting to display hours only when less than 24 hours

Sum Hours over 24 Hours

It is very common to display a result as total hours, even if it spans multiple days. After all, what is a day? Is a day 24 hours? Or is it 8 or 9 hours such as a typical business day? It is open to interpretation.

In Figure 6-90, the following SUM formula is entered into cell H8. Excel has automatically applied the same formatting rule that is used on the cells used by the SUM formula:
=SUM(H2:H6)
This is clever and a useful format. However, it is not what we wanted in this example.
Figure 6-90

Sum of total hours with the same days and hours formatting

There is a special formatting trick to display the total hours that exceed 24 hours in Excel. That trick is to enclose the “h” within square brackets.

An “h” without square brackets will only display the remaining hours past a multiple of 24 hours.

Let’s format all the duration values to show total hours only:
  1. 1.

    Select range H2:H6 and cell H8 and press Ctrl + 1 to open the Format Cells window.

     
  2. 2.

    On the Number tab, click the Custom category.

     
  3. 3.

    Type the following into the Type field:

    [h] "hours"
     
  4. 4.

    Click OK.

     
Figure 6-91 shows the total hours displayed in all cells.
Figure 6-91

Formatting time to show hours over 24

Handling Negative Time

Excel cannot display negative time, so this can create a problem if you need them. Fortunately, there are always workarounds for problems such as this.

In Figure 6-92, we have the London Marathon finish times for the men’s category for the last six years. In column D, a formula is calculating the time difference compared to the previous year.

Every year when the finish time is faster than the previous year, a negative time difference is returned, and Excel cannot display the time value, displaying ##### instead.
Figure 6-92

Negative times cannot be displayed in Excel

Let’s look at two methods to display negative time in Excel.

The first method is to switch the Excel workbook from its 1900 date system to the 1904 date system.

This will fix the negative dates; however, be careful, because changing the date system will change any existing dates in the workbook. The dates will be adjusted by four years.

In this example, we do not have any dates, so it is not an issue:
  1. 1.

    Click File ➤ Options ➤ Advanced.

     
  2. 2.

    In the When calculating this workbook section, check the Use 1904 date system box (Figure 6-93).

     
  3. 3.
    Click OK.
    Figure 6-93

    Switching to the 1904 date system

     

The negative times are now displayed correctly (Figure 6-94). If you notice this causes a problem with dates on your spreadsheet, you can easily come back to the Excel options and turn off the 1904 date system.

The results are also still numeric values. This is useful if you need to perform further calculations on them. The next approach will convert them to text to display them correctly.

Further formatting of the time values could be applied to hide the hours and display the negative values in a different color to highlight them. However, this is not the focus of this task, so let’s move on to the second method.
Figure 6-94

Negative times displayed correctly in Excel

The second method uses the TEXT function (covered in Chapter 5) to display the negative time values as text, but in a time format.

We will also take this opportunity to hide the hours from the results, as a marathon finish time will never be more than 60 minutes improved on a previous year.

In Figure 6-95, the following formula is entered in cell D3. An IF function tests if the result is positive and performs the required TEXT function based on the result:
=IF(C3-C2>=0,
TEXT(C3-C2,"mm:ss"),
TEXT(ABS(C3-C2),"-mm:ss")
)
The ABS function removes the sign from the result of C3-C2. So, a negative value is converted to positive for the TEXT function to use.
Figure 6-95

Formula to display negative time values

Date and Time Functions with Other Excel Features

Let’s now see a few practical examples of date and time functions being used with other Excel features.

Highlight Approaching Due Dates

A common requirement in Excel is to track due dates. These could be payments that are due, contracts expiring, or deadlines of project tasks.

With the array of date functions available in Excel, we can set almost any conditions we may need. Let’s see a few examples.

Figure 6-96 shows a simple list that contains some due dates.
Figure 6-96

Due dates to be highlighted

We would like to highlight the dates that are due within the next ten working days. For this, we could use the WORKDAY function in a Conditional Formatting rule:
  1. 1.

    Select range B2:B7.

     
  2. 2.

    Click Home ➤ Conditional Formatting ➤ Highlight Cells Rules ➤ Less Than.

     
  3. 3.

    Enter the following formula into the Format cells that are LESS THAN: box (Figure 6-97):

    =WORKDAY(TODAY(),10)
    Figure 6-97

    Rule to format dates due within the next ten working days

     
This formula is returning the date ten working days from today’s date. This is a simple example that only excludes weekend dates from its calculation. We saw more advanced uses of WORKDAY earlier in this chapter.
  1. 4.

    Specify the cell formatting from the list and click OK.

     
Figure 6-98 shows two dates being identified as due within the next ten working days.
Figure 6-98

Two dates identified as being due soon

Note

You will see different dates and possibly different results to what are shown in the images. The dates are calculated by a formula to remain current, and the results of WORKDAY are based on the TODAY function and so are determined by the date that you read and practice this.

You can add multiple Conditional Formatting rules to cells, so let’s add another rule to highlight due dates that occur between the next 10 and 20 working days:
  1. 1.

    Select range B2:B7.

     
  2. 2.

    Click Home ➤ Conditional Formatting ➤ Highlight Cells Rules ➤ Between.

     
  3. 3.

    Enter the following formulas into the two Format cells that are BETWEEN: boxes (Figure 6-99):

    =WORKDAY(TODAY(),10)
    and
    =WORKDAY(TODAY(),20)
    Figure 6-99

    Rule for dates due between the next 10 and 20 working days

     

Unfortunately, this window is not resizable, so the full formulas could not be shown in the image.

Note

When editing formulas in Excel windows, be careful of the active cell mode. Using the cursor arrows on the keyboard while in “Point” mode causes chaos in your formula. The different cell modes of Excel were explained in Chapter 1.

  1. 4.

    Specify the cell formatting from the list and click OK.

     
Figure 6-100 shows two rules active in the range of due dates. Dates within the next 10 working days and dates within the next 20 working days are both being highlighted independently.
Figure 6-100

Two formatting rules highlighting due dates

Highlight Dates Due Within the Next Month

Let’s add one more quick Conditional Formatting example, as an opportunity to recap on another function we saw in this chapter. That function is EDATE.

In this example, we want to highlight the dates due within the next month. This is often done by testing if a date is within the next 30 days. And this method is often sufficient, because we just want to bring the date to our attention and the result does not need to be that precise.

However, by using the EDATE function, we can test if a date is within the next month more accurately:
  1. 1.

    Select range B2:B7.

     
  2. 2.

    Click Home ➤ Conditional Formatting ➤ Highlight Cells Rules ➤ Less Than.

     
  3. 3.

    Enter the following formula into the Format cells that are LESS THAN: box (Figure 6-101):

    =EDATE(TODAY(),1)
    Figure 6-101

    Rule to format due dates within the next month

     
The EDATE function returns the date in exactly one month’s time. Remember, the EDATE function can also return previous dates if you needed to highlight dates with the last month, last three months, etc.
  1. 4.

    Specify the cell formatting from the list and click OK.

     
Figure 6-102 shows all except one date in the list occur within the next month.
Figure 6-102

All except one date occur within the next month

Data Validation Rules – Prevent Weekend Entries

As an example of date functions being used in Data Validation rules, let’s prevent the entry of weekend dates in a cell.

In this example, we will assume the weekend to be Saturday and Sunday; however, this can easily be customized for any day(s) of the week.

The WEEKDAY function is perfect for this task. We will use the WEEKDAY function to identify the day of week of the date entered and use return type 2, Monday = 1 to Sunday = 7. We will test that the index of the day of week entered is less than 6 (before Saturday):
  1. 1.

    Select the range that you want to apply the Data Validation rule to. Range E2:E6 has been used for this example.

     
  2. 2.

    Click Data ➤ Data Validation.

     
  3. 3.

    Click the Settings tab of the window.

     
  4. 4.

    Click the Allow drop-down and select Custom.

     
  5. 5.

    Enter the following formula into the box provided (Figure 6-103):

     
=WEEKDAY(E2,2)<6
In the formula, cell E2 has been used. This must be the first cell of the selected range.
Figure 6-103

Validation rule to prevent entry of weekend dates

  1. 6.

    Click the Error Alert tab and enter a meaningful Title and Error message to appear if a user enters a date that is a Saturday or Sunday (Figure 6-104).

     
  2. 7.
    Click OK.
    Figure 6-104

    Customized error alert for users

     
The Data Validation rule is set. In Figure 6-105, the error alert is shown because 24th October 2021 is a Sunday.
Figure 6-105

Invalid date entry being rejected by the validation rule

Summary

In this chapter, we learned the most useful date and time functions in Excel supported with numerous examples of their application in the “real world.”

The chapter began with a detailed explanation of how dates and times are stored in Excel and how to effectively enter, convert, and extract dates and times. This topic is often misunderstood by Excel users, yet it is so critical to the performance of your calculations.

We then embarked on a tour of many different functions including WORKDAY, NETWORKDAYS.INTL, EDATE, and TIME. We performed many formulas including to find working days, return fiscal months and quarters, and return week numbers in a year or from a specific start date.

This chapter was very comprehensive and should act as a guide to refer to whenever you need a particular date or time calculation.

In the next chapter, we will learn the VLOOKUP function in Excel. This notorious function is extremely useful and is one of the most commonly used functions in Excel. Because of this, an entire chapter is dedicated to it.

You will learn how to use the VLOOKUP function including some insider tricks to make it more powerful, flexible, and dynamic. You will also understand common mistakes that users make and should be avoided.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.116.36.192