Skip to Content
Technical Articles
Author's profile photo Abhishek Das

Add any number of days till 30 days to a given date in Online Report Designer (Report – Canvas).

Often during reporting in ORD- detailed reporting live data / advanced reporting (Report – Canvas), we encounter requirement where we need to get a new date from a given date by adding few days to it.

This blog post will walk you through the steps on how you can meet this requirement.
Following the below steps, we will be able to add any number of days, till 30 days, to a given date.


  1. First we need to create the ORD/ advanced reporting report and add the date field in the report. We will explain you the scenario with an example where we will add 30 days to the Recruit date of an employee. The Recruit date is 30/01/2010.
  2. In the next step, we will separate the Day, Month and Year of the date with which we need to add 30 days. This can be done by 3 calculated columns: Day, Month, Year.The calculation here is applying the function: Day, Month, Year on the date field respectively in the 3 calculated columns.
    So, as per our example, we will get 3 new calculated column fields with the values as follows:
    Day : 30, Month: 1, Year: 2010.
  3. Then we have done a Leap year check to figure out that particular year has 28 or 29 days in the month of February. The calculated column is called LeapYearCheck. In this calculated column we have hard coded the leap years from 1900 to 2100 as ORD calculated column lacks the modulo function. The outcome value of this calculated column field is 0/1 depending on the Leap year. For our example, LeapYearCheck=0.
  4. In the next step, we will calculate the number of days in the month of the given date. The calculated column used here is called: MonthDayCount which uses the previously created calculated columns: Month & LeapYearCheck. The If-Else loop checks the month number and decides the date on that basis.
    In our example case: MonthDayCount is 31.
  5. Now, it’s time to increase 30 days and for this purpose we are using a calculated column Day1 where we are adding 30 days to the number of days calculated in the calculated column Day.For our case, Day1= 30+30=60.
  6. We need to build our new date now and we will start with the first step : calculating the new day of the date. To calculate this we will use calculate column: New Day where we will subtract the calculated column MonthDayCount from Day1. This will give us the new day. If the calculation is zero, we will assign the value of New Day as the MonthDayCount.
    As per our example, New Day = 29.
  7. Lets proceed further in the calculation and calculate the month of the new date in the calculated column: New Month. In this we have checked the condition that if the Day1 value is more than MonthDayCount, then we will increment the month by 1, else we will stick to the same month value stored in the calculated column Month. And if the month is December, and Day1 is greater than MonthDayCount we set the new month as 1 i.e. Next year January.

    For our example : New Month = 2 , incremented by 1 as Day1 (60) > MonthDayCount (31).

  8. Finally we are left with the year calculation of the new date and it is done in the calculated column: New Year. In this, we will check whether the month is December and if it is crossing to next year January, then we will increment the year, else will stick to the same value of year stored in calculated column : Year.
    In our example: New Year is 2010.
  9. Now we will create the new date by concatenating the calculated columns: New Day, New Month & New Year in the calculated column: New Date 1.
    You will see that this calculated column field will give correct data for all cases but except the below two:
  • Year is not a leap year and you get the new date as 29th Feb and 30th Feb.
  • Year is a leap year and you get the new date as 30th Feb.For our example: the new date formed in New Date 1 = 29/2/2010, which is not correct as 2010 is not a leap year.
  1. Lets’ correct this exception case in our final calculated column: New Date. In this calculated column, we have taken care of the above explained exception cases. If these exception cases are encountered, the day and month will be incremented and in rest of the normal cases the New Date will be same as New Date 1.
    So finally for our example case, the New Date is 01/03/2010.
    This is a field of data type Text.
    The final calculation data calculating the date which is 30 days plus the recruit date : 31/01/2010:You can choose to hide all the calculated columns except the New Date.I believe this blog post will help you now on wards to create a new date by adding a certain number of days to a given date.

    Note: All the screenshots used in this blog are taken from Demo instance.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Anette Zajonz
      Anette Zajonz

      Dear Abhishek Das,


      How do you have configured the LeapYearCheck?


      I would like to add one year from the date of acompleted training. As an example, the training was completed on 1/8/2020 and would need to be renewed on 1/7/2021, do you have maybe a guide how I can configure it n the Canvas Report?




      Author's profile photo Melissa Domingo
      Melissa Domingo

      Hello! How do I add a simple LESS THAN ONE to a date field?

      Example: Hire Date - 1 Day

      I cant seem to get it to work.

      Author's profile photo Michael Kosloski
      Michael Kosloski

      You have to follow a slight different order of operations.


      • Split Date
      • Check Leap Year
      • Find New Month if date being adjusted is the first of the month
        • If Day - 1 = 0  (Nested If: If Month = 1 then 12 else Month - 1) Else month
      • Perform DaysInMonth calculation on newly calculated month value
      • Calculate New Day if date being adjusted is the first, then you need the prior month end date
        • If Day - 1 = 0 then DaysInMonth Else Day - 1
      • Calculate New Year if date being adjusted if 1/1
        • If Day - 1 = 0  AND Month = 1 then year -1 Else year
      • Make string
      Author's profile photo Kong Faat Lau
      Kong Faat Lau

      Hi Michael,


      Could you please elaborate a bit more on how to convert the "hire date minus 1 day"?


      Thank you so much!




      Author's profile photo Kong Faat Lau
      Kong Faat Lau

      How about "hire date - 90 days"? How should I perform the calculation?

      Author's profile photo Michael Kosloski
      Michael Kosloski

      Thanks for the post, this was very helpful. Wish we had a dateadd function!


      I think you over complicated the MonthDayCount. You can save time (and probably improve performance) by excluding the check for 31 day months, as we know if it is not February, April, June, September or November then there are 31 days.