Locked lesson.
About this lesson
We learn how to use dates as serial numbers, TODAY(), EOMONTH, WORKDAYS, and NETWORKDAYS.
Quick reference
Date Functions
Understand how dates work in Excel.
Learn the basics of calculations using dates, TODAY(), EOMONTH(), WORKDAY() and NETWORKDAYS()
When to use
These functions are useful to perform calculations with dates, especially in templates or reports.
Instructions
- TODAY() is a dynamic function that would show you today’s date every time you open the spreadsheet
- Days are serial numbers in Excel, with day 1 – 1 January 1900
- EOMONTH() can take any date and give you the end of the current, previous, next, two months back/forward etc. Useful because not all months have the same amount of days
- WORKDAYS() can calculate the end date if given a start date and the number of workdays (Mondays-Fridays), useful to get an end of project date
- NETWORKDAYS() calculate the number of Mondays-Fridays in a range (does not count weekends) – in other words, how many workdays available between two dates
- 00:04 In this lesson, we'll go through some date functions,.
- 00:07 And in the next, we'll follow up with time functions.
- 00:11 In the first two columns, I want to demonstrate a few basic Excel functions.
- 00:16 On the right, there are two more functions that are less known but also quite handy.
- 00:21 First things first, I want to start by refreshing your memory that dates in Excel
- 00:26 are seen as serial numbers.
- 00:28 We've got the numbers 1 and 30 in these cells.
- 00:32 If we convert those numbers to dates, we'll see, that means the very
- 00:37 first day in Excel's history was the first of January, 1900.
- 00:42 So the 30th day is the 30th of January, 1900.
- 00:47 The reason I showed you the 30th was to confirm the regional settings of this
- 00:51 computer.
- 00:52 Since we see 1/30/1900, we know the regional settings for
- 00:57 this computer for a date field is month, day, year.
- 01:02 Now, one of the most used functions, I think, is the today function.
- 01:06 And what does it do?
- 01:07 It displays today's date.
- 01:09 Seems obvious, but what's not so
- 01:11 obvious is that the today function updates every time you open the file.
- 01:17 Today is September 10th.
- 01:19 If I open the spreadsheet tomorrow, it will show you September 11th.
- 01:24 A month after that, it'll say October 11th,
- 01:27 that's a dynamic function that updates every time.
- 01:31 Now, if Excel sees dates as serial numbers,
- 01:34 that means you can do calculations with dates.
- 01:37 So tomorrow is technically today plus 1.
- 01:43 And yesterday is today minus 1.
- 01:48 We also have the year, month, and day functions.
- 01:52 Those are really straightforward.
- 01:54 The year function returns the year from a date, 2020, in this case.
- 01:58 The month returns the month for a date.
- 02:01 It's a 9 for September, the ninth month.
- 02:03 And the day function gives you the day value from the date.
- 02:07 So that would be the 10th.
- 02:10 The next one is really nice for accountants.
- 02:13 It's end of the month.
- 02:15 If you need to know the date of the final day in a given month,
- 02:18 you just need to feed Excel a date, and it will tell you.
- 02:23 In this case, I want to know the end of the month for today's date.
- 02:28 I type in EO month and click on the date.
- 02:32 The second argument asks for a number of months,
- 02:35 type in a zero to get the end of the current month being referenced.
- 02:40 Hit Enter and you'll see the end of this month is September 30th.
- 02:44 If you need the end of last month, use the same function, EO month, click the date.
- 02:51 But for the month's value, type in minus 1.
- 02:55 August 31st was the end of the previous month.
- 02:59 And what's the end of next month?
- 03:02 Change that negative 1 to a positive 1.
- 03:06 One month from now, the end of the month will be October 31st.
- 03:10 So a date is a serial number.
- 03:14 You have a nice today function that will update every time you open
- 03:18 the spreadsheet.
- 03:19 Tomorrow is the day after today.
- 03:22 It's today plus 1.
- 03:24 Yesterday is today minus 1.
- 03:27 You have a year function, a month function, and a day function.
- 03:33 And you have an end of the month function that will return the date at the end of
- 03:37 any month you like.
- 03:38 It just needs a date to start with, and the number of months before or after.
- 03:44 Now, these two formulas are WORKDAYS and NETWORKDAYS.
- 03:49 Here we have a project start date and how many days I need to finish the project.
- 03:55 I obviously mean workdays.
- 03:57 If it was just plain days, I could have taken the date, added 55, and
- 04:01 gotten the end date for the project.
- 04:04 But the WORKDAY function allows us to figure out dates when we need to account
- 04:09 for workdays and not include weekends.
- 04:12 So let's type in the workday function.
- 04:14 In the first argument is our start date.
- 04:17 The next argument is how many work days I require.
- 04:22 There is an option to add holidays but it's optional.
- 04:25 And I have the estimated end of my project.
- 04:28 If I stick to the 55 days, that takes us to the 1st of December, 2020.
- 04:34 NETWORKDAYS is like the reverse of WORKDAYS.
- 04:38 If we have a project start date, and we know the project deadline date,
- 04:43 and we'd like to know how many work days we've got to finish our work.
- 04:48 NETWORKDAYS will help us figure that out.
- 04:52 So type in, NETWORKDAYS, add the start date, and I have an end date.
- 04:58 And let's again assume no holidays, hit Enter.
- 05:03 And you can see that a project that starts on the 15th and
- 05:05 has to be done by the 30th.
- 05:08 You've got 12 work days to complete it.
- 05:10 So that's it for days and dates for now.
- 05:13 If you understand dates and are comfortable using the date functions,
- 05:17 it's quite useful for accounting templates and other accounting calculations.
Lesson notes are only available for subscribers.