Hi,
I have a list of time stamps. I want to insert rows automatically so they
will be in one minute intervills.

25/02/2009 23:00 382
25/02/2009 23:24 371
26/02/2009 01:15 356
26/02/2009 01:19 336
26/02/2009 01:37 356
26/02/2009 01:42 336
26/02/2009 02:40 316
26/02/2009 02:43 296
26/02/2009 02:55 276
26/02/2009 02:57 256

Mike,

I've assumed that your date time values are in column A, and are combined date time values to round
minutes.

Insert 2 new columns, let's say column at A, so that your time stamps are moved from column A to
column C, and let's also say that you have column headings in row 1. You might have dates in column
A, and times in column B, in which case you should insert another column and use = D2+E2 in cell C2,
copied down to match your values prior to continuing.

In A12 (one row below the end of your example data - adjust for your actual data table length) enter
the formula -

=ROUND(C2*1440,0)/1440

Then in A13, enter the formula

=ROUND((A12+TIMEVALUE("00:01:00"))*1440,0)/1440

Then copy A13 down until you get a value greater than the highest date/time that you have in your
list.

In B2, enter

=IF(C2<"",MATCH(C2,A:A,FALSE),ROW())

and copy down to match column A.

Then copy columns A and B, and paste values.

Select all your data, sort based on column B, then select column C and use Edit / Go to.... Special
Blanks OK, then type

=IF(A2="","",A3)

and press Ctrl-Enter.

Then copy all of column C, convert to values, then sort your entire table based on column C.

Delete columns A and B, and you're done.

You could also use a macro to perform this, but since you posted in worksheet functions, I decided
to go that way.

HTH,
Bernie
MS Excel MVP


"Mikeneedshelp" wrote in message
...

Hi,
I have a list of time stamps. I want to insert rows automatically so they
will be in one minute intervills.

25/02/2009 23:00 382
25/02/2009 23:24 371
26/02/2009 01:15 356
26/02/2009 01:19 336
26/02/2009 01:37 356
26/02/2009 01:42 336
26/02/2009 02:40 316
26/02/2009 02:43 296
26/02/2009 02:55 276
26/02/2009 02:57 256