Multiple Web Intelligence user’s questions are regarding date and time and especially difference between 2 dates.
Web Intelligence already offers 2 functions dedicated to dates difference:
- DaysBetween()
- MonthsBetween()
Those functions work well when we are manipulating dates but the result is not perfect when we are manipulating date + time: datetime, timestamp, etc.
Moreover, there is no out of the box functions to compute the difference for years, quarters, hours, minutes and seconds.
The goal of this document is to present you how to compute time difference functions with correct results.
Let’s assume we have 2 variables containing date + time and respectively named:
- [Start Date]
- [End date]
Years difference
Here is the formula:
=If ToNumber(FormatDate([End Date];”yyyyMMddHHmmss”)) – ToNumber(FormatDate([Start Date];”yyyyMMddHHmmss”)) >= 0
Then
ToNumber(FormatDate([End Date];”yyyy”)) – ToNumber(FormatDate([Start Date];”yyyy”)) – (If ToNumber(FormatDate([End Date];”MMddHHmmss”)) – ToNumber(FormatDate([Start Date];”MMddHHmmss”)) >= 0 Then 0 Else 1)
Else
(ToNumber(FormatDate([Start Date];”yyyy”)) – ToNumber(FormatDate([End Date];”yyyy”)) – (If ToNumber(FormatDate([Start Date];”MMddHHmmss”)) – ToNumber(FormatDate([End Date];”MMddHHmmssMM”)) >= 0 Then 0 Else 1)) * -1
Formula explanation:
- if [End Date] >= [Start Date], all computations will compute [End Date] – [Start date]
- if [End Date] < [Start Date], all computations will compute [Start Date] – [End Date] and the final result will be multiplied by -1
- If the difference between the 2 dates with the format “MMddHHmmss” is negative, then we subtract 1 from the result in both expressions
Quarters difference
Here is the formula:
=If ToNumber(FormatDate([End Date];”yyyyMMddHHmmss”)) – ToNumber(FormatDate([Start Date];”yyyyMMddHHmmss”)) >= 0
Then
((ToNumber(FormatDate([End Date];”yyyy”)) – ToNumber(FormatDate([Start Date];”yyyy”))) * 4) + (If ToNumber(FormatDate([End Date];”MMddHHmmss”)) – ToNumber(FormatDate([Start Date];”MMddHHmmss”)) >= 0 Then Truncate((ToNumber(FormatDate([End Date];”MM”)) – ToNumber(FormatDate([Start Date];”MM”))) / 3;0) Else Truncate((ToNumber(FormatDate([Start Date];”MM”)) – ToNumber(FormatDate([End Date];”MM”))) / 3;0))
Else
(((ToNumber(FormatDate([End Date];”yyyy”)) – ToNumber(FormatDate([Start Date];”yyyy”))) * 4) + (If ToNumber(FormatDate([End Date];”MM”)) – ToNumber(FormatDate([Start Date];”MM”)) >= 0 Then Truncate((ToNumber(FormatDate([End Date];”MM”)) – ToNumber(FormatDate([Start Date];”MM”))) / 3;0) Else Truncate((ToNumber(FormatDate([Start Date];”MM”)) – ToNumber(FormatDate([End Date];”MM”))) / 3;0))) * -1
Formula explanation:
- if [End Date] >= [Start Date], all computations will compute [End Date] – [Start date]
- if [End Date] < [Start Date], all computations will compute [Start Date] – [End Date] and the final result will be multiplied by -1
- If the difference between the 2 dates with the format “MMddHHmmss” is negative, then we revert the order of dates to compute the remaining quarters
- The number of remaining quarters is always rounded to the lowest integer (Truncate function) when computing the difference between months.
Months difference
Here is the formula:
=If ToNumber(FormatDate([End Date];”yyyyMMddHHmmss”)) – ToNumber(FormatDate([Start Date];”yyyyMMddHHmmss”)) >= 0
Then
MonthsBetween([Start Date];[End Date]) – (If ToNumber(FormatDate([End Date];”ddHHmmss”)) – ToNumber(FormatDate([Start Date];”ddHHmmss”)) >= 0 Then 0 Else 1)
Else
(MonthsBetween([End Date];[Start Date]) – (If ToNumber(FormatDate([Start Date];”ddHHmmss”)) – ToNumber(FormatDate([End Date];”ddHHmmss”)) >= 0 Then 0 Else 1) ) * -1
Formula explanation:
- if [End Date] >= [Start Date], all computations will compute MonthsBetween([End Date];[Start date])
- if [End Date] < [Start Date], all computations will compute MonthsBetween ([Start Date];[End Date]) and the final result will be multiplied by -1
- If the difference between the 2 dates with the format “ddHHmmss” is negative, then we subtract 1 from the result in both expressions
We are sure we have exactly the number of months by taking into account a timestamp.
Days difference
Here is the formula:
=If ToNumber(FormatDate([End Date];”yyyyMMddHHmmss”)) – ToNumber(FormatDate([Start Date];”yyyyMMddHHmmss”)) >= 0
Then
DaysBetween([Start Date];[End Date]) – (If ToNumber(FormatDate([End Date];”HHmmss”)) – ToNumber(FormatDate([Start Date];”HHmmss”)) >= 0 Then 0 Else 1)
Else
(DaysBetween([End Date];[Start Date]) – (If ToNumber(FormatDate([Start Date];”HHmmss”)) – ToNumber(FormatDate([End Date];”HHmmss”)) >= 0 Then 0 Else 1) ) * -1
Formula explanation:
- if [End Date] >= [Start Date], all computations will compute DaysBetween ([End Date];[Start date])
- if [End Date] < [Start Date], all computations will compute DaysBetween ([Start Date];[End Date]) and the final result will be multiplied by -1
- If the difference between the 2 dates with the format “HHmmss” is negative, then we subtract 1 from the result in both expressions
We are sure we have exactly the number of days by taking into account a timestamp.
Hours difference
Here is the formula:
=If ToNumber(FormatDate([End Date];”yyyyMMddHHmmss”)) – ToNumber(FormatDate([Start Date];”yyyyMMddHHmmss”)) >= 0
Then
(DaysBetween([Start Date];[End Date]) * 24) +
(ToNumber(FormatDate([End Date];”HH”)) – ToNumber(FormatDate([Start Date];”HH”)))-
(If ToNumber(FormatDate([End Date];”mmss”)) – ToNumber(FormatDate([Start Date];”mmss”)) >= 0 Then 0 Else 1)
Else
((DaysBetween([End Date];[Start Date]) * 24) +
(ToNumber(FormatDate([Start Date];”HH”)) – ToNumber(FormatDate([End Date];”HH”)))-
(If ToNumber(FormatDate([Start Date];”mmss”)) – ToNumber(FormatDate([End Date];”mmss”)) >= 0 Then 0 Else 1)) * -1
Formula explanation:
- if [End Date] >= [Start Date], all computations will compute DaysBetween ([End Date];[Start date])
- if [End Date] < [Start Date], all computations will compute DaysBetween ([Start Date];[End Date]) and the final result will be multiplied by -1
- The number of days is multiplied by 24
- If the difference between the 2 dates with the format “mmss” is negative, then we subtract 1 from the result in both expressions
Minutes difference
Here is the formula:
=If ToNumber(FormatDate([End Date];”yyyyMMddHHmmss”)) – ToNumber(FormatDate([Start Date];”yyyyMMddHHmmss”)) >= 0
Then
(DaysBetween([Start Date];[End Date]) * 1440) +
((ToNumber(FormatDate([End Date];”HH”)) – ToNumber(FormatDate([Start Date];”HH”))) * 60) +
(ToNumber(FormatDate([End Date];”mm”)) – ToNumber(FormatDate([Start Date];”mm”))) –
(If ToNumber(FormatDate([End Date];”ss”)) – ToNumber(FormatDate([Start Date];”ss”)) >= 0 Then 0 Else 1)
Else
((DaysBetween([End Date];[Start Date]) * 1440) +
((ToNumber(FormatDate([Start Date];”HH”)) – ToNumber(FormatDate([End Date];”HH”))) * 60) +
(ToNumber(FormatDate([Start Date];”mm”)) – ToNumber(FormatDate([End Date];”mm”))) –
(If ToNumber(FormatDate([Start Date];”ss”)) – ToNumber(FormatDate([End Date];”ss”)) >= 0 Then 0 Else 1)) * -1
Formula explanation:
- if [End Date] >= [Start Date], all computations will compute DaysBetween ([End Date];[Start date])
- if [End Date] < [Start Date], all computations will compute DaysBetween ([Start Date];[End Date]) and the final result will be multiplied by -1
- The number of days is multiplied by 1440
- If the difference between the 2 dates with the format “ss” is negative, then we subtract 1 from the result in both expressions
Seconds difference
Here is the formula:
=If ToNumber(FormatDate([End Date];”yyyyMMddHHmmss”)) – ToNumber(FormatDate([Start Date];”yyyyMMddHHmmss”)) >= 0
Then
(DaysBetween([Start Date];[End Date]) * 86400) +
((ToNumber(FormatDate([End Date];”HH”)) – ToNumber(FormatDate([Start Date];”HH”))) * 3600) +
((ToNumber(FormatDate([End Date];”mm”)) – ToNumber(FormatDate([Start Date];”mm”))) * 60) +
(ToNumber(FormatDate([End Date];”ss”)) – ToNumber(FormatDate([Start Date];”ss”)))
Else
((DaysBetween([End Date];[Start Date]) * 86400) +
((ToNumber(FormatDate([Start Date];”HH”)) – ToNumber(FormatDate([End Date];”HH”))) * 3600) +
((ToNumber(FormatDate([Start Date];”mm”)) – ToNumber(FormatDate([End Date];”mm”))) * 60) +
(ToNumber(FormatDate([Start Date];”ss”)) – ToNumber(FormatDate([End Date];”ss”)))) * -1
Formula explanation:
- if [End Date] >= [Start Date], all computations will compute DaysBetween ([End Date];[Start date])
- if [End Date] < [Start Date], all computations will compute DaysBetween ([Start Date];[End Date]) and the final result will be multiplied by -1
- The number of days is multiplied by 86400
Report example
Here is a screenshot of a report.
The dates in red mean that [End Date] < [Start Date]
You can download the Web Intelligence report attached to that publication.
Didier MAZOUE
Awesome Didier!