Forum Discussion
Bug: Dragging fill handle with dates
- Dec 09, 2021do 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.
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)
- paulobuchsbaumDec 09, 2021Copper Contributormtarler, it works beautifully!!