Forum Discussion
Bug: Dragging fill handle with dates
The bug reproduction is simple
a) Format Column A as Hour type hh:mm:ss
b) Put [16:00] in A1 e [16:10] in A2
c) Drag the fill handle in the bottom right 1 cell down
d) In A3 shows [16:20]
e) Put [16:20] in B3
f) Put the formula [=A3=B3] in C3
g) it Show [falso] ([false] in English
Internally Excel uses floating point sum, when it should add and round as it is a date.
- do you have excel 365? how about =TIME(0,SEQUENCE(100,1,0,10),0) to give you 100 rows adding 10 min each time. Of course you can offset that accordingly.
4 Replies
well that is 1 interpretation / opinion.
I appreciate the annoyance round-off / precision errors can cause but it is something we have to live with and account for (i.e. force a round off / INT / etc.... as needed)
Basically, just because you want to SHOW a certain format/precision doesn't mean you want the calculations to do the same and therefore the 'FORMAT' is completely independent from the actual VALUES.
An example were this is useful: I have a need to create a list of dates where every date is listed 2x so I simply start with 2022-01-01 00:00:00 and then in next cell enter 2022-01-01 12:00:00 and then use the fill down but show/format the column to only show the Short Date.
Another thought is according to your proposed way, the fill down could result in different results if they have it formatted in HH:MM vs HH:MM:SS vs HH:MM:SS.000 and then if they change the format should the filled series then change accordingly?It'ok, but it's a date format, mtarler, if was numbers, I would undestand it,
I use this kind of table to make statistics in a table database called [DB], where I use a criteria in order to sum a column [Occurrences] in a 10 minutes time lapse based on a column [Dt].
There are 2 solutions, supposing that table starts in line 2
a)
Add 2 more columns A and B, with hour and minute both with formula, suppose that starts in line 2
A2 and B2 without formulas, with initial hour and minute
A3 (Hour) = A2 + IF(B2=50,1,0) ' Add 10 minutes to previous hour and minute
B3 (Minutes) = B2 + IF(B2=50 ,0, B2+10)
C2 (Initial time) = Time(A2,B2,0) -> name [HI]
D2 (Final time) = Time(A2,B2+10,0) -> name [HF]
After, one can use C and D columns to build the criterion.
E2 (Criterion) = AND(Dt >= @HI, Dt <=@HF)
F2 (Query) =DSUM(DB,"Occurrences",E1:D2)' sum occurrences between a 10 minutes lapse of time.
b)
I can also use a nerd solution for the criterion (it's my real solution that I've adopted, it's more concise)
Using directly C (name [HI]) and D (name [HF]) for time ranges:
Criterion [D2] =AND(ROUND(Dt-INT(Dt),6) >= ROUND(HOUR(@HI)/24+MINUTE(@HI)/1440,6),ROUND(Dt-INT(Dt),6) < ROUND(HOUR(@HF)/24+MINUTE(@HF)/1440,6))
Query [E2]
=DSUM(DB,"Occurrences",D1:D2) ' sum occurrences between a 10 minutes lapse of time.
I get the fraction part of date/time of the field from current database record, and get the hour and minute expressed of a fraction of the day.
I use 6 decimal cases, for hold the precision of 1/86.400 (5 it would be enough)- do you have excel 365? how about =TIME(0,SEQUENCE(100,1,0,10),0) to give you 100 rows adding 10 min each time. Of course you can offset that accordingly.
