Nasir has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
VBA conditional date/time change
Hi,
Can someone please me with below. Basically we have to report our trades to the regulator daily, new and amended trades. every trade has a reference and based on trade ref I wanted to change the date/time by an hour except for 1 week from when clocks change.
basically we extract days activity on an excel. column A has trade refs start with ldn or nyk eg ldn02ce0efed or nyk1064edb4. the trade execution date and time are in column Q in format yyyy-mm-ddThh:mm:ss.000 eg 2016-11-17T16:31:44.000. in loop and with if function I want go through all trade refs in column A convert corresponding dates to format yyyymmddhhmmss and if trade ref starts with nyk then add an hour.
Many thanks
Nasir
RE: VBA conditional date/time change
Hi Nasir,
Thank you for your question. One way you could solve this is to use an If Left formula which will add an hour to all records which contain nyk. Please see below for an example. I used columns A, B and C, so you would need to adjust the references in your code to look at the correct columns in your spreadsheet.
Sub AddHour()
Range("c1").Select
Do Until ActiveCell.Offset(0, -2) = ""
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-2],3)=""nyk"",RC[-1]+(1/24),RC[-1])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
So the RC[-1] and RC[-2] references in your formula need to be adjusted along with the loop references.
I hope this helps.
Kind regards
Marius Barnard
Excel Trainer
