Excel VBA Introduction Part 55.1 - Working with Dates

welcome to this wise L excel vba tutorial this video is going to give you a brief introduction to working with dates in VBA we're going to start with the absolute basics of both writing and reading dates in your VBA code I'm explain how the regional settings of your computer can affect the display of those dates we'll talk a bit about the date datatype and the range of dates available to you when you're working doing VBA dates which is slightly different to the dates available to you working in Excel so there are a few little things to watch out for there we'll talk about how to get to the current date and the current time as well using VBA functions before we move on to a quick look at formatting dates using a variety of functions and properties of cells as well the last part of the video is going to talk about various calculations you can perform a date's primarily working out the difference between two dates we'll have a look at some of excels dates functions and how you can access those in your VBA code there's are some native VBA functions before we finish off the video with a quick look at how you can calculate age in years accurately so quite a bit to do here let's get started okay so I'm going to start this new video with a brand new blank Excel workbook all of them so far is saved it as a macro enabled workbook with a new name called dates in VBA from here I want to head straight to the visual basic editor and as always I can do that I definitely felt a tab clicking visual basic or pressing the alt + f11 on the keyboard once I'm in the VBA editor I'm going to insert a brand new blank module so as usual I can right clicking the project Explorer choose insert and then choose module I'm not going to bother renaming it other we will end up with a few different modules in this workbook all I'm going to do here is create a quick simple subroutine called state basics and then explain some of the absolute basic principles of working with data in VBA ok so the very first thing I'd like to accomplish here is show you how to write date values into your VBA code and also have to store those bate values in variables so let's start by declaring a variable to hold the date I'm going to call mine some writing short like a DT shop a date and then I'm going to assign the or use the data type the only available datatype for dates in VBA which is called date so dates can hold both date values and date and time values so date is the only available date/time datatype in VBA to assign the value to it of course just like any other variable you would state the variable name followed by an equal sign what I'd like to do here is let's say I want to assign today's date so my system clocks it is the second of May 2017 just really this is definitely the second of May here I'm using UK regional settings such day month year for the basic standard date format rather than is so it's not February 5th 2017 just in case you're you're wondering about that so I'd like to assign today's date to that variable to write date values in VBA code you'll have seen this in a few previous videos in this series but you said hash marks to enclose dates just like you'd use double quotes to enclose strings use hash marks to enclose dates so if I say 2 4/5 4/20 17 close - marks and that will define that date value to that variable all I'd like to do now is read that bit value back out into various different places in my VB editor and Excel so I've already got my immediate window displayed and the locals window so I'm going to like to segment or print it to the immediate window we'll see it in locals window when we step through the code I'd also like to display it on a message box and also write it out into some cells in the worksheet so that let me make sure that I can see the ways you can restore down my DP editor window if you haven't got the immediate window and locals window displayed then the View menu is where you'll need to go so you'll see them both in the View menu and all I'm going to do then is say debug print DT I'm going to say message box DT and I'm also going to say sheet 1 range a1 dot value equals DT so I'm going to write out that value into several of different places all I'm going to do then is step through this procedure using the f8 key and hopefully you'll see when you first begin using f8 key that the locals when it displays the value of your date time variable as effectively a time format at the value of 0 so they're going to use the f8 key to continue stepping through I'll assign the value to my variable now you might already be able to notice if you can squint maybe you can just about read the font size in the locals window you might already be able to see that we've got a small tiny little issue with what's them being fed into that date variables you see it's switched from the second of the fifth - fifth of the second 3:17 if I carry on stepping through I've I've printed out the fifth of the second 2017 and unless you've got showed me the fifth of the second 2017 and finally my sheet my worksheet shows me that it needs a fifth of the second 2017 now because this is my original setting that my machine are set to be UK settings then this definitely is being read as the 5th of February 2017 in fact I can prove that just briefly if I were to format that single cell there with a long date format instead so you can clearly see that that's set to be the 5th of February not the 2nd of May so one small issue you're going to have let's just reset this procedure first one small issue you can have if you live effectively anywhere except for the US or some weather that uses the u.s. month/day/year day format then you're going to be in the stead of trouble when you start writing data values into your VBA code this is a problem that actually would have become more apparent had I been recording this video on any date after the 12th of the month I will just show you quickly what I mean by that so currently it looks here to me as I've typed in the 2nd of the 5th 2017 2nd of May have I typed that Minister 13th of May 2017 as soon as I move away from that line you can clearly see that you had the date format changes to month state year the 13 can't possibly be anything other than the day of the month so we're I to run this procedure again if I use the f8 key chapter again you'll see this time that although it's displayed as a month day year in the code that I've written anything that displays the value back to me that uses my regional settings such as the locals window 13th of the fifths and the immediate windows 13th of the fifth and of course my message box and of course sheet one range a1 13th of May 2017 so if your regional settings are anything other than essentially the u.s. month/day/year format then you need to remember that when you write your dates into VBA but it's month day yeah now it's like me being trained your entire life to write out your date formats as a dame of year there's gonna be quite a difficult thing to get used to it's absolutely fine as long as you're typing we notate this after there tells of the month but prior to that them is very easy to accidentally write it out in you're more familiar date format date month year so one way to mitigate that will be to mention your dates in a format that's completely unambiguous so let's try to enter the second of May again into this date so I'm going to type in the number two but rather than putting in a number of them what I'm going to do is write out either the short or the long misname so it just happens that for me the short name in the long name are exactly the same but you could also have Jan or January February etc so clearly that than what the word May can't possibly be misinterpreted as the number of the day of the month suddenly when I click away from this line all the date formatting changes again to go to month day yeah but the number five has replaced the word May but that's coming to the correct position so that's one other way you could potentially prevent yourself from making mistakes type out the slightly longer date format with the name of the month next let's have a look at the range of data you have available in VBA this is another potential source of error if you're working with gates in VBA and Excel because the range of data available is different to both systems let's have another quick new sub regime here let's call this something like data ranges and what I'd like to do here is have three separate variables one told each date of the minute under Maxis VBA States and the min date available in Excel so I'm going to declare a variable called demon estate and I'll also have D max estate and let's also have Tim D min Excel so I'll be excels minimum date which is different but that will also be sought as a date what I'd like to do then is assign various values to these variables so I'm going to do that by saying D min first of all equals and the earliest that you could possibly store in VBA state system is the first of January 100 so I'm going to into that as a date I'm gonna enter in a set of hash marks 1 / 1 / 100 it's impossible to put in a year earlier than the Year 100 simply because if you touch type in 99 that's going to be interpreted according to vba state system is in 1999 you'll see that clearly when you move the cursor away so 100 is the earliest year you can enter for date mbta d max is actually the same as in excel so d max is equal to the 31st that's all I should enter this should nice let me tell you what let me answer is 31 space Dec or December 9999 and then that will be moved around again of course when I moved to Custer neckline 12/31 so again month/day/year and that's the same as in excel as well the final date format of a value can radium is for excels min date so Excel doesn't actually store date/time values earlier than the first of January 1900 so what we're going to do say beam in Excel equals and I'm goin to into a set of hash marks 1 / 1 / 1900 Excel does actually have two separate date systems it can work with it can either have 1900 as the others date or 1904 but the fault settings always nineteen hundreds of a massive explicitly changed that then 1900 should be the earliest that Excel can use okay what's I'd like to do next then is simply display those values back to the end-user so let's display them let's say on a message box and also write them into new cells in a new worksheet what I'm going to do here is say message box and then I'm going to say D min with a VP newline character and then G max has a theme with a V Meath new line character and finally another understand and the min Excel so excels minimum data there we go then I'd simply like to add in a new worksheet and write out those three values into cells in words as well we're going to end counter a problem with one of these of course but this kind of the point of showing you this in the first place um as a worksheet dot add and then I'm going to say range a1 dot Val D equals D min and then it's going to copy that line and paste it in a couple of times and edit the codes so that we can say a 2 and a 3 and then say D Max and they deem in Excel okay so we display the information on the message box and also I sneaked out into cells and worksheet I'm going to step through this one Jun f8 keys let's use the f8 key to begin stepping through and you can see the three date of air variables have been populated with the default value of zero and then we'll see that the values 1 1 130 112 9999 + 1 1 1900 gets entered into the locals window if I display those on a message box that's exactly what I get first Jan 100 etcetera etc and then finally if I start writing these out into cells in a worksheet now of course this one's going to fail because Excel can't accept a date/time value of belly than the first of 1900 so if I hit FA there I'm going to get a runtime error that was kind of expected if I hit deep book what I can simply do is click and drag this yellow arrow just to skip over that line let's ignore that one for the time being then let's set the maximum date in there so we put the maximum date into cell b2 as soon as you can clearly see that so that goes in as expected 31st of the 12 9999 and then finally range a3 value the minimum value for Excel so let's hit f8 to enter that and then something goes slightly wrong and something slightly strange happens rather than entering the first of Jan 19 I get the second of Jan 1900 okay so this is a little bit unexpected unless you're familiar with that sells famous leap year bug let me see if I can try to explain what this is all about let's get another quick subroutine and let's have a name call something like the leap year bug and I'm going to declare a variable but you can hold a simple Bateson's a dim DT as date and then I'm going to add in a new worksheet to write some values into so what please dad what I like to do is set DT to be equal to and bear with me here I'm going to set this to be the first of march in 1908 of value I'm going to enter into there is one mom 1900 and of course the formatting will then set itself according to the original settings of your room of VBA which is always us month/day/year and then I'd like to write out things arranged a 1 the value of that date song as a ranging one that value equals DT what if then like to do is try to assign the value of 28th of Feb 1900 so I'm going to set this to be 28th of Feb 19 hundred and again that'll flip around into the 2/28 because 28 can't possibly be the month of the year and then set that into range a - okay so if I were to then use f8 to step through this again confidingly we should see I can just zoom in so you'll see a little more clearly that first of all the third sorry the 1st of March 3rd of March 1st of March March 1st goes in there as expected when I set the value to be the 28th of feb 10 into the locals window correctly when it gets read into Excel itself that's displayed is at 29 2009 teen hundred well that actually wasn't between scientist 7 1900 and this is a deliberative book that if you you're interested in reading about then there's a lovely fascinating article about how Excel incorrectly assumes the United original leap year um it was doing deliberately to keep it in line with another competitive program at the time called Lotus 1-2-3 you may be familiar with that if you're incredibly old like me but anyway if you're interested then that's why you get a slight discrepancy between PPA and Excel dates prior to the first of March 1994 the unlikely you're going to be using dates earlier than update this will only ever cause you a problem if you are comparing data in VBA and Excel prior to the 1st of March 1902 be using dates like that then you never ever ever have to worry about just a useless bit of trivia like many of the other things I show you my videos but it certainly worthwhile being aware of just in case just to prove that VBA can't handle the 29th of February the date February 29th 1900 that will throw you a syntax error you can't even execute the code so VBA know is but Excel clearly has this this little issue so they go something particularly interesting but potentially useful if you encountered that problem yourself now so far everything we've done assumes you're going to be writing out your date directly into your VBA code but it's actually reasonably rare that you're going to be doing that more commonly you're going to be accessing your dates from cells in the worksheet or from data imported elsewhere or connecting us to databases or even just using excels functions and formulae to calculate dates one of the most common dates you're going to be interested in in most of your calculations is going to be the current date or today's date now you may well be aware that you can calculate today's date in Excel worksheets simply by using the day function so just to quickly demonstrate that if I were to click into an Excel worksheet cell and say equals today then I can close a set of parentheses there are no parameters to the today function and if I were to enter that it will return today's date for me formatted according to my regional settings of course so to demonstrate or replicate the same sort of feature in VBA let's have a new subroutine called current date and then what I'd like to do again is declare that same variable dim DT has date and then I would like to assign today's date to that variable now the function for today's date in VBA is different to today's our sorry excels today function the name of the function confusingly in Excel that written in VBA that returns today's date is the same as the name of the data type so DT equals state from alert will store today's date if I can just quickly demonstrate that by both printing it to the image it when do I say debug print and then let's also write that up into cell a5 sometimes a range a 5 and dr. value equals DTE I should also say what I want is a debug duck printers I almost forgot there I'm just going to clear the contents of the locals when it's Arabic on the immediate window first and then if I use the f8 key to type through somewhat unsurprisingly I get the second of the fifth the second of the vests and of course the second of the fifth so exactly the same as the regional settings of my machines at the 2nd of May of course XL as another function which calculates both the gate and the current time so the function that does that is senticles now if I typed into a cell in Excel again if I set equals now and then opened and closed in parentheses that returns not just a date but also the time at which I wrote or called the function that will recalculate every time I do something else or or manually recalculate the worksheet so just a dent rate of that increases a second as well I'm going to apply a quick custom number for match of ourselves I'm just going to display the format cells dialog box let's just add on the seconds by adding : SS to the end so just to demonstrate that as I deal with the things I would use the f9 key to recalculate the worksheet you'll see that it updates the value in that cell if I were to type something into another cell even just any old random bit of text isn't really matter that will force that function to refresh as well so what I'm going to do next is replicate that same functionality in VA we're not quite the full functionality but at least the result of the now function so what I'm going to do here is in the same subroutine inside as I used earlier I'm going to say DT equals and then VBA's function is actually called the same as excels function this time so it's called now and then once again if I were to debug to print it and then change the value of a cell based on that as well let me just copy and paste those two lines and then let's say it range eight eight value can calls it DT so where I to use the f5 key to run the entire thing and then again I want to apply the same format to those cells so that I can see the seconds so the simplest thing to do here is use the format painter button to format that I could have written some code to do the same thing but we'll get into formatting dates early so one thing that you'll be able to see hopefully from this if I use the f9 key to recalculate my worksheet is that once the the now function has returned as result because we're just passing the result of the function into the cell not the actual formula itself then that won't recalculate so you can clearly see that if I hit the f9 key that it recalculates the now function but not this is just a value of course now it is possible to write out values into cells in the worksheet using VBA where the date and time will be recalculated substitute that sort of need to do is enter the actual functions into the cells rather than the results of VBA's beta time functions let's have a quick go at doing that if I switch back to the VBA editor and let's have a new subroutine call something like entered date functions so this time rather than setting the values of the cells to be equal to the result of a function I'm going to write out the function itself so to do that I'm just going to jump straight into the cell I'm not going to bother using a variable this time I'm not going to hold any results I'm going to set the bodies of the cells to calculate the results sometimes they range a 10 I've got value equals and then in some double quotes I'm still going to write out the formula that I would write out to calculate today's date so in Excel that would of course be the today function not the date function same things were just copy and paste that line of code and then set range pay 11th now these to be equals to the now a function not the result of the now function and then as I said earlier on let me just make sure that I can format the value of a 11 so that it has that time component down to the second as well so to do that I can say let me just paste in what I just copied earlier on and then I can take away the value property and use the number format property instead so range a 10 dots number format equals then in some double quotes I can write out my date format so that will be DD /mm 4/4 wise followed by a space then a couple of H's curl on a couple of M's another colon and a couple of SS as well it was a 11 M entity there sorry rather than than a 10 okay pardon so a 11 from time component okay so having done all that let me just hit the f5 key to run the entire thing and we will indeed end up with those values being entered into the cells in the worksheet but if I switch back into excel and I recalculate the worksheet again by using the f9 key you'll see that this time the results of this cell updates as well so it's exactly the same formula as I entered in manually okay so on the subject of formatting dates let's look at a couple of things that you can do in VBA terms so number format is a property of a range object in an Excel worksheet of course there are several formats functions available to you in VBA let's have a look at a couple of them in fact what I'm going to do at this time is insert a new modules let's right click and insert a module and let's have a new subroutine called something like formatting States just for convenience I'm going to declare a variable two holes today's date someone's a de Medici as date and then say DT equals date which will give me of course say Tate what I can do at that point is create a brand new worksheet sung as a worksheets dot ad and then I'm going to set the values of a few cells to be equal to the results of formatted versions of today's date so I start with range a one times a range a one dot value equals and then I'm going to use the format date/time function first of all so I use the control space keyboard shortcut to display the intellisense there's a function in their calls format date/time so in there I have a variety of different parameters the only compulsory parameter is the thing that I'm about to format which is the value hold in my DT variable but then I have a variety of built-in date formats that I could do to use the first one I'm going to go for is the general date so you'll see that that's the default anyway so if I commit this parameter then I get a general date anyway so let's put that one in there first of all then a quick case of copy and paste so I'm going to copy and paste out a couple of times and say range a to range a three and then just take away the general day format press control space again and I've got a long date and then let's go for the other one which was a short date I'm not going to bother with the the time components so long date and short date use the regional settings of my computer let's have a quick go if I use the f5 key to run that one as it stands and then just make sure that I can increase the width of my column so you can see the results so the general date format and the long date format and the short date format all conforming to the regional settings of my computer now of course that's a fairly limited list at date time formats as you might expect you've seen you can customize your date formats in Excel you can actually do the same thing in PPA as well so let's have another piece of that same line that I just just copied I'm going to say range a for dot value this time rather than using the format date/time function what I'm going to do instead is use just the generic format function so the format function doesn't use specific date formats like like the list we've seen their general date long date and short date what it does instead is allows you to enter your own custom date formats so what I can do here is in a set of double quotes I can write out essentially the same sort of date formats that we just saw in the number format property of a range object but this time let's go for something a little bit more elaborate I'm going to go for for DS which will give me the name of the day of the week for by a single D which would you be the number of the day of the month another space and then I'm going to have for MS which will give me the full month name followed by another space and for Y's which will give me the year of the date as well so having entered that if I were to run a subroutine matching the f5 key and I'll end up with yet another new worksheet I've got a few too many but you see this time I get a slightly more expanded more elaborate date format according to the format that I've typed in here one thing worthwhile bearing in mind with the results of the format function is that the results might not always be a date so this particular format function out of used here to create this clevy elaborately format has resulted in the value of the cell being treated as text I can never take the difference here actually let me just copy and paste that last line we've written together and let's change the value of cell a5 I must try to replicate the generic box standard general date format I'm going to go for a couple of these followed by a forward slash and then a couple of MS and a forward slash and let's go with four wise actually okay so if I were to run this subroutine again now I'll end up with a new worksheet with those date patties and says it but hopefully what you can see from this I vote to select that cell a4 it's got the general format whereas I select cell a5 that has got the date format it's also if I were to try to extract bits of one of these dates let me just use some symbol like the Year function to try to extract a year from any of the dates we've just looked out there we enter the Year function and then fill that formula down the column I'll end up with try again we'll end up with a value error next to this kind of more elaborate day for what it can't be treated or interpreted as a date so digit be ever so slightly careful with the elaborate date formats try to reserve those for I guess report final report rather than for things you're going to be calculating with now we've just seen that Excel has a function called year which will extract the year component of date we've also got functions called month and day which extract the month and the day components but vba has its own versions of those functions as well all called exactly the same thing let's just have another quick little subroutine please let's have new cervical dates parts and we can similarly copy and paste similar lines from the previous procedure that's probably the variable declaration in the worksheet up at and the range a1 dot value equals so we copy that out paste it into this new procedure and all I'd like to do to star where this sets the range a one value to be the year of the date and then I can just copy and paste that line and change the cell references so we've got a 2 and a 3 this might all be quite boring you may want to know all this already and it's not particularly exciting I admit but it's all sort of the groundwork for some of the more complex things you might want to be doing with dates there's also a function which will put together various separate date parts into a single continuous data so let's have one more line what this is going to do is say range a for value equals and then it's going to be rain you can use a function there called date serial so the function called based serial which if I were to open some parentheses tells you it needs a year and a month and a date component to build a complete date so I can do here simply hits copy and paste the ranging one has a year range 8 2 has the month and range a 3 has today so having done all that I can then simply run that's a between using the f5 key and will hopefully see that we get the separated out parts of the dates from the year and the month and the day and then finally you've got the date reconstructed as a second ma 2017 let's move on and look at some of the other basic calculations we can perform a date in VBA I've just done a bit of tidying up in the workbook I've deleted all the worksheets except for sheet 1 what I'd like to do in here is put in a date sometime in the future some dates that we can calculate how many days it is until so let's go with them traditional one in the UK will go for Christmas Day how many shopping data is it until Christmas and that sort of thing so I'm going to change the date in that cell - that's only 5th big voluntary fifth self 2017 I'm not I'd like to do at that point is calculate some values associated with that date so I'll switch back into the BBFS and I'm going to insert any much of this and then let's have a simple supper tune in here called basic state calculations so I'll say basic date calculations one of the simplest calculations you can perform with two dates is work out the difference between them and you can do that simply by subtracting one day from another and a very basic fashion so let's have a couple of variables just to demonstrate this let's say dim star state state and we'll also have end date as date I'm going to set the end date to be the value of cell a1 so we might want to modify that later on and start it is simply going to be today's date so say start date equals date and also end date equals sheet 1 dot a 1 dot value I get there eventually typing it in there we go start Davi okay so to work out the difference between those two dates I'd like to put the value into a cell in the worksheet let's say range a two I'm going to say range a2 dot value equals n state - start date so all dates are just numbers in VBA and in Excel and ripoff computing in general or something most of the systems I have used so if a date is just a number you can work out the difference between them by subtracting one from the other it's far way to run that one we'll end up with I usually a 5 key we'll end up with 237 days until Christmas now VBA does actually have a function designed purely for calculation the difference between dates you may have encountered it in other systems the functions called date diff so rather than just subtracting one Paperman of the date what we could also do is use the data function it's a little more complex use and for this simple example it might just be well worth subtracting one date from another but I just copy and paste that line and then replace the line with range a3 value equals and there's a date dysfunction native to VBA so the date dysfunction has effectively three compulsory parameters the first parameter is which interval of time was unit of time you interested in so if we want to know how many days it is until Christmas then I can type in a set of double quotes to letter D I thought that months I could go for MS and so on the various other options available there so I searched in a comma let's go for star state so you go with the earliest date first and then the third parameter will be the end date so the final date were interested in so if I were to execute that subroutine at that point now see that I get the exact same answer in cell a3 of the advantages of using the dysfunction is it's slightly more convenient to test for different units of time as you saw with this first parameter the interval parameter so let's have another couple of lines I have a couple of copies of this one and if I change the cell references so we're looking at cells a4 and a5 then we can change the units we're looking for two different increments so say rather than days we'll go for weeks so that's two WS and then let's say four months we'll go for M and then what I can do if you want to see the full list available by the way the simplest thing to do is just get help on the date diff function if you click from the word date diff and press the f1 key that will take your way to the correct website which will show you the list of all the different intervals of time you have available so you'll get the date date function and help page and then the list of options is they'll all down there so that's my video editor and Excel and let's just run that subroutine one more time using the f5 key and I'll see those two hundred thirty seven days or thirty four weeks or seven months as it turns out now although VBA has a handful of useful date time functions such a date if it doesn't have anywhere near like the number of and variety available to Excel itself Excel has a great big range of different date time functions so just looking at the formulas tab in Excel ribbon there's a separate date and time section and there are many more functions in this list than VBA itself has available one particularly useful one will be net workdays so the number of working days between two dates rather than just a number of old days between two days sadly VBA doesn't have its own version of that function but it can reference excels version of that function so just heading back to the VB editor in order to calculate the number of work days until Christmas then I can copy and paste and that line again let's use changes to arrange a six and to access any of excels worksheet functions then you want the worksheet function property so I say worksheet function dot Network days now something you don't get as much help when you reference these functions in VBA compared to how you see them listed in Excel but if you need any help then just use excels formula wizard to get the help on those what I'm going to do here is just enter the two days I'm interested in so it's going to start date and end date and then what skin of are words you execute that subroutine one more time I'll end up with thousands but a hundred and hundred and seventy workdays rather than two hundred thirty seven total days until Christmas just thinking back to the date dysfunction again one common reason you're going to encounter this is to calculate the difference in years between two dates so let me just do a quick separate example of this if I could another quick subroutine called calculates years difference something along those lines what I'm going to do there is create a start date and an end date variable so in fact I'm just going to cheat and copy and paste some of these lines of code there let's copy and paste those and then I this time I'd like my end date to be today's date I'd like my start date to be a specific date let's imagine let's say for the sake of argument I was born on the 1st of January in 1990 I wasn't sadly was a much much later earlier than that per term there we go so the first of jam in 1990 and what I'm then going to do is calculate the difference in years between that date and today's date so I'm going to use my data time function that was just that's just debug print this rather than write this out into cells in the worksheet so you can see when I'm trying to demonstrate here debug dot print and then I was going to copy and paste a date diff calculation and to calculate a difference in years that's for Y's between the start date and the end date and if I were to execute that that would tell me that currently I would be 27 years old as indeed a date is in 2017 ok so let's imagine that I was born not on the 1st of June 1990 let's say that I was born on say D fifth of May so I'm going to change the 1 and 1 to 5 and 5 so if I were to run now on this time the result of the formula still tells me that I'm 27 and I'm not yet if I was born on that date I'm not 27 for another 3 days this is one small limitation of the VBA date dysfunction when you ask for the difference in years it doesn't look for the entire date it looks purely the difference between that number there 1990 and that number there 2017 and difference between those two years is 27 now there are several different solutions there's sort of a problem depending on exactly which system you're working in but one simple convenient way to solve this issue in Excel is to use excels own version of the date diff function now you may well not be familiar with the data function in Excel as opposed to VBA it's spelt differently used to be an undocumented term come up just quickly show you that see if I can show you what I mean by that if I clicked into a cell in the worksheet and said equals a date diff you'll see that date diff doesn't appear in the intellisense list so if I persevere and type in date gift start with a single F rather than two F's in Excel and then open up some parentheses you'll see that date diff does pop up in the list now depending on which version of Excel you're using I happen to be using Excel 2016 I can't remember which version of Excel this was introduced in but Excel you too contain absolutely no documentation about the dates dysfunction whatsoever as of the later versions of Excel so 2015 certainly if I clicked on this little hyperlink here for date if I do actually get some help there's some documentation for the day-to-day function which is really handy beyond the order of the parameters is slightly different budget it escaped to abandon typing now since I can scroll down the order of the parameters is different it's start date end date and then unit or interval of time and the units are slightly different in the Excel stated if function compared to VBA squares is a single Y for the number of complete years so one of the nice things about excels day gif is it does only calculate complete years not the overall difference in years between two dates so taught to use that the other problem that I've got as well sadly is that because as you saw dates diff doesn't appear in this list here it also doesn't appear in the worksheet function list in VBA which is a little bit of a pain if I want to use it in VBA so what I can do instead let me show you there is the the solutions if I say debug print there's a function in VBA called evaluate and what the evaluate function does is it allows you to pass in if actually a string of text which can then be evaluated in the VBA engine and then it will return the results to you so what I can do is enter in a set of double quotes that dates in diff function then open up some parentheses now the problem that I've got here is I need to read to be sort of referring to formula and valve using the worksheet I can't refer to variable names in VBA that's one slight awkwardness I was like oh so you're good thing about this but if I set my start date to be cell a1 on this particular sheet and was a date if a 1 comma then I want to use today's date some use this today function as the end date and then finally I'll need to enter or refer to the letter Y to get the number of years now don't if you can see here that the Y needs to be enclosed in double quotes and that's another issue for me in VBA because if I typed in if I tried to type in some double quotes wrapped around the letter Y this isn't going to work at what it would appear to what would happen is Excel would think that I've ended this set of double quotes here so that's one piece of text I started the letter Y there then I've began another string of text here so the solution to that is doing two double double quotes basically to wrap up the letter Y in a single set of double quotes or very awkward lots of way places that this can go wrong finally the Magnum close double quotes to contain that entire thing that I want to evaluate and then close the extra set of round brackets right at the end and finally if I were to change the date in here to the fifth of the fifth 1990 just so I've got the value typed in somewhere sensible in the worksheet and if I were to execute this subroutine one more time I'll find out that finally they diff VBA's version gives me 27 the evaluate function using date diff in Excel version gives me 26 which is exactly what I wanted now although this technique does work it's not particularly elegant is it and it's prone to a few limitations as well I'm going to be working in Excel VBA in order to use the bit dysfunction it's also awkward to reference the values of variables in my VBA code it works much more neatly if I refer to cell references what I've have to do is refer to my variables in such a way that I could write out the date into the final string expression to allow that to be interpreted properly so because of all those limitations what's worthwhile doing or what's worth are thinking about if you're likely to be calculating aging years frequently it's writing your own custom function to do so so let's have a new procedure down at the bottom of this module it's going to be a function rather than a subroutine I'm going to call it age in years I'd like my function to have two separate parameters both passing by Val so that I don't affect the values that have been passed into them in the calling procedure so I say by Val start date as date and then we'll also have by Val end date as date your greens much more in what by Val means we have another video in this series that explains the difference between by Vallum by a ref which is the default but hopefully you're familiar with that's enough by now we've done it enough times in this series so I've by Val end date as date I like the function to return an integer so as you say as integer and that's the basic definition of my function created now I'd also like to make the end date parameter optional and assume that if I didn't pass in a value to that parameter that the end date would be treated as today's date so in order to make a parameter optional then you can simply add the word optional in front of the parameters name we again this is a technique we've used in previous videos in the series you can also assign a default value to parameters and to do that ordinarily what you would do is type in an equal sign at the end and then assign the constant value you want now sadly because I want to return today's date that's not a constant today's date is similar changes and I can't add expressions to default values for parameters so I'd have to take that or approach that and solve that problem in a slightly different way and the way I'm going to do that is in the function itself so if you remember when you first begin a procedure that uses date variables in that if I just use the f8 key to begin stepping through this subroutine you'll see that beta variables begin with a default value of effectively zero but formatted as a unit of time so hours minutes and seconds so what I could do here is I could check in my function if end date equals zero if we can spell ends date properly that would have a great chance of working so if enter date equals zero then I'm going to set end date and until it's again an end date equals eight such that first basic problem solved now the technique I'm going to use to calculate the aging years is fairly basic but it helps you to explain what happens at each different stage so I start by declaring a couple of variables and as a dim years diff as integer then I'm also going to say dim anniversary as state so the first thing I'm going to calculate after I've worked out the default value for the N state parameter assuming that I've missed one out is I'm going to calculate the difference in years between the start date and the end date and the way I'm going to do that is using the date diff function that we used in the previous example so let's just copy that and then say years diff equals and then paste that in to give us a difference in years between those two dates the next step is to calculate the anniversary date of somebody whose birth date all who start date was the date we pass in so for example if I calculated that this start date was the fifth of May 1990 and years DF has calculated that the difference in years was 27 what I would like you is effectively at 27 years on to the start date to give me the same date in this year so I'm going to have 27 years of the fifth of May 1990 that will give me the fifth of May 2017 so to do that I'm going to say anniversary equals then another useful function in VBA is called date add survey date add open up some parentheses the first parameter is interval again so it's essentially the same as for date diffs that's going to be for Y's followed by the number of those intervals I would like to add that's whatever number is currently stored in the years v unction so I can say years diff finally I need to say which date I want to add that number of years on to so I want to add that number of years on to the star state to give me the anniversary in this year the next thing they need to do is calculate whether the anniversary date has happened yet because if the anniversary has not yet happened in this current year then the date dysfunctions return the wrong answer is one too many so what I'm going to do here is use another if statement to check if anniversary is greater than the end date type a sim if that's true then what I would like to do is subtract one from the Year stiff results that say years diff equals years diff minus one so that will return the correct result if my anniversary hasn't happened if my anniversary has already happened then I don't need to do anything so years diff was actually the correct answer all I've got to do now is return the results to the function so to do that I can write in the function name aging years and I can make this equal to years deaf okay so all that remains at this point is to give the function a quick test probably the easiest way to do that is to use the previous sub regime I've just cleared the contents of the immediate window by selecting all the text and hitting the Delete key let's have a quick extra debug print statement in here so let's start by saying debug dot print will go with age in years and then I'm going to pass in just the start a so the start dating this procedure was the fifth of May 1990 so let's go with start date I don't need to pass in the end date you can see that's an optional parameter so I'm going to omit that the time being and then if I were to run that subroutine I should end up with the correct answer evaluate in 18 years and the incorrect answer for date diff because my birth date my birthday my anniversary hasn't yet happened in this year so I hit the f5 key to run that I get 27 for date if I'm reported as one year too old and then I get the correct answer for the other two I can modify this in a couple of different way so I can specify the exact end dates I could just pass in the date function and then again if I run that one again using the f5 key your cell return the correct results 27 26 26 and just clear the contents of the immediate window there I could also pass in a different date altogether so let's remove the date function and use a set of hash marks to pass in a specific date let's say I wanted to test the result as of let's say what let's go for the 5th of February I know 2002 so I'm going to go fill the 5 Seb 2002 so in 2002 the person born on the fifth of May 1990 will become 12 years old but until the 5th of May that person will still be 11 years old of course so this should return the result of 11 if I use the f5 key to run it 3 7 26 11 as soon as I hit the 5th of May however for that year I get 5th of May that year that person will become 12 so there we go so there's another option this looks a bit more long-winded of course to write out your own custom function but least you're more confident in exactly what the result is going to be if you do it yourself if you like what you've seen here why not head over to the wives our website where you can find those more free resources including these videos some rhythm blogs and tutorials even some exercises that you download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 32,234

Rating: 4.9694657 out of 5

Keywords: excel vba, visual basic for applications, introduction, tutorial, training, free, online, date, dates, datediff, datedif, worksheetfunction, dateadd, year, month, day, age in years, wise owl

Id: rZScXs8tfFM

Length: 46min 56sec (2816 seconds)

Published: Wed May 03 2017