Date and Time Functions in Microsoft Excel - For Beginners

hi there Jamie Keet here today at Teacher's  Tech hope you're having a great day today   today i want to show you how to use date and  time functions inside microsoft excel so i'm   going to show you how these functions work  and then together with a formula to even get   them to do more so let's get started today with  date and time functions inside microsoft excel so i'm going to start off this  tutorial just simply with some   simple functions and dates and then i'll  show you some more advanced things too   now with this worksheet if you want to follow  along i'll put the link down below in the   description so you can click on it and download  it and enter the information how i show you   now let's start off with just something simple i'm  just going to click in today's date now i could   just i don't have to use a function if i want  today's date i could just simply type today's date   hit enter and it gives me back a date and if  you're new with using dates you can format these   so if i right click and format cells you can  pick a different format you can go through the   uh the list here so if i was going to just  pick this hit okay and you can see how it's   formatted here all right let's uh but i don't  want this so i want to be able to have a dynamic   date so that if i open the worksheet tomorrow  it's going to change and for this i need to enter   a function and the function that i'm going to use  so i just deleted that cell is the today function   and if i just click in here there's a couple  different ways you can get to the functions uh   on this so if i click this little f up in  here insert function i could go up here

you could type today and hit go and it would  find it in the list i could double click on   today and it just enters it in just like this even  though it looks exactly the same as the last one   i showed you i it's different because if i open  this worksheet tomorrow it's dynamic it will say   october 12th so it's automatically going to update  and remember i'll just delete this real quickly   you don't have to use that function i key that i  just showed you could just type this in so if   i was using today i could just keep typing or use  it at this point i just have to make sure that i   end i have to end that bracket there and put  it in all right so let what can we do with this   let's just try some simple formulas now let's say  hypothetically my birthday was december 17th and   i want to know how many days until my birthday  you can just create a formula using subtraction

subtraction if i go ahead and put equals here and  so we're starting a formula i click on this cell   right here and i go ahead and minus so i went to  click the further one out first and then minus   this cell and then you can see it's 67. when i  open up this spreadsheet tomorrow this would be   october 12 and this would automatically adjust to  66 because this first one is dynamic and it would   change for it now let's see something else we  can do with this uh if i wanted to add something   i'll go to this cell reference here's the cell  and i want to have a bill due after a person   purchase it and then we'll say 20 days out  so what i'm going to do is i'll start another   formula i'm going to say equals and we'll just  go to this top one again i'm going to reference   this cell and i'm going to go ahead and add  so just add my   this cell right here so this grace period one even  though i have nothing in there yet i can quickly   adjust it and change grace period if i want so  i'll hit enter it's the same date even though the   format's a little different i'm going to click in  here if i say let's say if it was in 20 days like   so you can see it adjusted 20 days now it's due  on halloween here if i was going to say 30 days   we could go ahead you know we'll adjust it very  quickly with the cell references i could have   instead of putting d8 so if you look up here  in the formula i could have put plus 30 and   it would give me the same date but i like using  that cell reference because it's quick to change   on any of these if you want to put in uh maybe the  time you could do it this in a different different

formats too so if i was going to put equals you  can use the not the new one you can use the now   and if i select this one and just end the bracket  hit return now notice it's giving me the just the   date this is because the way that this one's  formatted so if i go to format cells if you take   a look at the time one i could adjust this and  pick uh pick a time here and it will just give   me back a time to it so it depends on how you want  your cell formatted you could go through and just   pick these so you could go look at the date and  time uh if you want let's say you go to this one   so it's going to give me the date and the time  altogether you can adjust that i do want to point   out there's some shortcuts to the static ones that  make it kind of handy if you just want to enter   a quick date in here so i'll just click and sell  this cell so if i hit control and then hold that   down and add my semicolon it will quickly add  the date and then if you do the control shift in   semicolon although i guess that kind of makes it  the colon if you hold down the shift but then you   can see how it enters some time so those are just  some quick shortcuts so those are just some simple   things with time and date i want to show you a  little bit more advanced ones that you can do   too now i just want to go through a number of  different ways you can still add and do different   things and find the differences between using  times i'm just going to click in this cell right   here and i'm going to put use my now function  that i showed you before so i'm going to type in   now on this one and i'll just not new i'll go now  and complete it so there's an empty worksheet that   you can just go to the bottom and practice this  as we go along what i want to do first is let's   say if we add we want to add a half a day to  this or 12 hours we can just simply do a formula   indicating this based on this time so if i use my  equal sign to start my formula and i go ahead and   click on this cell and go to my edition i can add  a half a day just by putting 0. 5 for half a day   so if i go and put my 0. 5 in hit enter and you'll  be able to see that this is 12 hours ahead on this   one so but let's say if we wanted to be more  specific than this we can actually add another   more like let's say five hours and 29 minutes to  this but then we'll have to use another function

with it so if i go to this cell right here and i'm  going to just start my formula and i'll go back to   here clicking on this cell and now what i'm going  to do is add i'm going to start by typing time   and you can see i get this one  right here i'm going to choose this   now all i have to do is follow along so  how many hours did i say well i said five   right and then you put the comma follow it along  how many minutes 29. now i could even get to the   second but i didn't say any seconds so i'm just  going to put 0 and i'm going to end this and hit   enter and you can see now it jumped ahead 5 hours  and 29 minutes so those are just some tips of how   you can be adding specific amounts of times or  hours to a date and i just want to note anytime   there's any changes uh to the worksheet  that this will change automatically here   it's dynamic right so it's going to make those  changes and everything will adjust from there another cool function with date and time inside  excel is the workday so i can actually count   i want to know how when is 30 work days from a  certain time so i typed in november 1st right here   and i want to know when is 30 work days and the i  can calculate this a couple different ways and i   could have it with or without holidays depending  on how the workplace works first of all i'm gonna   do this uh without any holidays  in it if you look up top and i didn't point   out this yet but if you look under the tabs and  you look under formulas there's all these date

and timelines right here so i could click down  and take a look at all the different ones here   but if i go to workday right here and just click  on this i get this dialog box of the arguments   that i want to put in well i just put november 1st  here and i'm going to click on it so that's the   start date b6 how many days am i looking at let's  say 30 days 30 work days when will the date be   i won't put the holidays yet i'll just put  okay so then you can see that 30 work days   from november first would be december 13th now  let's say if i wanted to do with the holidays if i   again if i wanted i could do just type equals  workplace or go back up to date and time   and sorry workday and go up here so my start date  is here 30 days again is here this time i'm going   to add holiday so i wrote them over here so these  are the two holidays that in november i'm going to   highlight them like this you can see it gave that  range i hit ok and now it's december 15th so they   took these as holidays and now it changed the date  so that's just pointing out a work day counting   workdays for you and also making sure that you see  under formulas you have all these different ones   that you can use so there's lots of other date and  time functions here from the library if you look

under date and time and i recommend you playing  around with those but the last function i want   to show you today is one you're not going to find  on this list and that is date diff and date diff   can calculate the difference whether it be days  months or years between two different dates and   i want to show you how that works so i'm going to  start with the formula in this cell here date diff   and i'll type you got to make sure you  start with the equal sign so i'm going to   type date diff like this and i'm going to go ahead  and start it if i click on this will give me   some help on the right hand side to kind of show  you the argument description and the units to use   but how it works is if i click on this first  date where it says december 2nd 2020 and then   i just put a comma and i click on the second date  and put another comma what i can do is then just   in quotation marks put d for day that's the unit  and make sure you have it in quotation marks and   then enter and you get the days now you can do the  same thing for months so go ahead and start it and   try to put date diff like this and  start it up and click on the first one   comma second one like this and then making sure  that you have the m for month and end it and you   get 10 months and you can do the same thing give  it a try with year just putting the y in there   and you'll see the different uh the different  what it will come up as a zero on it because   it's not a full year so i hope you like these  different tips for using date and time function in   microsoft excel today let me know what else you're  looking for down in the comments down below thanks   for watching this week on teachers tech i'll see  you next time with more tech tips and tutorials