Adding dates to a column and keeping data associated with the proper date

**dannybten23** · 06-19-2013, 05:39 PM

I have a column with dates and times, and an associated column with data for all the dates and times. I want to add every hour of every day to the date/time column, but want the data associated with the times to stay in the row next to the correct time. How would I do this? In the attached file, i want to make coulmns A and B look like Coulnds E and F. I have hundreds (if not thousands) of rows, so i need a way to do this easily. Also, the times are not all evenly spaced or exactly on the hour. I dont need the times to be exactly on the hour or spaced out evenly, but i would like to have at least one time from every hour.

Any suggestions will be appreciated!
DBdate time alter.xlsx

**oeldere** · 06-19-2013, 05:43 PM

@dannybten23

The file your posted is not consistent.

You add data in between (in column E and F) that are not in A and B.

Also the data are not a- or decending ordered.

**newdoverman** · 06-19-2013, 08:13 PM

I created a column of dates and times starting 1 hour before the oldest time in your data. This was done by entering the date and time in H1 and in H2 entered =H1+1/24 and copied down column H. I then selected this column of dates and times and copied and pasted back as Values. This eliminated the formulae that produced the dates and times.

Next, I selected the existing data (dates times and associated values) and copied and pasted that data on the bottom of the dates and times that I created in Column H.

Next, I selected columns H and I and Custom Sorted on Column H Oldest to newest.

The results you will see in the attached file.