Excel Magic Trick 783: Date Functions & Formulas (17 Examples)

welcome to XML matrix 783 hey if you want to download this workbook EXO matrix 779 276 click on the link below the video oh wow we have an epic video here this is date functions and formulas I'm going to show you seventeen examples hopefully this will sort of be a one-stop shopping for date functions and formulas dates here's a column of dates and if I look up here it says date number format if I apply the general format you see that it has a number underneath a serial number if I put the one number one here and apply a date we see that the first day that Excel knows is 1 1 1900 if I type a 2 here and then apply a number formatting short date I see 1 2 1900 so I'm going to ctrl Z on that undo one more time right so 4 0 5 8 5 that's how many days since December 31st 1899 there are and that will represent the number 2 11 2011 now serial dates are underneath and we'll do date math later first couple example just going to see how to get information from a date like we could say use the day Fox and the day function requires a serial number if you didn't know that dates were still number you mean like what is that so I'm going to click there and it will give me a day oh yeah that's the 11th day in the month if I double click and send this down that's all the day function it gives you a number between 1 and 31 now if you wanted the actual name of the day you'd have to use the text function text function requires any value knowing that a date is a number actually helps us understand that comment and then you have to know custom number formatting well um date number formatting is easy and now you have to put number formatting in double quotes and I want day right so I put D that would give me 11 DD that will give me 11 D DD that would give me if it's Monday it will give me mo n and if I put four DS it will give me the fully spelled out name thank you so that's Friday if I had two in there it would give me just FRA control Z for undo I'm going to double click and send that down so that's the actual day now a month well sometimes you want to see the month one is January two is February etc so you can use the month function notice it says serial number copy that down sometimes people have this as an extra column and then they can do an easy some if by month if you wanted the month spelled out like day over here you'd use text I'd give it this value comma and then if day is D what do you think month is right mm mm and then you can copy it all the way down sometimes these two items are used in some product formulas where you add a particular month and you need to match a word criteria against a serial date how about year equals what do you think the name of the year function is yeah it's year and year we look at a serial number just give the year same thing as a month sometimes people add extra columns like this and then it's easy to do a sum if by year now let's get a little going here and now watch this when I scroll over to the side I can't see that so I'm going to actually highlight this and freeze it that way as I scroll to the side I'll always see this view and then over here freeze and freeze the column so now when I scroll over no problem I can see last day of the month now last day the month if you have 2007 and 10 there's a built-in function called end of month in earlier versions you have to add the data analysis toolpak in under the Tools menu data a Tools menu add-in if I can even remember back that far awesome function end of month you give it a start date which has to be a serial number and you just say how many months in the future or in the past one would give us the end of month next month so it would be after February's March I think right so March I give it a zero that's this month right and that's what we want if you give it a minus one it'll give you last month so last day of this month would be zero now in earlier versions or if you run into a computer that doesn't have in a month it's useful to know how to do this now what we learn here is not going to be applicable applicable much as we go forward in the future because we have in a month but the tricks we learn here will help us with some more advanced tricks I'm gonna use the date function equals date if I could type oh man I'm having a hard time here let's see date all date does is you give it a year a month in a day and it spits out the serial number so I'm going to just going to give you an example 2011 comma let's say the month is two comma and the day is 11 right that's kind of silly there but that's what it does you put in those parameters arguments or parameters and it spits out the serial number formatted as a date but that's not what we want here we want the last day of the month so we're going to use this function to get the year I'm going to use the Year function that will dump it 2011 in there then I'm going to say the month that'll dump a 2 in there now here's the trick to getting the last day of the month I'm going to add one right now it's two if I add one it sends it to next month comma and now the day the day is awesome because if I put one year sure it's 11 right month well it's not two it's three and if I put a 1 that's a cool way to get the first day next month but watch this this argument right here can take positive numbers zeros are negative so if I give it a 0 well let me ask you this if I put in a 1 it's the first of the month so if I go backwards one number what it's going to do this trick gets us to the first day next month we just add a zero here and it goes back 1 so that's a tricky way to get the last day of the month now I'm going to double click and send that down and notice it even knows leap year I didn't point that out about the end of the month all right also this argument here can have - numbers and sometimes advanced formulas will do tricky things to get to get a date and you have to have a - here but I'm going to put zero now the first day of next month well there's two ways we'll do the end of month and a month we have a start day comma and this is the first day next month so why what am I going to do oh I can get the last day of this month right that's the serial number so all I have to do is add one and then double click and send it down now first day next month well we actually just did this equals date and then I want this on what the year so I'm going to do the year of this comma close parentheses comma and then the month of this and the whole trick is I want to send it to next month so this is a - I add one and then comma the day is simply hard-coded one that'll give me first date next month and then double click and send it down alright now what about there's lots of other tricks when it's a vest date that just means sometimes you have a in Human Resources you have a column of higher dates and you want to see when they vest to get their pension and that criteria for this company is five years we can use the e date edate is awesome you give it a start date comma and a number of months in the future now end of month always calculates the end of the month but what's great about edate is it knows that this is 211 so if i give it a 1 it'll give me the 11th next month right or whatever you want - 1 that'll give me the 11th of the prior month but what do we want here notice the argument requires month and we have year so no problem we say that and lock it with the f4 key because I'm going to copy it down times 12 and that gives us our months control-enter and there's the vest date so a column of vest dates from our higher dates how about work day in the future so here it is I need the work day in the future 16 days in the future well to get and actually are the example for date math is over here maybe I should go do this first well no I want I'll just show you there's a function called work day and that's all it does now work day this assumes that Saturday and Sunday are the only weekends but this new function in 2010 is awesome watch it start date comma days remember the whole point of this is we want an actual date it's going to deliver a serial number here's the start date that's the number of workdays in the future now watch this comma and this new weekend argument absolutely awesome this solves the problem early in earlier versions before 2010 there were some nasty array formulas to do things like this or non array formulas too but this drop down and this weekend argument just makes it beautiful now I'm going to assume that we have Sunday only so I'm going to put an 11 that means the only day that it's going to skip over when calculating this future workday is Sunday comma and then holidays you can leave it out if you have holidays I'm going to click on a single cell you could highlight a bunch of cells with all the holidays for whatever but there it is holidays and close parentheses and there it is it calculates 16 days in the future given this as the start date boom and I'm going to double click and send it down I forgot to lock it now notice when I copy that down it's all highlighted the active cell is right there I'm going to hit the f2 and I simply forgot to lock one of the cells now I'm editing this in the active cell so I'm going to go up to here and hit the f4 key to lock the row reference and then this I'm going to lock the row reference to populate all of these highlighted cells with the new edited formulas I'm going to hold ctrl and tap enter so then you can go down here and see that that worked now last Monday this is going to be the trickiest phone we do totally awesome actually in video 786 coming up later we're going to do a chart and one of the most important tricks is knowing how to calculate last Monday so for example this is Friday right so I need the Monday before which would be equals if this is Friday like four or five or something there it is so I subtract four from this but I certainly don't want to go through and count as I go down and do it's hard code these no way so how do we do this well first we got to learn what the weekday function does weekday functions totally awesome here's Monday Tuesday Wednesday Thursday Friday Saturday Sunday I added a custom number format control one just like we saw earlier custom number format I'm going to leave all that junk out here it just shows DDD mmm D D Y so we we saw it just a moment ago in the text function but you can certainly do it in the format cells dialog box under the number tab custom and then type it out and that's what you see that serial number is still under though under there though all right what does week they do it's going to tell it's either one two three four five or in some cases sunday is one this is two three four five and in other cases this is 0 this is 1 this is 2 the weekday will ask us which one we want so weekday serial number here it is comma and oh this is awesome in 2010 another beautiful drop-down with all these amazing options so one if you put a 1 here it's 1 to 7 if you put a 2 its month I'm sorry sunday is 1 thru Saturday at 7 if you put a 2 Monday is one Sunday at 7 and this is the one we want for last money zero for Monday 6 for Sunday so I'm going to type a 3 and double click and send this down now think about this what are we going to do I need last Monday if I have Friday how many days do I need to subtract to get back to Monday for how many and in fact we could do a little trick here one two two three four I'm going to do the day function here equals day and I'm going to click on this first one and I'm going to populate all there's two things here I'm not going to type that in because you don't need it we'll put it in automatically and I've have the active cell with the formula and I want to populate all these so ctrl enter I did one too many now 16 17 18 that's what the day function delivers so on this particular day Monday is 60 but I want to have a column full of Monday's so here's what we do we do this - weekday I want that you weekday international oh wait a second I'm sorry I got confused there I just want the weekday and that because it'll just it doesn't need the International it just gives us a single number serial number I'm going to say this one right here comma and then that three now right now think about what does this do it's looking at Monday so it subtracts a zero but when I copy it down this one's going to subtract a1 a2 a3 just as we see up here so this is the whole trick that's custom number formatting there I'm going to get rid of that over here there's custom number format I'm going to get general what it did is the formula sucked the number format from there you can either go general here or control shift tilde control ctrl + Shift + in this little tilde or grave accent right that's the keyboard shortcut to go back to general oh and I got that green I don't want any of that and now look at this there's a bunch of 16s and that is the conceptual trick I'm going to have to do day - weekday to get a bunch of 16s to show Mondays and really last Monday important for some charting tricks again will see this letter so you ready we'll see it later ready we're going to do date oh I need the year so I'm going to use the year function , I need the month that's just our month right here and then I need day comma day - week day comma week day and then I'm going to click right there comma 3 now that comma 3 you know I remember when I first learned about week 10 I didn't know this cool trick I learned it from the mr. Excel message board but I was like zero who wants a zero like this is the perfect reason for it alright so I'm going to close parentheses I put a comma there alright so there we go and it will give us Monday's all the way down so this is Monday 7 this is the prior Monday right so any one of these date inputs is just telling me the Monday from before all right let's keep going over here a couple more date tricks all right so this one I should have done this one at the beginning this is just basic dates since these are serial numbers I need to figure out the number of days between two dates right this is using an invoicing a lot here's here's the due date here's today how many days late is this so equals and it's always the later date minus the earlier date or n minus begin sometimes when you do this formula specially in earlier versions that will suck the format so then you have to apply general but in later versions they fix that so I'm going to double click and there it is it tells me how many days between two dates all sorts of date calculations require that you calculate the number of days between two dates how long was a project how late is this invoice etc etc now let's do project date and this one will change it up a little bit I'll say later - earlier but what's the problem here this since these are numbers underneath it's subtracting it it's never going to include this first date but for projects you often want to include it so what do you do plus one that's the number of the days it took for this project now net working days we're going to look at another awesome new function because yes sure enough this is a project right and these are all the days but what if we didn't work on Sunday or Saturday or Sunday or Monday or whatever equals networking just like well so this is from earlier versions this is the new one earlier versions just assume Saturday and Sunday this one you have the option so I'm going to do a start date start date so I'm going to do that one comma and then end date comma oh look at this awesome drop-down there is our choice and I'm going to say Sunday only could be Friday Saturday whichever one whichever weekend you have and there you have it set Sunday comma and holidays and I'm going to kind of want that so I'm going to drag it and notice I'm dragging a bunch of cells even though they're blank that way if I have more holidays later I can do that now I need to lock these I'm going to hit f4 and then close parentheses and then double click and send it down that is just awesome so net working days between two dates all right that was 17 date tricks all right we'll see you next trick you

Channel: ExcelIsFun

Views: 1,110,676

Rating: 4.78089 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, Date, Functions, DAY, TEXT, MONTH, YEAR, Last, Day, in, Month, First, Next, Vest, Date:, EDATE, WEEKDAY, Formula, for, Days, Between, Two, Dates, Inclusive, Net, Working, with, NETWORKINGDAYS

Id: VvIvINcn79I

Length: 19min 5sec (1145 seconds)

Published: Sat May 21 2011