0

current month date range

Jet Reports Historic Posts

February 25, 2015 15:21

None

Follow

THANKS! I am having a hard time understanding this would you mind posting the same but to show the current entire year so I can see the difference and further understand how this works? I tried to modify this and just messed it up but would like to understand it. Thanks for your help!

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Jet Reports Historic Posts February 25, 2015 15:34 Official comment

  Sure:
  
  =NP("DateFilter",DATE(YEAR(TODAY()),MONTH(TODAY()),1),DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))
  
  -HP

• 0
  
  Jet Reports Historic Posts February 25, 2015 15:21

  Hello,
  
  Would someone be kind enough to tell me how to formulate a date range equivalent to the "Current Month"? For example today is "2/25/2015" and when my report runs I need all data for "2/1/2015..2/28/2015" and it needs to be dynamic in the sense that it will need to change each month to get the correct date range for the current month. Any assistance appreciated, thanks!

• 0
  
  Jet Reports Historic Posts February 25, 2015 16:54

  Basically HP is taking standard Excel and putting it into an NP(DateFilter).
  
  Here is an article on how to use the Excel Date function: https://support.office.com/en-us/article/DATE-function-9b619152-52d4-48f0-b1ab-a32dee59b6e4?CorrelationId=c9bab226-168c-48db-9791-73eac2522c42&ui=en-US&rs=en-US&ad=US
  
  If I wanted the full year I would do something like this:
  
  =NP("DateFilter",DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31))
  
  Basically for the full year, you know that the first month/day will always be January 1 so we used 1 as the Month and Day parameters in the first date function. We know December 31 will always be the last day of the year so we use 12 and 31 for the Month and Day parameters, respectively, in the second date function. We just use the Year(Today()) function to get what the current year is.

• 0
  
  Jet Reports Historic Posts February 25, 2015 22:28

  Thanks for your help folks. I think I understand now. Much appreciated!

• 0
  
  Jet Reports Historic Posts February 27, 2015 08:14

  Take a look at the excel eomonth function. Using 0 as the second paramter gives you the month end date fo the current month. Using -1 and then adding one to the date would also get you the first day of the currenr month.
  
  NP("DateFilter",EOMONTH(TODAY(),-1)+1, EOMONTH(TODAY(),0))
  
  You can also move several months ahead or back for aged reports.