Sep 09 2019 06:30 PM - edited Sep 09 2019 06:31 PM
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
Sep 09 2019 09:22 PM
SolutionTo 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
Sep 09 2019 09:40 PM
@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!!
Sep 10 2019 12:00 AM
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.
Sep 10 2019 09:58 AM
Sep 10 2019 10:10 AM
You're welcome! Glad I could help.
Sep 09 2019 09:22 PM
SolutionTo 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