SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-845740%22%20slang%3D%22en-US%22%3EAdd%20a%20value%20to%20multiple%20cells%20in%20specific%20place%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-845740%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20of%20data%20formatted%20as%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A00%3A00PM%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A00%3A00PM%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A00%3A00PM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20format%20cannot%20be%20changed%2C%20but%20I%20am%20trying%20to%20add%2010%20to%20the%20minutes%20place%2C%20increasing%20in%20every%20cell%20(10%2C20%2C30...)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%2C%20I%20would%20be%20able%20to%20use%20a%20formula%20to%20make%20these%20values%20change%20from%20the%20above%20to%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A10%3A00PM%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A20%3A00PM%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A30%3A00PM%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A40%3A00PM%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A50%3A00PM%3C%2FP%3E%3CP%3E2019-Sep-09%2008%3A00%3A00PM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20guessing%20this%20will%20involve%20a%20lot%20of%20if%2Fthan%2Felse%20related%20stuff.%3C%2FP%3E%3CP%3EI%20have%20no%20idea%20how%20to%20approach%20this.%20Any%20assistance%20would%20be%20helpful%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESummarized%3A%20Add%2010%20in%20a%20specific%20place%20in%20the%20cell%2C%20based%20on%20the%20data%20of%20the%20past%20cell(s)%2C%20and%20when%20%3A50%3A%20is%20present%2C%20add%201%20to%20the%20hours%20(7%20becomes%208)8)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2019-Sep-09%2007%3A50%3A00PM%3C%2FP%3E%3CP%3E2019-Sep-09%2008%3A00%3A00PM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-845740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-845974%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20value%20to%20multiple%20cells%20in%20specific%20place%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-845974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406249%22%20target%3D%22_blank%22%3E%40bnhx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20increment%20time%20by%2010%20Minutes%2C%20you%20may%20try%20the%20following%20macro...%3C%2FP%3E%3CP%3EIn%20the%20attached%2C%20click%20the%20button%20called%20%22Increment%20By%2010%20Minutes%22%20on%20Sheet1%20to%20increment%20Time%20in%20DateTime%20Stamp%20in%20column%20A.%3C%2FP%3E%3CP%3EThe%20code%20assumes%20that%20all%20the%20DateTime%20stamps%20are%20same%20(as%20per%20your%20description)%20and%20it%20will%20take%20the%20first%20one%20and%20start%20adding%2010%20minutes%20to%20it%20and%20does%20the%20same%20for%20other%20values%20down%20the%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20IncrementDateTimeBy10Minutes()%0ADim%20dt%20As%20Date%0ADim%20lr%20As%20Long%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%0A'Assuming%20DateTime%20strings%20are%20in%20column%20A%0Alr%20%3D%20Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%0A%0Adt%20%3D%20DateValue(Range(%22A2%22).Value)%20%2B%20TimeValue(Range(%22A2%22).Value)%0ARange(%22A2%22).Value%20%3D%20dt%0A%0Adt%20%3D%20dt%20%2B%20TimeValue(%2200%3A10%3A00%22)%0ARange(%22A3%22).Value%20%3D%20dt%0A%0ARange(%22A2%3AA3%22).AutoFill%20Destination%3A%3DRange(%22A2%3AA%22%20%26amp%3B%20lr)%2C%20Type%3A%3DxlFillDefault%0ARange(%22A2%3AA10%22%20%26amp%3B%20lr).NumberFormat%20%3D%20%22yyyy-mmm-dd%20hh%3Amm%3Ass%20am%2Fpm%22%0A%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-845975%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20value%20to%20multiple%20cells%20in%20specific%20place%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-845975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406249%22%20target%3D%22_blank%22%3E%40bnhx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406249%22%20target%3D%22_blank%22%3E%40bnhx%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20the%20following%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20IFERROR(DATE(LEFT(A2%3AA30%2C4)%2C%20MONTH(1%26amp%3BMID(A2%3AA30%2C6%2C3))%2CMID(A2%3AA30%2C10%2C2))%2BTIMEVALUE(MID(A2%3AA30%2C13%2C8)%26amp%3B%22%20%22%26amp%3BRIGHT(A2%3AA30%2C2))%2BTIME(0%2C10*(ROW(A2%3AA30)-1)%2C0)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20attached%20is%20a%20sample.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20converts%20input%20string%20in%20to%20a%20Date%2FTime%20serial%20number%20and%20keeps%20adding%20Row*10%20minutes.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20assumes%20that%20the%20input%20data%20is%20in%20range%20A2%3AA30.%20If%20there%20is%20a%20change%20you%20need%20to%20update%20formula%20for%20revised%20range.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EROW(A2%3AA20)-1)%26nbsp%3B%20%3C%2FSTRONG%3E-%20this%20component%20is%20a%20row%20dependent%20function.%20if%20your%20starting%20row%20is%20N%2C%20you%20need%20to%20do%20ROW(A2%3AA20)-%3CSTRONG%3E(N-1))%26nbsp%3B%3C%2FSTRONG%3Eto%20get%20the%20right%20row%20number.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20it%20works%20for%20you!!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-846091%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20value%20to%20multiple%20cells%20in%20specific%20place%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-846091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406249%22%20target%3D%22_blank%22%3E%40bnhx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20you%20can%20achieve%20your%20desired%20results%20with%20a%20simple%20formula%2C%20like%20this%20in%20B2%20of%20the%20attached%20file%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMAX(A2%2CB1)%2BC%242%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENote%20that%20C2%20stores%20the%20required%20time%20increment.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847127%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20value%20to%20multiple%20cells%20in%20specific%20place%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847127%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20assistance!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847128%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20value%20to%20multiple%20cells%20in%20specific%20place%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847128%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847129%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20value%20to%20multiple%20cells%20in%20specific%20place%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847129%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847140%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20value%20to%20multiple%20cells%20in%20specific%20place%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406249%22%20target%3D%22_blank%22%3E%40bnhx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20I%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
bnhx
New 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

 

2019-Sep-09 07:50:00PM

2019-Sep-09 08:00:00PM

 

7 Replies
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!
Thank you for your help!

@bnhx 

You're welcome! Glad I could help.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies