98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA conditional date/time change
VBA conditional date/time change
Resolved · High Priority · Version 2010
Fri 18 Nov 2016, 15:13Delegate Nasir said...
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
Mon 21 Nov 2016, 10:07Trainer Marius said...
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
Mon 28 Nov 2016: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Select only cell that contain text to lock formatFor selecting cells that only contain Text in Excel |
