It is quite common to perform date-related calculations when we work with Excel. Before Power Query, we must do it with built-in functions. However, we may not get the results we need with a single function most of the time. In fact, a combination of functions as well as simple math are required. With Power Query, we can perform many date-related calculations via User Interface of Power Query Editor. In this blog post, I am going to show you both the Power Query way (in video) and the Excel formula way (text).

You may download the sample file to follow along.

Let’s begin with Power Query. See how easy it is in the following video:

Want to know the Excel formula equivalent? Keep reading.

Age (Duration)

Age = TODAY()- D19

Total Years = (TODAY() - D19) / 365

Total Days = TODAY() - D19

Total Hours = (TODAY()-D19) * 24 

Total Minutes = (TODAY() - D19) * 24 * 60

Total Seconds = (TODAY()-D19) * 24 * 60 *60

where D19 is the date to compare to (applicable to the rest of the formula stated below)

This section is quite straight-forward. It calculates the number of days between two dates, and then divides it by 365 to get the total years; multiplies 24 to turn it into number of hours; 24 * 60 to turn it into number of minutes and so on.

Year related

Year =YEAR(D19)

Start of Year =DATE(YEAR(D19), 1, 1)

End of Year =DATE(YEAR(D19),12,31)

This section is also simple because the start and end of the year is always 1/1 and 12/31 respectively.

Reminder: Set the cell format accordingly to show the result you want (date or number).

Month related

Month =MONTH(D19)

Start of Month =EOMONTH(D19, -1) + 1

End of Month =EOMONTH(D19,0)

Days in Month =EOMONTH(D19,0) - EOMONTH(D19, -1) or DAY(EMONTH(D19,0)

Name of Month =TEXT(D19, "MMMM")

Name of Month Short =TEXT(D19, "MMM")

Use of EOMONTH is the key in this section. Why? When we know the end date of previous month, the start of current month is simply that date + 1. Make sense? For the same token, the number of days in a month can also be obtained.

Did you know, TEXT is a great function to get the name of a month or day, be it long form or short form. 👍

Quarter related

Quarter of Year =CHOOSE(MONTH(D19),1,1,1,2,2,2,3,3,3,4,4,4)
or if using M365
=LET(
m,MONTH(D19),
SWITCH(
TRUE(),
m<=3,1,
m<=6,2,
m<=9,3,
4)
)

Start of Quarter =DATE(YEAR(D19), CEILING(MONTH(D19),3) - 2, 1)

End of Quarter =EOMONTH(DATE(YEAR(D19), CEILING(MONTH(D19),3), 1),0)

The use of CHOOSE makes this calculation super flexible when the fiscal year does not start in January. Since MONTH(D19) returns a number from 1 to 12 according to the month of the date, we can adjust the sequence of the 12 values inside the function of CHOOSE. For example, if fiscal year start from July, we can revise the formula as below:

=CHOOSE(MONTH(D19),3,3,3,4,4,4,1,1,1,2,2,2)

To get the Start of Quarter (assuming year starts in January) is a little bit indirect. CEILING is first used to get the last month of a quarter by setting the second argument to 3, followed by offsetting the result by -2 to get the first month of the quarter. When the first month of the quarter is identified, getting the Start of Quarter becomes easy. 😉

EOMONTH is the best option to get the End of Quarter because End of Quarter could be either 30th or 31st.

Week related

Start of Week =D19 - WEEKDAY(D19,3)

End of Week =D19 - WEEKDAY(D19,3) + 6
'Note: End of Week is Start of Week plus 6

The above formulas return Start and End of Week assuming Monday is the first day of a week. The second argument of the WEEKDAY function is the key. You may need to offset the result for different return type selected.

Day related

Day =DAY(D19)

Day of Week =WEEKDAY(D19,2)

Day of Year =D19 - DATE(YEAR(D19),1,1) + 1

Name of Day =TEXT(D19,"DDDD")

Name of Day =TEXT(D19,"DDD")

Note: The result returned by WEEKDAY is slightly different from that in Power Query. In Power Query, it returns a number from 0 – 6 (0 = Sunday) while in WEEKDAY function in Excel, it returns a number from 1 – 7 (7 = Sunday), in our example.

Earliest / Latest

Earliest =MIN(D19:D19)

Latest =MAX(D19:E19)

MIN and MAX are used to get the earliest and latest date, respectively.

Bear in mind that a date is just a number in Excel.

“Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.”

Excel Help

When we have a solid understanding of it, we can perform various date-related calculations with simple twists. Having said, I deeply appreciate the ease of using Power Query to perform such calculations.

There are many other ways to perform the above-mentioned calculations using Excel functions or formulas. If you have other options, please share with us by leaving comments below. 🙌