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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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).
- 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.
- 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.
- 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.