And future dates and for the final part of the video how to calculate the difference between dates including how to calculate the age in years accurately so let's get started if you've been following along with previous parts of this series you'll be fairly familiar with the basic setup by now so you may prefer just to skip to the next part of the video if you haven't seen previous parts of the series here's a quick look at the basic setup we've got a basic macro enabled workbook which allows us to run a query by clicking this fairly obviously labeled.
Button when we do that it's going to extract some information from a separate excel file called movies and the movies workbook contains a list of films and of actors in different worksheets and we'll be extracting data from each worksheet at different parts of the video you're welcome to keep the movies workbook open or close it down all of the code will still work regardless i've got both of these files stored in the same folder and i'll drop a link in the video description so that you can download these files and follow along and write the code with me if you'd like to.
The code in the data expressions workbook relies heavily on microsoft activex data objects i'm not going to talk about that too much in this video we've got a separate playlist which covers that this series is meant to focus on the sql side of things if you are interested in the activex data object side of things and how to establish connections to workbooks etc then i'd recommend starting with this video here how do i get data from a closed excel file using vba in the excel vba working with databases playlist and again i'll drop a link in the video description so that you can.
Find that one easily enough just have a quick look at the basic code in the data expressions workbook when we click the run query button that triggers a subroutine called createsql query and the job of this subroutine is really just to construct the string which represents our select statement so i've got a couple of basic ones one that selects from the film table and one that selects from the actors table which is currently commented out when we constructed the string we then pass that into a separate subroutine.
Which deals with all the complicated stuff such as connecting to the excel workbook using activex data objects it populates a record set using the results of our sql query so that's where our query text gets used to populate the source property of the record set and then there's a bunch of extra code that writes out the information returned in the record set to a brand new excel worksheet just have a very quick look at how that actually works if we switch back to the menu sheet in the data expressions workbook click the run query button it extracts.
The code which does that is contained in the get query results subroutine if i just quickly scroll down to show you that section there's an if statement here which checks if the data type of the column is a date data type then it applies formatting to the cell containing those dates in the excel worksheet now that's fine as long as you're writing your data out into a worksheet but you won't necessarily always be doing that so let's see how we can apply some formatting to the query columns rather than to the excel worksheets after the date has been written out i'll.
Scroll back up to the top of this subroutine and then i'm going to add a new column to our select list it's going to involve the film release date so rather than write out the entire thing from scratch i'm going to copy that column and then just paste it in immediately below i'm then going to use a function that allows us to format a date or a time it's got a clever name it's called format date time and then if i open up some round brackets after that i've got two arguments i need to fill in first of all the dates all the time that we want.
To format so that's the film's release date followed by a comma and then a number which indicates which particular date format to use i'm going to enter the number one here which corresponds to the sun the standard system long date format so i'm going to assign an alias to this column to say as fdt short for format date time long date and i'll wrap that up in some square brackets the other date formats you can apply.
Using the format date time function if i copy and paste that line of code if i change the number of the format the number two i can change that to a short date so having done that if i head back to my menu sheet click the run query button again you'll see that this time i've got the dates formatted not using the vba code in the separate get query result subroutine but using the format date time function.
In the query itself now there is something of a difference in terms of the output of our two new columns if i compare this with the original release date column if i just change the width of that to make it a bit easier to see you can hopefully make out that the date values in that column are aligned to the right hand side of the cell and if i selected one of these values for example o3 may 2002 the formula bar shows me the standard short date format for that date.
The two new columns have all their values aligned to the left hand side of the cell and if i selected o3 may 2002 you can
See that that's exactly what is shown in the formula bar so excel is treating these values as text that's the data type of the output of the format date time function another good clue that this or these two columns aren't being treated as dates any longer is the code that we're using to format the cells after the data has been written into the excel worksheet i scroll back down to find that if function again or that if statement i.Should say if the type of those columns was a date data type then they would be reformatted to have this new date format so this same one that is applied currently to the release date column so the fact that those two new columns don't have this format is another clue that the data type of those columns is no longer date that's not really something we need to do anything about you can still write formulae in the excel worksheet which treats these values as dates so for example if i.
Use the basic excel function to calculate the year from either of those two cells it will happily calculate the year from that string of text because it can be treated as a date excel will happily do so and if i do the exact same thing for this cell here in fact if i try to do the exact same thing for this cell here it will also be treated as a date even though technically it's stored as text the format datetime function is useful but it is limited in terms of the number of date formats it can produce.
If we want a little more flexibility in our date formats we could use a different function instead let's head back to the visual basic editor and i'm going to scroll up to the top of the subroutine top of the module i should say and then i'm just going to copy one of my two formats date time function line so i'll copy the format date time long date line paste that in just below and then i'm going to change the name of the function from format date time to just format the two parameters i'm.
Going to fill in here are the date that i want to apply my format to and then the specific format that i would like to apply to that value but rather than entering this as a number we enter this as a string so i'm going to open up some single quotes and then actually just to begin with i'm going to reproduce what we did with the format date time function but i'm going to do this by typing in the name of the long date format i can then close the single quotes and i'll just change the name of this column or the alias of this column to say.
F long date and then i'm just going to copy and paste that line again and then change the name of the function or sorry the name of the format to short date and do the same thing for the column alias having done that if i head back to my menu sheet and i run that query again you can see we've basically got exactly the same output from the format function as we did with the format date time function and again the data type of the.
Output of that function is treated as text as well as using named date formats you can also type in your own custom date formats using combinations of the letter d m and y just to demonstrate how that works let's head back to the visual basic editor i'm going to copy and paste the last line we put into our select list and then i'm just going to change the alias of that column and call it custom date then i can take away the.
Named date format short date and still inside the single quotes start by typing in a single letter d and that's a perfectly legitimate date format and if i were to run my query at that point it would format my date to show just the single digit day of the month so imagine my film was released on today's date the 5th of june 2021 the date format for that column would come out as the number 5. if i type in 2ds that would come out as zero.
Five if i type in three ds that would come out as the short name of the day of the week so sat and then four ds will come out as saturday if i then type in a space i can type in a single d after that so that will come out to saturday five a single d will also handle dates from the tenth of the month as well so if i type in a d and it was the tenth of the month that would still come out as the number ten not just a one or a zero so four d's followed by a space followed by a single d.
And then another space and it's the same principle for the month so if i type in a single letter m that would come out as the number six
Two m's would be zero six three would be jun and four would be june another space and then for years you want either two or four y's so yy would give you the year without the century and four wise would give you the year with the century you can also use combinations of punctuation characters in here so if you wanted to separate these different portions with hyphens or forward slash.Characters you can include those as well i think this one works a little more neatly just using spaces rather than punctuation anyway having done all that let's head back to the menu sheet run that query again and we get our own fairly elaborate custom date format in the final column now let's move on to look at some functions which are designed to extract certain portions of a date i'm just going to tidy up a little bit first i'm going to go to my menu sheet and click the delete all but menu sheet.
Button to get rid of every sheet except for the menu sheet and then back in the visual basic editor i'm going to get rid of a couple of these redundant columns let's get rid of the format date time columns they do just what the format function does with the long date and short date named formats for the next column let's create a new line so i'm going to insert a new line with some double quote characters beginning with a comma and then i'm going to use a function called year open some round brackets and then simply pass in the reference to the.
Release date field you can probably guess what portion of the date that that function is going to return it is of course going to be the year so i'm going to assign an alias to that column i'll call this one release year and there's a similarly simple function that you can use to extract the month and the day and the weekday so let's concatenate a continuation character and then rather than writing all that out again.
From scratch let's just copy the year function or the entire line involving the year function and then change the year to say month in both the function name and the column alias we'll paste it in again just below and we'll extract the day so this is the day of the month let's call this release day of month and i'll spell month correctly eventually and then finally we'll have a function called weekday.
Release day of week okay so four specific functions designed to extract a specific piece of a date if we head back to the menu sheet and run that query we'll find we get these four new columns each showing us the correct portion from the original date the four functions we've just used there will return their result as a number but what if you wanted to.
Return a piece of text instead let's say that instead of the month number you want it to return the name of the month now probably the simplest way to do this is just to use the format function and apply a format using four m's and that would just return the name of the month of the release date but just to show you this one other function that we have available as well if i head back to the visual basic editor i'm going to start by copying and pasting the line which calculates the month number because we do actually need this for the next function to work if i paste that in and.
Then just change the alias to say release month name and then at the beginning of that line just after the comma i'm going to type in the function month name open up some round brackets and then close an extra set of round brackets after the film release date so the month name function accepts a number between 1 and 12 and then returns the name of the month from january to december if i head back to the menu sheet and i run.
That query you can hopefully see that we've got their full long month name shown in the next column it's also possible to return the short month name so the equivalent of using the format function and using three ms to do that i can specify a second argument of the month name function so we have the month function nested in here as the first argument type in a comma and then say true which indicates that you do want to abbreviate the names of the months so if i head back to the menu sheet and.
I run that query again we'll get the same information just with abbreviated month names rather than the full ones we can do a similar thing to get the name of the day of the week although again it's much easier to do this using the format function and using either three or four ds depending on whether you want the short or full day of the week name but again just to demonstrate how the weekday name function works and one little potential problem with this i'm going to copy and paste my release day of week column definition and then.
Paste that in at the end of the select list release date of week name is the new name i'm going to give to that column and then in the same pattern we used for the month name i'm going to write weekday name in front of the weekday function open up some round brackets and then close an extra set of round brackets after the film's release date now at this point if i head back to my menu sheet and i choose to run that query you may notice a subtle difference in terms of the results so with our custom.
Date format where we simply formatted that date to include the name of the day of the week jurassic park was released on a friday if i head across to the new column we've just created apparently now jurassic park was released on a saturday now this is due to another setting in the both the weekday and the weekday name functions actually which allow you to specify which day is the first day of the week if i have a quick look back at a help page for either the weekday function or the weekday name functions each of.
These indicates they have a first day of week parameter which you can set to be any day of the week and the default is to have sunday as the first day of the week i'm going to change mine to say that monday is the first day of the week by setting the day of week parameter to the number two so to do that i'm going to head back to the visual basic editor and i'll do this inside the weekday function so after i've specified f dot release date i can type in a comma.
Followed by the number 2. if i then head back to the menu sheet and i run that query again i'll see this time i'm releasing jurassic park on a friday which is the correct day of the week just as with the month name function is possible to shorten or abbreviate the month name you can abbreviate the weekday name as well there's a separate parameter in the weekday name function which allows you to abbreviate the the day name so to do that i'm going to head back to the visual basic editor.
And then after i've closed off the weekday function so in between those two closed round brackets i can set the abbreviate parameter to true and one more time if i head back to the menu sheet and run that query i'll get the abbreviated day of the week name at this point i'm just going to tidy things up a little bit so i'm going to go back to my menu sheet and choose to delete all but the menu sheet then i'm going to head back to the visual basic editor and i'm going to get rid of a couple of these.
Extra columns i'm going to get rid of my custom date and short date columns just to tidy up a little bit and get rid of the extra blank line there as well and i'm going to get rid of the weekday name column as well just to give myself a little more space for the next little section now there may be other units of time that you're interested in calculating from your dates such as the quarter the week of the year.
Or the day of the year while we don't have specific functions for calculating those intervals there is a more generic function we can use to perform that task it's called date part if i just refer quickly to the documentation for the date part function you can see if i scroll down a little further to the settings section these are the different units of time that we can calculate so there's of course some duplication here but it also gives us access to things like the quarter the day of the year and the week of the year.
So just to quickly demonstrate how that works let's head back to the visual basic editor and i'm going to create a new column using the date part function so i'm going to add a new column from scratch this time i'll type in some double quotes on the next line followed by a comma and then the date part function before opening up some round brackets now i'm going to start by specifying which unit or interval of time i'm interested in so i'm going to say q in some single quotes followed by a comma and then it's going to be the film's release date so i'm just going to copy.
And paste that part to avoid having to type it out and then i can close the round brackets and say as release quarter if i could spell that correctly there we go i can then close a double quotes and concatenate a continuation character and as before rather than writing that out again let's copy and paste it a couple of times and then i'm going to change the units of time to ww for release week.
And then finally y a single y for day of the year so release day of year okay so having done that if i head back to my menu sheet and run that query again i get three new columns so i think we've dealt fairly comprehensively with picking a data part into its separate components but what if we needed to do the opposite of that what if for example you have your dates stored as a separate year month and day.
And you need to construct a complete date out of those separate units well we have functions which allow us to do that as well let's head back to the visual basic editor i'm just going to get rid of my date parts columns again just to tidy up a little bit to make my life a little easier for the next stage and then on this new line i'm going to open up some more double quotes type in a comma and then type in the function called date serial now the date serial function accepts a year.
As a number a month as a number and a day of the month as a number and then constructs that into a complete date now of course we don't have those separate units of time stored in our movies database or our movies workbook we've got only complete dates stored here but we have calculated the separate components a year and month and a day of the month so we're just going to put all of those back together again fairly pointlessly i admit but just to demonstrate the principle of the thing now one of the nice things about using gaydodb is that you can.
Reference previously created custom columns in subsequent columns in your select list so i'm just going to basically list out the release year the release month and finally the release day of month aliases i can then close the round brackets and then give this an alias let's call it uh date of release just to distinguish it from our previous columns.
And then we can concatenate a continuation character at the end and having done that we can head back to the menu sheet and run that query again and we basically construct our release date column again hopefully you can see that this is definitely treated as a date so you can see that unlike the format functions which left align the the date value and treated as text the date serial function does construct an actual date value so you can see it's right aligned in the cell if i select the cell it shows me the.
Short date format and it has the formatting applied according to the if function in the get query results subroutine or i should say if statement so it's definitely recognizing that new column as a date data type and applying this number format appropriately so you can use date serial when you have the year month and day stored as numbers but what if you have your date stored as text that you want to convert into a date well there's another function for that.
Called date value let's first of all just construct a string of text which looks like a date so i'm just going to concatenate three of our previous columns together to look like a date in a new column so let's add a new line to the select list and i'm going to start by using the release day of month field so i'm just going to copy and paste that and then i'm going to concatenate that with a literal space character in some single quotes and concatenate that with.
The month name or release month name now of course i could be copying and pasting the actual functions themselves which calculate these parts but it seems simpler here we end up with a shorter column if we just reference the existing aliases i'll concatenate another literal space character and then finally concatenate a release year as well and then assign an alias to that column let's call this one as.
Of release 2 because i've run out of inventive names for my columns at this point so date of release 2 i can then close the double quotes at the end and concatenate a continuation character and then if we head back to the menu sheet and we run that query we end up with a new column which is again this time clearly text you can see that it's left aligned in the cell and it's showing you the 11 journal 1993 text rather than the short date format.
So now what i would like to do is take that piece of text and convert it into a date and to do that i can wrap the date value function around this expression so i can say at the beginning of that line after the comma date value open up some round brackets and then close around brackets just before my as keyword so having done that i can head back to the menu sheet run the query again and this time we should see that the same value is now right aligned.