1 00:00:01,110 --> 00:00:06,650 In the next two lectures we're going to be taking a look at working with dates and time in Excel. 2 00:00:06,900 --> 00:00:10,380 In this lecture we're going to take a look at working with dates. 3 00:00:10,460 --> 00:00:13,650 First off how do we input dates correctly in Excel. 4 00:00:14,040 --> 00:00:18,120 How dates are input depends on your regional settings. 5 00:00:18,120 --> 00:00:24,090 If your regional settings are set to United States you are going to input dates starting with month 6 00:00:24,150 --> 00:00:25,680 day and then the year. 7 00:00:25,860 --> 00:00:30,900 If you're in Europe and let's say you're in Germany, your regional settings are set to German. 8 00:00:30,900 --> 00:00:37,230 You're going to input dates as day, month, and then year. To make sure that your date was actually input 9 00:00:37,230 --> 00:00:38,060 correctly. 10 00:00:38,070 --> 00:00:40,280 Take a look at the number formatting. 11 00:00:40,470 --> 00:00:47,140 If you see date as the format then you can be sure that Excel has correctly recognized your input. 12 00:00:48,090 --> 00:00:53,980 If I wanted to have today's date in this cell and I wanted to have it in dynamic way, I can use the 13 00:00:53,980 --> 00:00:55,160 "Today" formula. 14 00:00:55,170 --> 00:01:00,050 All I have to do open bracket, close bracket, and press enter. This is dynamic, 15 00:01:00,060 --> 00:01:04,769 So if I open this file tomorrow I'm going to see tomorrow's date in here. 16 00:01:04,769 --> 00:01:12,140 Now if I take a look at the number formatting here I can see that date was assigned to this. 17 00:01:12,150 --> 00:01:15,180 Excel has correctly recognized this as a date. 18 00:01:15,600 --> 00:01:22,650 If I right mouse click, go to format cells or use the shortcut key control+1, I can see the date shown 19 00:01:22,650 --> 00:01:26,810 here and there is a star, an Asterisk sign right beside it. 20 00:01:26,850 --> 00:01:32,760 Down here I can see date formats that begin with an asterisk respond to changes in regional date and 21 00:01:32,760 --> 00:01:35,910 time settings that are specific for the operating system. 22 00:01:35,910 --> 00:01:41,910 This means if I send this file to someone who has different regional settings it's automatically going 23 00:01:41,910 --> 00:01:42,600 to adjust this. 24 00:01:42,630 --> 00:01:46,800 So my original settings now are English United States. 25 00:01:46,800 --> 00:01:53,130 This means my dates begin with a month and then the day. If I send this to someone that has German regional 26 00:01:53,130 --> 00:01:59,150 settings, it's automatically going to show it to them as the day first and then the month. 27 00:01:59,190 --> 00:02:01,690 That's what this Asterisk does. 28 00:02:01,920 --> 00:02:08,840 If I didn't want this to respond to the operating system, I can pick a type from here. I'm just going 29 00:02:08,840 --> 00:02:09,960 to cancel this. 30 00:02:10,020 --> 00:02:14,330 Now what if we wanted today's date and time in a dynamic way. 31 00:02:14,430 --> 00:02:21,030 There is another function and that's called the "Now" function. If you didn't want these to be dynamic. 32 00:02:21,030 --> 00:02:28,010 So if you wanted to add a timestamp to your Excel file you can use this shortcut key for dates. 33 00:02:28,050 --> 00:02:32,810 Just go to an empty cell and then press control and semicolon and press enter. 34 00:02:32,820 --> 00:02:38,230 Notice that there is no formula behind this so this state is going to be fixed in this file. 35 00:02:38,280 --> 00:02:45,560 If I wanted the time its control+shift+Colon and press enter and if I wanted them both together 36 00:02:45,630 --> 00:02:54,820 control+semicolon space control+shift+colon and enter. Now let's take a look at how Excel remembers dates. 37 00:02:54,820 --> 00:03:00,490 In excel dates are stored as a serial number that starts from January 1, 1900. 38 00:03:00,490 --> 00:03:07,120 So basically the number associated with January 1, 1900 is a one. January 2, 1900 39 00:03:07,150 --> 00:03:07,880 is a two. 40 00:03:07,990 --> 00:03:13,330 February 25, 2019 is 43,521. 41 00:03:13,420 --> 00:03:18,010 The fact that Excel stores these as numbers helps us make calculations on dates. 42 00:03:18,010 --> 00:03:24,420 For example we can add 10 days to this date and get the correct date back. 43 00:03:24,430 --> 00:03:26,670 Let's quickly take a look at this in Excel. 44 00:03:27,250 --> 00:03:31,660 I'm just gonna remove these, add to today's date 45 00:03:31,690 --> 00:03:32,410 Two days. 46 00:03:32,410 --> 00:03:37,590 So plus two and press enter, automatically I get a date back. 47 00:03:37,600 --> 00:03:45,500 Now if I switch this to the general format that's the number that's associated with the fifth of May 2019 48 00:03:45,600 --> 00:03:45,610 . 49 00:03:45,640 --> 00:03:47,890 That's how many days we are away 50 00:03:48,010 --> 00:03:50,470 since January 1, 1900. 51 00:03:50,470 --> 00:03:55,170 Now in addition to this, Excel gives us a lot of practical date functions. 52 00:03:55,180 --> 00:04:00,910 So for example let's say we received this set of dates from our database and we wanted to strip out 53 00:04:00,970 --> 00:04:02,290 the year. 54 00:04:02,290 --> 00:04:03,760 These are official dates. 55 00:04:03,760 --> 00:04:09,970 So if you take a look at the formatting here they are seen as dates by Excel, which means we're allowed 56 00:04:09,970 --> 00:04:16,750 to use date functions on them. To strip out the year, we can use the YEAR function. Open bracket, put in 57 00:04:16,750 --> 00:04:24,250 our date, close bracket, press enter, send this down. This way we strip out the year from our date. For month, 58 00:04:24,820 --> 00:04:33,190 it's the MONTH function and send this down. It strips out the month from our date. For day, 59 00:04:33,460 --> 00:04:39,390 Take a guess, the DAY function. Put in your date, close bracket, 60 00:04:39,580 --> 00:04:41,250 Send this down. 61 00:04:41,250 --> 00:04:44,900 Now in addition to this, we also get the date function. 62 00:04:44,980 --> 00:04:51,820 So in case your database was giving you year separately, month separately, and day separately and you actually 63 00:04:51,820 --> 00:04:55,600 wanted to create a date out of these, you can use the date function. 64 00:04:55,600 --> 00:04:58,640 This function has three arguments. 65 00:04:58,660 --> 00:05:00,390 First one is the year. 66 00:05:00,610 --> 00:05:01,990 That's my year. 67 00:05:01,990 --> 00:05:03,660 Second one is the month 68 00:05:03,820 --> 00:05:13,190 and third one is the day. Close bracket, press enter and we just created dates. Now let's calculate the number 69 00:05:13,190 --> 00:05:17,400 of days from this day to today's date. 70 00:05:17,540 --> 00:05:20,270 I can use simple formulas here right. 71 00:05:20,270 --> 00:05:23,680 Since I have today's date here I'm going to reference this cell, 72 00:05:23,720 --> 00:05:29,930 fix it using the F4 key because I'm planning to pull this down and I don't want the reference for 73 00:05:29,930 --> 00:05:31,100 this to shift. 74 00:05:31,130 --> 00:05:39,890 Minus this day. I can leave the relative cell reference here and send this down. 91 days have gone by 75 00:05:40,370 --> 00:05:42,080 since this date. 76 00:05:42,080 --> 00:05:46,090 You can of course also use the today function directly in here. 77 00:05:46,090 --> 00:05:51,590 So if you don't have it in a separate cell, you can type in today, open bracket, close bracket. 78 00:05:51,740 --> 00:05:54,780 You need that because this is an actual Excel function. 79 00:05:54,800 --> 00:05:58,830 And if I send this down, it's thinking that I want dates right. 80 00:05:58,840 --> 00:06:06,320 So if you ever get dates but you actually want numbers just switch this to the general format and we 81 00:06:06,320 --> 00:06:08,030 get our numbers back. 82 00:06:08,030 --> 00:06:12,980 Now what if you wanted the date for the first day of the month? 83 00:06:13,130 --> 00:06:18,610 So you have these dates, some of them start on the first day of the month and some of them don't. 84 00:06:18,700 --> 00:06:21,970 And you want the month and the year to be the same 85 00:06:22,130 --> 00:06:26,650 but the date will always be the first of the month. Which of the functions we learnt now 86 00:06:26,660 --> 00:06:29,190 can you use here? 87 00:06:29,690 --> 00:06:36,350 We can use the date function. Because remember we get the ability to strip out the year from here. 88 00:06:36,350 --> 00:06:38,540 Can I just click on this? 89 00:06:38,600 --> 00:06:45,470 I can't because I need to strip out the year. I need the year function and then I can reference 90 00:06:45,470 --> 00:06:49,540 this cell. That gives me the year associated with that date. 91 00:06:49,640 --> 00:06:51,430 Next is the month. 92 00:06:51,440 --> 00:06:58,190 Here I need the month function associated with this date, close bracket, and for the day it's the first day 93 00:06:58,190 --> 00:06:59,000 of the month right? 94 00:06:59,030 --> 00:07:04,040 It's fixed so I can put a one here, close bracket, press enter. 95 00:07:04,040 --> 00:07:05,120 I can send this down. 96 00:07:05,660 --> 00:07:07,640 So these remain the same 97 00:07:07,640 --> 00:07:10,750 and these ones become the first of that month 98 00:07:10,880 --> 00:07:12,620 and the same year. 99 00:07:12,620 --> 00:07:13,830 Now here's something different. 100 00:07:13,850 --> 00:07:19,890 What if I wanted to date for the last day of the month associated with these dates? 101 00:07:19,940 --> 00:07:21,380 This can vary, right? 102 00:07:21,380 --> 00:07:27,740 I can't fix the day here but we have another useful Excel function that we can use here and it's called 103 00:07:27,740 --> 00:07:31,220 the "EOMonth" end of month function. 104 00:07:31,220 --> 00:07:33,710 All this requires is a start date. 105 00:07:33,800 --> 00:07:35,910 That's going to be our date right here 106 00:07:35,930 --> 00:07:40,040 and how many months relative to this month do we want to move. 107 00:07:40,040 --> 00:07:45,590 So if we want to get the end of the month of this date right here. 108 00:07:45,590 --> 00:07:49,790 Our months argument is going to be zero, close bracket and press enter. 109 00:07:49,790 --> 00:07:52,760 That's the last day of September 2006. 110 00:07:52,790 --> 00:07:53,930 If I send this down. 111 00:07:54,320 --> 00:07:59,110 That's the 31st of December, 28th of February and so on. 112 00:07:59,150 --> 00:08:04,730 Remember in this example we used the DATE function to get the first of the month but we don't necessarily 113 00:08:04,730 --> 00:08:05,570 need to do that. 114 00:08:05,570 --> 00:08:08,980 We can also use the "EOMonth" function. 115 00:08:09,110 --> 00:08:14,360 How could we adjust this to get the first of the month. 116 00:08:14,470 --> 00:08:18,930 Well what if I get the end of the month of the previous month. 117 00:08:18,950 --> 00:08:25,400 If I change this to minus one and press enter, notice I get August 31st. 118 00:08:25,400 --> 00:08:32,650 So it goes one month back and it gives me the last day of that month and I just add one day to this. 119 00:08:32,900 --> 00:08:35,610 I get the first day of the month. 120 00:08:35,780 --> 00:08:38,070 That's just an alternate way of doing this. 121 00:08:38,120 --> 00:08:39,549 Both ways are right. 122 00:08:39,559 --> 00:08:43,120 You just have to go with the one that you most feel comfortable with. 123 00:08:43,289 --> 00:08:48,980 Now based on this what if we wanted to calculate the date for the last day of the next month. 124 00:08:49,090 --> 00:08:53,820 So again because it's the last day of a month we're going to use the EOMonth function. 125 00:08:53,930 --> 00:08:55,870 Our start date is this one. 126 00:08:55,880 --> 00:08:59,280 Now what's our month's argument? It's one. 127 00:08:59,330 --> 00:08:59,570 Right. 128 00:08:59,570 --> 00:09:00,830 Because we don't want this month. 129 00:09:00,830 --> 00:09:04,400 That would be zero, minus one is the previous month. 130 00:09:04,400 --> 00:09:06,310 One is the next month. 131 00:09:06,480 --> 00:09:14,850 Press enter and send this down. So these are some useful basic date functions that are going to come 132 00:09:14,850 --> 00:09:19,380 in handy when you work with dates in Excel. 133 00:09:19,380 --> 00:09:21,570 In the next lecture let's take a look at working with time.