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?
-
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.
-
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.
-
Thanks for the reply. I was able to produce the reports with some manual modifications. Hopefully, a fix will be released before next November :)
-
I sure hope so!
-
Print this page