Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 4 dates and I need to be able to calculate the number of months between each. For example: between loss date and report date but also between loss date and closed date. What expression do I use and do I need to create multiple master items? What if one of these dates is null?
Loss Date, Report Date, First Reserve Date, Closed Date
Case No | Loss Date | Report Date | First Reserve Date | Closed Date |
100 | May-18 | Aug-19 | Jan-20 | Sep-21 |
101 | Jun-16 | Apr-18 | Jul-18 | Jun-20 |
102 | Jul-21 | Sep-21 | Jan-22 |
The methods I suggest will provide the number of days
1) Sum([Report Date]-[Loss Date])
2) NetWorkDays([Report Date],[Loss Date])
but you require number of months so you need to divide by "how many days per month" . On the solution, @nevopotokcloudinary suggests to divide by 30. That denominator is the business rule I was referring to.
I guess you could do a simple subtraction of dates e.g. Sum([Report Date]-[Loss Date]) or perhaps find the net working days in between e.g. NetWorkDays([Report Date],[Loss Date]) . To convert to months you need to come up with a business and define the proper denominator.
I hope this helps
You can use this expression for all months intervals (absolute intervals)
Fabs(Div(LossDate-ReportDate,30)+1)
Thanks. Can you tell me more about coming up with a business and define the proper denominator. Not sure what that means.
Thanks. What if I want to see month intervals with % of month. Example using may 1 and June 15, rather than 1 month to show 1.5 months.
The methods I suggest will provide the number of days
1) Sum([Report Date]-[Loss Date])
2) NetWorkDays([Report Date],[Loss Date])
but you require number of months so you need to divide by "how many days per month" . On the solution, @nevopotokcloudinary suggests to divide by 30. That denominator is the business rule I was referring to.