Adjust "time" column when modifying row

[redkulat]

Hi everyone,

Is there a forumla to automatically adjust the "time" in a column when any specific row is modified? For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Subject[/TD]
[/TR]
[TR]
[TD]7:00 am[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7:30 am[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:00 am[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:15 am[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:00 am[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If I were to modify "7:00 am" by 15 minutes to "7:15 am", I would want all the other rows to automatically adjust time by 15 minute like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Subject[/TD]
[/TR]
[TR]
[TD]7:15 am[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7:45 am[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:15 am[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:30 am[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:15 am[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am hoping it's simpler than I thought. Any help guiding me in the direction would be super helpful, thank you!

 

[alansidman]

Assuming your data is in Column A. In cell D2, put 0:15 and format it as date h:mm then this vba code should advance it by 15 minutes. Change Cell D2 as needed.

Option Explicit


Sub AdjTime()
    Dim i As Long, lr As Long
    Dim chg As Date
    chg = Range("D2")
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        Range("A" & i) = Range("A" & i) + chg
    Next i


End Sub