Forum Discussion

bnhx's avatar
bnhx
Copper Contributor
Sep 10, 2019
Solved

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

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 😎😎

 

2019-Sep-09 07:50:00PM

2019-Sep-09 08:00:00PM

 

excel
Formulas and Functions
Macros and VBA
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Sep 10, 2019

    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

     

     

5 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Sep 10, 2019

    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. 

  • Kodipady's avatar
    Kodipady
    Iron Contributor
    Sep 10, 2019

    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!! 

     

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Sep 10, 2019

    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

     

     

Resources