Generate a column of times

users
#21

Thanks again for the replies, this is what I've tried so far:

The start time was 10:25pm and the stop time was 10:55pm, with 2026 samples.
I first highlight column 'A', and format the cells as 'Time', using the
01:37:46pm format. The first cell, A1, gets the start time, 10:25:00pm. The
second cell gets 10:25:01pm, which is the start time with an increment of 1
second. I then highlight these first two cells, then drag their lower right
corner down the column to row 2026. The last cell now reads 10:58:45pm,
which does not match the stop time of 10:55pm.

#22

Well, of course not.

The difference between 10:25pm and 10:55pm is exactly 30 minutes, or
1800 seconds. But you have 2026 samples, which, at a second each, comes
to 33 minutes and 46 seconds, which is equivilent to a stop time of
10:58:46pm. The extra second is (I think) due to the first sample being
zero seconds after the start time, instead of 1, so you're actually
only adding 2025 seconds, not 2026.

Your samples are spaced more frequently than one second apart, which is
why using a fixed spacing of a second to start the series doesn't work.
Brian's previous answer about using a formula sounds like the answer,
although I haven't looked at it myself. But he knows what he is talking
about, and the approach is the correct one. It calculates the actual
sample frequency and uses that to calculate the timestamps for each
sample. Try that and see if it doesn't give you the answer you are
looking for.

Paul

#23

Thanks again for the replies, this is what I've tried so far:

Why not try one of the two techniques already suggested (both of which work)?

The start time was 10:25pm and the stop time was 10:55pm, with 2026 samples. I first highlight column 'A', and format the cells as 'Time', using the 01:37:46pm format. The first cell, A1, gets the start time, 10:25:00pm. The second cell gets 10:25:01pm, which is the start time with an increment of 1 second.

That's your mistake. You said in your original message that the samples were taken "at *approximately* 1 sample per second" (my emphasis), not exactly so. Your 2026 samples span 2025 intervals between 10:25 and 10:35, and if we divide thirty minutes by 2025, we find that the interval is actually eight-ninths of a second, not exactly one second. If you add eight-ninths of a second 2025 times to 10:25 you will get to 10:55 with no problem.

I then highlight these first two cells, then drag their lower right corner down the column to row 2026. The last cell now reads 10:58:45pm, which does not match the stop time of 10:55pm.

That's because 2025 seconds is not thirty minutes, but indeed thirty-three and three-quarter minutes. You should note that your difficulty here is entirely to do with your appreciation of the problem, and nothing to do with whether Calc can or cannot do what you require.

Using Mr Drago's technique, you need to set A2 to 10:25 PM plus eight-ninths of a second. You can do this in various ways. One is to put 10:25 PM and 10:55 PM in two cells, subtract them, and divide by 2025. As a number, this will display as 0.000010288... as the interval is that fraction of a day. Use this value in the calculation in A2, using =A1+Xn, where Xn is the result cell. Note that the value in A2 will display as 10:25:01 PM, but it is not exactly one second later - it merely displays that way with your chosen formatting. The value *stored* in the cell is nevertheless accurate. You will need to select the value in A2, copy it, and paste it back using Paste Special... with Formulas unticked in order to convert the formula into a number. Now select A1 and A2 and fill down the column. Row 2026 will read 10:55:00 PM. In fact, rounding errors mean that it misses the mark by less than a hundred-millionth of a second. Is that good enough?!

Using my formula technique, put the start and finish times in A1 and A2026. The formula you need in A2 with these new cells is
=A1+(A$2026-A$1)/2025
Select just A2 this time (not A1 and A2) and fill this down the column to A2025 (not A2026, or you will get a circular reference error, Err:522). The rounding errors will be no more than with the previous technique.

I trust this helps.

Brian Barker

#24

Thank, Brian, that last reply got it working. I can see that the timeline
created fits within the start and stop times. I can see some time values
repeated twice but that's what you'd expect with a non one sample per second
sampling rate. All I wanted was to correlate notes taken during sampling
with the data collected. One or two samples either way is acceptable.
Thanks to all.

#25

... that last reply got it working.

Good-oh!

I can see some time values repeated twice but that's what you'd expect with a non one sample per second sampling rate.

Exactly. I meant to mention this but forgot. Remember that the repetition is only in the displayed, rounded values: the actual stored values will be accurate and not repeated.

Brian Barker