Adding new time-steps to a complex date+time series in Excel

Question

I need to insert periodic 10-minute increments to my date-time series that is part of an existing weather station database (currently at 30-min increments), while keeping the date changes consistent for each step and day-to-day. This is a 14-month database...The highlighted rows in the picture are the original data, while the red font indicates what I have manually inserted (the process that I would like to automate for the remaining dates)--the empty spaces in the remaining columns are a required part of the process for later interpolation. Does anyone know how to accomplish automation of this? I am guessing I'll need to write some sort of code or macro or something (I am clearly not a "super"user...). I will be interpolating the missing temp. and baro. values for the missing steps later.

Answer 1

We will make the new rows below your current data then use sort to put them into the proper location. To make the new data, we will use the auto-fill feature. To get the auto-fill feature to work the way we want we have to do use twice, once for 0:10 and 0:40 and again for 0:20 and 0:50.

Because you have two separate columns for the date and time, we're going to copy our work from one column into the other so that we have identical information in the date and time columns and then use format so that it displays in the way that matches your current format and layout

You show your data as starting at 01/01/2019, if this differs, then make the appropriate changes below. I'm assuming that you know basic tasks such as how to use drag-down auto-fill and cut and paste.

In the first blank cell at the bottom of the date column

1. 01/01/2019 00:10:00

In the second cell at the bottom of the date column

2. 01/01/2019 00:40:00

Select the two cells and drag-down auto-fill until you have your 14 months. This should take just a few seconds

Now, do the above again for the 20 and 50 minute intervals.

1. 01/01/2019 00:20:00
2. 01/01/2019 00:50:00

Copy this new data and paste it under the time column so that there are two identical columns of data under your current data

Use custom sort with two sort criteria at one time, sorting first by the date column and then the time column

Set the format of the columns to match your current data format

Answer 2

Keeping in mind the Date to be changed, as Time increases, you need this formula.

How it works:

• Formula in cell AL24:

  =AJ24+(AK24+TIME(0,10,0))

Where 10 is Minute to be added.

• To make the formula dynamic you may use this one:

  =AJ24+(AK24+TIME(0,$AL$20,0))

Where cell AL20 comprises Minutes to be added.

N.B.

• Fill formula down.
• Date format is MM/DD/YY.
• Apply MM/DD/YY HH:MM AM/PM format on Column AL.
• You can find in cell AL27 & AL28 the DATE has been changed, as the TIME crosses 24 Hrs.

---

Edited:

Considering recent screen shot & comments below by OP, I'm posting a new method to achieve the goal.

Note:

• OP needs time to be incremented withing a certain time frame by 10 & 30 minutes in column B.

• Enter 24:30 in cell AK43.
• Applied cell format is h:mm AM/PM.

• Formula in cell AK44:

  =IF(AND(AK43>=TIME(24,30,0),AK43<=TIME(2,0,0)),AK43+TIME(0,10,0),AK43+TIME(0,30,0))
  

---

• You may adjust time frame

  =IF(AND(AK43>=TIME(24,30,0),AK43<=TIME(2,0,0)) 
  

  and time to be incremented

,AK43+TIME(0,10,0),AK43+TIME(0,30,0))

in the formula as needed.

• In the formula adjust cell references as required.