Excel Date Functions

Overview

Five date related functions in Excel make working with dates much easier. The functions are as follows.

• =Date(Year, Month, Day)
• =Day(Date)
• =Month(Date)
• =Year(Date)
• =Text(date, format)

Examples

To find the first day of the month from a date entered in D4, you could enter the following formula.

=Date(Year(D4), MONTH(D4), 1)

Since dates are stored in Excel as integers, you can find the last day of the month from a date entered in D4 with the following formula:

=Date(Year(D4), MONTH(D4)+1, 1)-1

Since you can disassemble a date easily into the year, the month and the day, it is also easy to calculate the start of the year for the date in D4 with the following formula:

=Date(Year(D4), 1, 1)

If your fiscal year started in July instead of January, the formula is similar. The IF formula in the Year parameter of the following formula determines, in this case, if the start of the fiscal year is the same as the calendar year (i.e. Sept 6, 2023 is in the 2023 Fiscal Year), or if you need to subtract 1 from the calendar year (May 15, 2023 is in the 2022 Fiscal Year):

=Date(If(Month(D4)<7,Year(D4)-1, Year(D4)),7, 1)

To build a date range from two dates, use the NP ("DateFilter") function. If the start of the month was in cell D4 and you wanted to calculate a filter for the whole month, the formula for the date range would be:

=NP("DateFilter",D4, Date(Year(D4), MONTH(D4)+1,1)-1)

The function above looks complicated, but it is just an NP function with the end of month formula previously listed.