Useful Microsoft Power Query Date Functions

Excel Sales Forecasting For Dummies

The following table contains Excel date functions that help do things like add months, pull out date parts, and get the number of days within a given time period.

Function	What It Does and How to Use It
Date.AddDays	Increments a given date value by a specified number of days. This example returns a date that is seven days from the date in Column1: Date.AddDays([Column1], 7)
Date.AddMonths	Increments a given date value by a specified number of months. This example returns a date that is three months earlier than the date in DateColumn1: Date.AddMonths([DateColumn1], -3)
Date.AddWeeks	Increments a given date value by a specified number of weeks. This example returns a date that is 12 weeks from the date in DateColumn1: Date.AddWeeks([Column1], 12)
Date.AddYears	Increments a given date value by a specified number of years. This example returns a date that is one year earlier than the date in DateColumn1: Date.AddYears([DateColumn1], -1)
Date.Day	Returns the day number for a given date value: Date.Day([DateColumn1])
Date.DayOfWeek	Returns a number between 0 and 6 representing the day of the week from a date value: Date.DayOfWeek([DateColumn1])
Date.DayOfYear	Returns a number that represents the day of the year from a date value: Date.DayOfYear([DateColumn1])
Date.DaysInMonth	Returns the number of days in the month from a date value: Date.DaysInMonth([DateColumn1])
Date.Month	Returns the month number from a DateTime value: Date.Month([DateColumn1])
Date.WeekOfMonth	Returns a number for the count of week in the current month: Date.WeekOfMonth([DateColumn1])
Date.WeekOfYear	Returns a number for the count of week in the current year: Date.WeekOfYear([DateColumn1])
Date.ToText	Returns the text representation of a given date. The following example returns the 3-character month name for the date in DateColumn1: Date.ToText([DateColumn1],"MMM") The following example returns the full month name for the date in DateColumn1: Date.ToText([DateColumn1],"MMMM")

About This Article

About the book author:

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.