SOLVED

Add a value to multiple cells in specific place in a column

Copper Contributor

I have a column of data formatted as

 

2019-Sep-09 07:00:00PM

2019-Sep-09 07:00:00PM

2019-Sep-09 07:00:00PM

 

This format cannot be changed, but I am trying to add 10 to the minutes place, increasing in every cell (10,20,30...)

 

Ideally, I would be able to use a formula to make these values change from the above to

 

2019-Sep-09 07:10:00PM

2019-Sep-09 07:20:00PM

2019-Sep-09 07:30:00PM

2019-Sep-09 07:40:00PM

2019-Sep-09 07:50:00PM

2019-Sep-09 08:00:00PM

 

I'm guessing this will involve a lot of if/than/else related stuff.

I have no idea how to approach this. Any assistance would be helpful

 

Summarized: Add 10 in a specific place in the cell, based on the data of the past cell(s), and when :50: is present, add 1 to the hours (7 becomes 8)8)

 

2019-Sep-09 07:50:00PM

2019-Sep-09 08:00:00PM

 

5 Replies
best response confirmed by bnhx (Copper Contributor)
Solution

@bnhx 

To increment time by 10 Minutes, you may try the following macro...

In the attached, click the button called "Increment By 10 Minutes" on Sheet1 to increment Time in DateTime Stamp in column A.

The code assumes that all the DateTime stamps are same (as per your description) and it will take the first one and start adding 10 minutes to it and does the same for other values down the rows.

 

Sub IncrementDateTimeBy10Minutes()
Dim dt As Date
Dim lr As Long

Application.ScreenUpdating = False

'Assuming DateTime strings are in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row

dt = DateValue(Range("A2").Value) + TimeValue(Range("A2").Value)
Range("A2").Value = dt

dt = dt + TimeValue("00:10:00")
Range("A3").Value = dt

Range("A2:A3").AutoFill Destination:=Range("A2:A" & lr), Type:=xlFillDefault
Range("A2:A10" & lr).NumberFormat = "yyyy-mmm-dd hh:mm:ss am/pm"

Application.ScreenUpdating = True
End Sub

 

 

@bnhx 

@bnhx , 

Please try the following formula. 

= IFERROR(DATE(LEFT(A2:A30,4), MONTH(1&MID(A2:A30,6,3)),MID(A2:A30,10,2))+TIMEVALUE(MID(A2:A30,13,8)&" "&RIGHT(A2:A30,2))+TIME(0,10*(ROW(A2:A30)-1),0),"")

 

Also attached is a sample. 

This formula converts input string in to a Date/Time serial number and keeps adding Row*10 minutes. 

This formula assumes that the input data is in range A2:A30. If there is a change you need to update formula for revised range.  

ROW(A2:A20)-1)  - this component is a row dependent function. if your starting row is N, you need to do ROW(A2:A20)-(N-1)) to get the right row number.   

 

hope it works for you!! 

 

@bnhx 

I believe you can achieve your desired results with a simple formula, like this in B2 of the attached file: 

=MAX(A2,B1)+C$2

Note that C2 stores the required time increment. 

Thank you for your assistance!

@bnhx 

You're welcome! Glad I could help.

1 best response

Accepted Solutions
best response confirmed by bnhx (Copper Contributor)
Solution

@bnhx 

To increment time by 10 Minutes, you may try the following macro...

In the attached, click the button called "Increment By 10 Minutes" on Sheet1 to increment Time in DateTime Stamp in column A.

The code assumes that all the DateTime stamps are same (as per your description) and it will take the first one and start adding 10 minutes to it and does the same for other values down the rows.

 

Sub IncrementDateTimeBy10Minutes()
Dim dt As Date
Dim lr As Long

Application.ScreenUpdating = False

'Assuming DateTime strings are in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row

dt = DateValue(Range("A2").Value) + TimeValue(Range("A2").Value)
Range("A2").Value = dt

dt = dt + TimeValue("00:10:00")
Range("A3").Value = dt

Range("A2:A3").AutoFill Destination:=Range("A2:A" & lr), Type:=xlFillDefault
Range("A2:A10" & lr).NumberFormat = "yyyy-mmm-dd hh:mm:ss am/pm"

Application.ScreenUpdating = True
End Sub

 

 

View solution in original post