Generate a pattern of numbers

[hd625b]

I would like to generate a pattern of numbers. The data is from the electric company. There is an entry for each hour of electric usage per day so the 24 hours of July 1 is group 214 and July 2 would be group 215 and so on. The date set is over 5000 lines so entering this manually would take days.

Start, Stop, Usage, Group

2014-07-01 00:00:00, 2014-07-01 01:00, 0.1, 214

2014-07-01 01:00:00, 2014-07-01 02:00, 0.14, 214

2014-07-01 02:00:00, 2014-07-01 03:00, 0.13, 214

…

2014-07-01 21:00:00, 2014-07-01 22:00, 0.27, 214

2014-07-01 22:00:00, 2014-07-01 23:00, 0.18, 214

2014-07-01 23:00:00, 2014-07-02 00:00, 0.12, 214

2014-07-02 00:00:00, 2014-07-02 01:00, 0.29, 215

2014-07-02 01:00:00, 2014-07-02 02:00, 0.21, 215

2014-07-02 02:00:00, 2014-07-02 03:00, 0.14, 215

…

2014-07-02 21:00:00, 2014-07-02 22:00, 0.48, 215

2014-07-02 22:00:00, 2014-07-02 23:00, 0.35, 215

2014-07-02 23:00:00, 2014-07-03 00:00, 0.24, 215

Thank You.
MtL

[karolus]

which part of the Datasets do you want to generate?

[hd625b]

The Group column.

[Lupp]

What you call group you describe as a serial number of the day. 2014-07-01 being day number 214 will tell us then that 2013-11-30 was the starting day (number 1) of this numbering and thus 2013-11-29 was day number 0.

Assuming the ‘StartingTimeStamp’ of an hour is in column A, Row (say) 21, your formula in cell D21 should be: =INT(A21)-VALUE("2013-11-29"). The constant part should, if possible, be provided as the content of a constant cell, say F1. Then your formula will read =INT(A21)-$F$1.