0

Excel - Problem with time change (Daylight Eastearn Time to Normal Eastearn Time)

Hi,

I have an Excel Report that calculates weekly statistics (7 days). The report works fine except when the report's time range includes the day when the time change from Daylight Eastearn Time to Normal Eastearn Time (Occured on october 6th). In this case, everything fails. I guest the opposite time change results in the same issue

Figure 1 shows the report working properly

Figure 3 shows the (many) error messages I get when the time range includes october 6th.

Figure 2 shows the result

Using an aggretate interval of 169 hours (7 days + 1 hour) instead of "7DAY" works fine but is just a workaround. I do not want to have to change all calculations two times a year to add or subtract 1 hour !

Anyone has same or similar issues?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • eric. tremblay
    • Data Architect
    • eric_tremblay
    • 1 yr ago
    • Reported - view

    Another issue is that I get two rows of data for the same day in my Daily Totalizers. I would have to set the Aggregate Interval to 25:00:00 I think, but only for this day, not for the entire Weekly report. 

    Like
  • Hi eric. tremblay ,

    The reason for the errors is the function is returning more rows than you expect and is bleeding into another array on the spreadsheet. The extra row is because the time range is greater than the aggregate interval. Having that extra hour causes the function to generate another interval. This wouldn't occur for the other time change because the time range would be less than the aggregate interval.

    In your particular use case for the Daily Totalizers, I think it will work if you use a relative time, '1d', instead of 24:00:00 for the aggregate interval. The extra row will only appear if the time change occurs within the last interval of the given time range. (This appears to be a bug). Unfortunately, I think you will still get an extra row for the first example you put forth until we release a fix for the bug.

    Like
    • eric. tremblay
    • Data Architect
    • eric_tremblay
    • 1 yr ago
    • Reported - view

    Thanks for the reply. I was able to produce the reports with some manual modifications. Hopefully, a fix will be released before next November :)

    Like
    • I sure hope so!

      Like
print this pagePrint this page
Like Follow
  • 1 yr agoLast active
  • 4Replies
  • 46Views
  • 2 Following