• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

DateAdd Function ????

I have a field on a form called [currentperiod] and it holds a number from 1-12 dependant on the month of the year a report is running for. I need to be able to subtract 1 or 2 from this number to be able to produce comaprison monthly reports.

ie:

If I'm running a report for period 10, I need to be able to do periods 8 & 9 also not a problem as I can just subtract one. however when I reach period 1 next year I need to be able to have the reports generated for periods 11 & 12 of the prior year. I have tried dateadd("m",-1,[currentperiod]) but it does not work all the time.

Is there anything I can do?
0
Carl2002
Asked:
Carl2002
2 Solutions
 
bluelizardCommented:
instead of using these formulae

  =[currentperiod] - 1
and
  =[currentperiod] - 2

use these

  =iif([currentperiod]>1;([currentperiod] - 1);([currentperiod] + 11 ))
and
  =iif([currentperiod]>2;([currentperiod] - 2);([currentperiod] + 10 ))


--bluelizard
0
 
Raynard7Commented:
Is currentperiod a date - or is it a number from 1 to 12?

It doese not look like it is a date,

I would use the logic

MonthsBeforeTime = (([currentperiod] + 12 - monthsPrior - 1) Mod 12) + 1

Where monthsPrior is the number of months.

This will effectively treat the numbers as "wrapping" around the number 12
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now