Overview
There are many times when you want to make your report easy for an end user to work with but need to use variable date ranges to retrieve the data for the report. An example of this is a report that lists the sales activity for the month, the year to date and the previous year. Instead of requiring the user to enter all of the date filters that the report needs, you can calculate the date ranges based on a single start date. The formulas for calculating various dates relative to a start date are detailed below in the Excel section.
Using NP("Datefilter")
Since Excel stores dates as numbers, it can be difficult to format a date range correctly to use as a filter. The NP("DateFilter") function provides an easy way to do this.
-
Assuming that D4 contains the first of the month, the formula for calculating the filter for the whole month is listed below.
=NP("DateFilter",D4, Date(Year(D4), MONTH(D4)+1, 1)-1)If D4 equaled 2/1/2019, the result of the above formula would be 2/1/2019..2/28/2019.
-
The formula for calculating the filter for the year to date for the month in cell D4 is listed below.
=NP("DateFilter", Date(Year(D4), 1, 1), Date(Year(D4), MONTH(D4)+1, 1)-1)If D4 was 2/1/2020, the result of the formula above would be 1/1/2020..2/29/2020.
Note that Excel will take care of leap years for you. -
The formula for calculating the filter from the same month last year until the end of the month in cell D4 is listed below.
=NP("DateFilter", Date(Year(D4)-1, Month(D4), 1), Date(Year(D4), MONTH(D4)+1, 1)-1)If D4 equaled 5/1/2020, the result of the above formula would be 5/1/2019..5/31/2020.
Comments