Skip to Content
Author's profile photo Didier MAZOUE
Didier MAZOUE
Posted on June 23, 2016 5 minute read

Date and Time difference

Follow RSS feed
3 Likes 372 Views 1 Comment

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]

Date and Time difference - 1.png

You can download the Web Intelligence report attached to that publication.

Didier MAZOUE

Assigned tags

Related Blog Posts

    Related Questions

      /
      Date and Time difference - 1.png
      1 Comment
      You must be Logged on to comment or reply to a post.