Forum Discussion
Fill series by hour
I think there should be a simple solution to this, but it is escaping me.
I'm creating a food diary, and want to make one column show the date, and the next column show every hour of that day. Each worksheet will have a week's worth of entries, so I need to replicate the second column 7 times on each worksheet.
How do I use "Fill Series" to fill in the hours when I type the first one as 12:00 AM? I keep trying to do it, selecting 23 cells below the first one, but it fills in each cell with "12:00 AM".
I know I can just type them in, but I'm sure there is a shortcut.
TIA
Wow, I never knew you could do that! I figured there must be a simple way, I just couldn't figure it out. Thanks so much!
For Fill Series, you only need to select the cell with 0:00 AM.
In the Fill Series dialog, select Columns.
Enter 0.041666666666666666 in the Step box and 0.9583333333333333333 in the Stop Value box.
Or:
Type 12:00 AM in a cell and 1:00 AM in the cell below.
Select the two cells, then drag the fill handle in the lower right corner of the selection down.
- That's a little more complicated than the answer from OliverScheurich, but it works just as well. Thank you so much!
For posterity....
HansVogelaar wrote: ``In the Fill Series dialog [....] Enter 0.041666666666666666 in the Step box and 0.9583333333333333333 in the Stop``
There is no need to enter decimal approximations. The Fill dialog accepts 1:00 and 23:00.
Moreover, the time syntax results in a more accurate result, as demonstrated in the image below.
Besides, it is useless to enter more than 15 significant digits, since that is all that Excel parses.
And BTW, the correct 17-significant-digit approximations are 0.041666666666666602 and 0.95833333333333304.
(17-significant-digit approximations are the necessary and sufficient precision to use for converting between decimal and binary representations with no loss of precision, according to the IEEE 754 standard).
-----
Be that as it may, the most accurate way to add time (or any decimal fraction) down a column is with a formula of the following form, which explicitly rounds to the desired precision:
=--TEXT(B2+"1:0", "h:m")
The following image compares the accuracy of the various ways (*) to propagate a fractional change.
(Errata.... I should have written "drag one cell" and "drag two cells", not "handle".)
The decimal comparison is of the form =D2=$A2, as we might rely on in IF and SUMIF formulas.
The binary match is of the form =ISNUMBER(MATCH(D2,$A2,0)), as we might rely on in MATCH, LOOKUP and RANK formulas.
(The difference between decimal comparisons and binary matches is due to a misguided "feature" that is unique to Excel and work-alike apps. It is a half-baked attempt to hide infinistesimal differences. It is non-standard.)
Note that all methods except adding a decimal time approximation (column D) produce results that are TRUE for all decimal comparisons.
(.... In this short example. Decimal comparison errors might occur over longer periods of time and/or with smaller increments, notably one second.)
But only the formula method produces results that are TRUE for all binary matches.
-----
(*) The "one-cell" drag method is the method described by OliverScheurich. It seems to be limited to incrementing a time constant by one hour.
The "two-cell" drag method is the more-general drag method. Enter two or more constant values (0:00 and 1:00), select the constant cells, and drag the handle in the last cell down the column.
In general, the last constant is increment by the slope of the best-fit linear regression of the constant values.
For two constants, that is simply the difference between the first two constants.
