WebI: How to Calculate Elapsed Time and Days

Question:
When creating a WebI report, how do I calculate the days or time between two dates?

Answer:
There are two functions for calculating elapsed time and dates:  TimeBetween() and DatesBetween(). Both functions need:

1) A starting date and an ending date. 
If a truncated date (no time stamp) is used then the default time will be 12:00:00 am (midnight).

2) A measurement period (i.e: Month, Year, Millisecond, Hour).

  • Acceptable periods for TimeBetween() are: DayPeriod, WeekPeriod, MonthPeriod, QuarterPeriod,SemesterPeriod, YearPeriod, HourPeriod, MinutePeriod, SecondPeriod, MillisecondPeriod
  • Acceptable periods for DatesBetween() are: DayPeriod, WeekPeriod, MonthPeriod, QuarterPeriod, SemesterPeriod, YearPeriod.

Examples:

1) You need to calculate the number of years between two objects in your reporting area: the date you acquired a piece of equipment [Acquired Date], and the date the data was last exported to your reporting area [Last Export Date]. The equation would be written as:

=DatesBetween([Acquired Date];[Last Export Date];YearPeriod)

2) You need to calculate the time elapsed minutes between two objects in your reporting area: the date and time a request for service was received (Received Date], and the date and time the request for service was closed [Closed Date]. The equation would be written as:

=TimeBetween([Received Date];[Closed Date];HourPeriod)


*Need help?*
If you need help, please contact the EIM team at https://td.unh.edu/TDClient/Requests/ServiceDet?ID=142,  or by calling the IT Service Desk at 862-4242.

Details

Article ID: 1809
Created
Fri 7/19/19 6:22 PM
Modified
Tue 1/21/20 12:49 PM