Excel VBA Basics #23 DATEADD - Add or Subtract Minutes, Months, Weekdays, Seconds whatever!!

hey there are youtubers welcome back this is Dan strong with excel vba is fun today we're going to talk about the date add data add function and VBA why I had a question from one of our viewers and they were asking how do you add two or three months to the date exactly two or three months so keep it the first of each month and there's a long answer in her short answer and the short answer is using the date add function instead of messing with this a day - that day plus you know 30 days or plus the next month or whatever and then extracting the month and extract in the year and then adding a month and then if if the month is a twelfth month and you have to add once you know if you add one month and then you have to add one to the year well this is so much simpler so we're gonna go over a lot of different ways that you can add for example you can add minutes you can add seconds you can add a couple months two or three months to whatever date you start with or you can even add a quarter or a year or whatever so let's get started with let's just have our starting date let's hit at alt f11 as we always do and to get started let's go ahead and pull up a new module so we'll click down here and click on module so now we're in the thick of it so what are we going to call our new macro let's call it sub date add test we don't want to call it date add or Excel get mad that we're using the same one as a built-in function so what's our starting date we'll call it st date is let's just say 12 and 13 1986 okay so let's hit f8 and f8 and see what fills in to st date see it's got that just gonna take it as text we might want to do a C date that's another lesson but basically C date will convert whatever expression you put into it as a date and converted in a date format instead of a text string type of format so let's let's go back and hit f8 and it should take that so now we have our starting date now let's manipulate it using date add so we'll say let's add well you know you know that we can add plus or minus a number of days by just saying plus three that would make it to 1216 1986 so we could always say and our new number is going to be equal to St date plus 3 that's the easy way of adding like three days to it if I hit f8 that would be well it's not telling so let's debug dot print and we'll say what we want to know what this noon um is if I see there if I hit enter it says that it was a sixteenth so let's take our new number and let's make that equal to our start our let's use date add I want to hit my open parentheses you'll see you take the interval let's see if you can see this you take the interval number so for example what type of interval we're gonna use how about let's add one month to it let's use the letter M for month over here so use it as a string that a string means it's a text surrounded by quotes now I hit comma the number how many months do we want to add to the date here let's add five months exactly to the starting date here and what's the date we're going to use you could either type in a date in quotes like 12 13 1986 but we want to keep this nice and dynamic we want to keep it or we can change it any time we want so let's just use st date we've already locked in that date here so let's hit that here now I'm going to backtrack my arrow to the new number so new num is going to be equal to the date add using months and how many months five and we're adding it to this date so let's hit f8 and then we'll double check here what is new number we're going to debug print the phrase new number and now we see that it is 513 1987 so it just added five months exactly to our starting date that's pretty sweet let's add let's add five weeks ooh let's add five weeks I just changed that to a double you looking over here excuse me five week days if you want to do five weeks it would be ww-well this is exciting let's add weekdays so I guess Monday through Friday let's add let's add five weekdays to that hit f8 debug print hit enter so I'd be 1218 let's add 60 weekdays and hit f8 debug print so as you can see the date has just changed to that so there's a lot you can do with that again this is okay so weekdays would be kind of like days you could put the D letter D for days or you could say I want to add 60 weeks to the day - that right there so let's try that we're gonna add 60 weeks ooh debug different so I b-26 1988 interesting how about we want to add in this light in this lady's case she wanted to add excuse me she wanted to add one quarter to the date each time so just one quarter or three months so you could put the letter in there and put three or you could put a Q and hit one and she wanted it to increment each quarter so this is what we ended up doing is it took that and then let's say she wanted to loop this for example you could say 4 X is 1 - 4 or 1 - however many you want then we're going to end it this next X alright so in this case if we start it over let's hit let's hit that there so f 8 f 8 in fact each time we're just going to debug that print I don't know why it's not letting me hover over it debug print our new number okay so we'll take that well let's let's let's think about that X is 1 2 4 and then we'll take that so be our starting date and then there's our first quarter I have this starting date okay so we need to change this from saying a new number to using the same variable so the starting date is going to be whatever the last starting date was well goodness no no we need to take this out that's the whole equation and put at the beginning before the loop okay so all right I got this so the initial starting date is going to be 12 13 1986 then we start to loop the first time around and the start date is going to be equal to 1/4 later than the original start date and so when we debug print st date it'll show up down here let's clear all these previous ones so we can see hit f8 so there's the first one hit next and then start date is going to be adding another quarter and we'll debug print that down here so 613 then we'll go up another quarter 9 13 and 12 13 1987 so it just incremented four quarters so that's pretty awesome anyway there's multiple applications if you're working with time you could use the hours the minutes the seconds so that's pretty cool we could have the starting date actually be today's day by typing the word date at the beginning let's run this macro again f8f8f8 equals date which is today's date so then when we dub debug print let's erase all this hit if 8 7 6 2013 that'd be 1/4 away and then another quarter another quarter another quarter you could use that with the time I believe time if you type that in would be exactly like saying equals and now in an excel sheet but time puts in the current time so let's see how that works and then instead of adding quarters we will add minutes we will add 10 minutes to the time and then we'll debug print that down here that'll be interesting hit f8 so we should change this to start time but I'm lazy so it's still st date so from 1 to 4 so the current time is actually 159 p.m. and this will be let's see yeah that's right oh my goodness did I put o km as month not minutes how about in for a minute apologies everybody let's rerun this one I'll erase all that so wondered why I was putting the date in there it's because I was adding months ok so it's right two o'clock on the dot so this should say to 10 p.m. yep to 20 to 30 to 40 p.m. so that is pretty freakin sweet you guys anyway play around with that it's some really easy way to add and subtract times in fact I wonder if we could do negative 10 I did I've never tried using negative numbers there but I imagine a function the same so I should say 150 p.m. if it works oh my gosh we took away 10 minutes oh my goodness Wow okay so play around with that thank you so much for watching guys god bless

Channel: ExcelVbaIsFun

Views: 34,141

Rating: 4.9053254 out of 5

Keywords: Microsoft Excel (Software), macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, microsoft visual basic, thisworkbook, object, vba basics, vba tutorial, visual basic tutorial, range, excel, excel tutorial, excel basics, easy excel, easy vba, easy visual basic, minute, month, week, weekday, second, hour, add, subtract, increment, decriment, dates, date, date function, date math, time

Id: mM5Itb2NNM8

Length: 10min 59sec (659 seconds)

Published: Sat Apr 06 2013