Date Functions Overview

Learn about Date Functions in Power Query M Language, the key for working with dates. This page provides a comprehensive list and descriptions of Date Functions available in the M Language.

Table of contents

Learn about Date Functions in Power Query M Language, key for working with dates. This page provides a comprehensive list and descriptions of Date Functions available in the M Language.

Date Component Functions

FunctionDescriptionInputOutput
Date.DayReturns the day component.AnyNumber
Date.DayOfWeekReturns a number (from 0 to 6) indicating the day of the week of the provided value.AnyNumber
Date.DayOfWeekNameReturns the day of the week name.AnyText
Date.DayOfYearReturns a number from 1 to 366 representing the day of the year.AnyNumber
Date.DaysInMonthReturns a number from 28 to 31 indicating the number of days in the month.AnyNumber
Date.MonthReturns the month component.AnyNumber
Date.MonthNameReturns the name of the month component.AnyText
Date.QuarterOfYearReturns a number indicating which quarter of the year the date falls in.AnyNumber
Date.WeekOfMonthReturns a number from 1 to 6 indicating which week of the month this date falls in.AnyNumber
Date.WeekOfYearReturns a number from 1 to 54 indicating which week of the year this date falls in.AnyNumber
Date.YearReturns the year component.AnyNumber

Creation and Conversion Functions

FunctionDescriptionInputOutput
#dateCreates a date value from whole numbers representing the year, month, and day.NumberDate
Date.FromCreates a date from the given value.AnyDate
Date.FromTextCreates a Date from local, universal, and custom Date formats.TextDate
Date.ToRecordReturns a record containing parts of the date value.DateRecord
Date.ToTextReturns a textual representation of the date value.DateText

Date Modification Functions

FunctionDescriptionInputOutput
Date.AddDaysAdds the specified days to the date.AnyAny
Date.AddMonthsAdds the specified months to the date.AnyAny
Date.AddQuartersAdds the specified quarters to the date.AnyAny
Date.AddWeeksAdds the specified weeks to the date.AnyAny
Date.AddYearsAdds the specified years to the date.AnyAny

Information Functions

FunctionDescriptionInputOutput
Date.IsInCurrentDayIndicates whether this date occurs during the current day.AnyLogical
Date.IsInCurrentMonthIndicates whether this date occurs during the current month.AnyLogical
Date.IsInCurrentQuarterIndicates whether this date occurs during the current quarter.AnyLogical
Date.IsInCurrentWeekIndicates whether this date occurs during the current week.AnyLogical
Date.IsInCurrentYearIndicates whether this date occurs during the current year.AnyLogical
Date.IsInNextDayIndicates whether this date occurs during the next dayAnyLogical
Date.IsInNextMonthIndicates whether this date occurs during the next monthAnyLogical
Date.IsInNextNDaysIndicates whether this date occurs during the next number of daysAnyLogical
Date.IsInNextNMonthsIndicates whether this date occurs during the next number of monthsAnyLogical
Date.IsInNextNQuartersIndicates whether this date occurs during the next number of quartersAnyLogical
Date.IsInNextNWeeksIndicates whether this date occurs during the next number of weeksAnyLogical
Date.IsInNextNYearsIndicates whether this date occurs during the next number of yearsAnyLogical
Date.IsInNextQuarterIndicates whether this date occurs during the next quarterAnyLogical
Date.IsInNextWeekIndicates whether this date occurs during the next weekAnyLogical
Date.IsInNextYearIndicates whether this date occurs during the next yearAnyLogical
Date.IsInPreviousDayIndicates whether this date occurs during the previous dayAnyLogical
Date.IsInPreviousMonthIndicates whether this date occurs during the previous monthAnyLogical
Date.IsInPreviousNDaysIndicates whether this date occurs during the previous number of daysAnyLogical
Date.IsInPreviousNMonthsIndicates whether this date occurs during the previous number of monthsAnyLogical
Date.IsInPreviousNQuartersIndicates whether this date occurs during the previous number of quartersAnyLogical
Date.IsInPreviousNWeeksIndicates whether this date occurs during the previous number of weeksAnyLogical
Date.IsInPreviousNYearsIndicates whether this date occurs during the previous number of yearsAnyLogical
Date.IsInPreviousQuarterIndicates whether this date occurs during the previous quarterAnyLogical
Date.IsInPreviousWeekIndicates whether this date occurs during the previous weekAnyLogical
Date.IsInPreviousYearIndicates whether this date occurs during the previous yearAnyLogical
Date.IsInYearToDateIndicates whether this date occurs during the current year and is on or before the current day.AnyLogical
Date.IsLeapYearIndicates whether this date falls in a leap year.AnyLogical

Start-End Functions

FunctionDescriptionInputOutput
Date.EndOfDayReturns the end of the day.AnyAny
Date.EndOfMonthReturns the end of the month.AnyAny
Date.EndOfQuarterReturns the end of the quarter.AnyAny
Date.EndOfWeekReturns the end of the week.AnyAny
Date.EndOfYearReturns the end of the year.AnyAny
Date.StartOfDayReturns the start of the day.AnyAny
Date.StartOfMonthReturns the start of the month.AnyAny
Date.StartOfQuarterReturns the start of the quarter.AnyAny
Date.StartOfWeekReturns the start of the week.AnyAny
Date.StartOfYearReturns the start of the year.AnyAny

Last update: November 24, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/date-functions
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.

BI Gorilla Youtube Channel

Last update: August 13, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/date-functions
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.