Buying for a Team? Gift This Course
Wishlisted Wishlist

Please confirm that you want to add Working with Dates in Excel - Date Functions and Date Format to your Wishlist.

Add to Wishlist

Working with Dates in Excel - Date Functions and Date Format

Create powerful date formulas with DATEDIF, EOMONTH, NETWORKDAYS, EDATE, ISOWEEKNUM and more. Apply conditional formats
0.0 (0 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
0 students enrolled
Last updated 10/2016
English
$10 $20 50% off
2 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1.5 hours on-demand video
  • 17 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I Learn?
Understand how dates are stored in Excel and avoid common errors
Perform various calculations on dates using date functions
Apply conditional formatting based on date values
Fill date series and copy dates
View Curriculum
Requirements
  • Functions in the course are available in Excel 2010, 2013 and 2016
Description

This course focuses on using dates in Excel.  Learn how to enter dates correctly on a worksheet including some really useful shortcuts. Understand how Excel stores dates and to overcome common problems with using dates.

Master how to perform calculations on dates - calculate the number of days, months and years between two dates, what day of the week your birthday will be in 20 years time. remaining working days in a month, when an invoice is due or what to charge a customer on a pro rate basis. Discover the power of date calculations that keep themselves up to date based on the current date. 

Date Functions featured in the course are:

TODAY() - calculations on the current date
DATEDIF() - calculate the difference between two dates in years. months and days
WORKDAY() - calculate when you will finish a task based on working days
NETWORKDAYS() - calculate the number of working days between two dates
EOMONTH() - calculate on month end or beginning of month dates
EDATE() - calculate on months
DATE() - calculate on years, months or dates
DATEVALUE() - convert text dates to number dates
DAY(), MONTH(), YEAR() - extract parts of a date to perform calculations or analysis
WEEKNUM, ISOWEEKNUM - calculate the week number for a specified date
WEEKDAY - calculate the week day for a specified date
YEARFRAC - calculate the portion of a year elapsed between two dates

The course also covers formatting dates including showing the day of the week as - Monday, Tuesday etc and creating custom date formats including your own text  Discover how to apply conditional formatting to dates to highlight dates this week, this month, this year, in the past or on the current date.

You will also learn how to create lists of dates easily and quickly including a list working days, week commencing dates or the same date each year/month.

Who is the target audience?
  • Anyone who uses Excel at home or at work
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 18 Lectures Collapse All 18 Lectures 01:26:49
+
Course Introduction
1 Lecture 02:42
+
Introduction to Excel Dates
6 Lectures 36:16

Learn how Excel stores dates as numbers and how to avoid common date entry errors.  Learn how to format dates properly.

Preview 11:25

Dates are numbers: every date has a serial number behind it.  This can cause a lot of confusion.  This lecture explains how the serial number for a date is calculated and explains the importance of understanding cell formatting

Understanding Dates as Numbers
05:42

This lecture looks at how the regional settings on your computer effect date entry. It also looks at how to create custom formats for dates for example, Tuesday, 11 October 2016.

Formatting Dates
07:26

Perform basic dates calculations between two dates.  Calculate when you need to start or when you will finish a task.  Use the the TODAY() function to return today's date.

Basic Date Calculations
03:34

Use conditional formatting to highlight particular dates - today's date, this week's dates, dates in the past etc.  Use conditional formatting to highlight overdue invoices.

Preview 05:16

Learn how to create lists of dates automatically in Excel, including consecutive days, workdays, same day each month or year and every x number of days.

Filling and Copying Dates
02:53
+
Perform Calculation Based on Working Days
2 Lectures 08:41

The WORKDAY() function will allow you to calculate a finish date for a task based on the number of working days it takes to complete a task.

Using the WORKDAY() Function
05:25

The NETWORKDAYS() function calculates the number of working days between specified dates.

Using the NETWORKDAYS() Function
03:16
+
DATE Functions
9 Lectures 39:10

Use the DATEVALUE() function to convert an imported list of text dates into their numeric equivalent (proper date form). Convert date and time text strings into dates.

Using the DATEVALUE() Function
01:13

Use these functions to extract the day, month or year portion of a specified date. Helpful if you need to calculate the number of years, months or days between two dates.  

Preview 04:27

Use the DATE() function to calculate x number of years, months or days forward or backward from a specified date. Convert text strings into dates for example 20170122 to 22/01/2017.

Using the DATE() Function
07:45

Use WEEKDAY() to return the day of the week as a number. Useful for identifying every say Monday or weekend day.  

Using the WEEKDAY() Function
02:19

YEARFRAC() calculates the fraction of year as a result of whole days between a start date and an end date.  Useful for working our pro-rata payments etc.

Using the YEARFRAC() Functrion
01:40

Return the week number for a specified date using the date system for your location.

Using the WEEKNUM() and ISOWEEKNUM() Functions
02:55

Calculate beginning with a start dates x number of months forward or backward.  Useful for calculating anniversary dates etc.

Using the EDATE() Function
05:05

The EOMONTH() function returns the last or first day before or after a specified month. Useful for working out membership expiry dates, trial periods, invoice terms, working days in a month etc.

Using the EOMONTH() - End of Month Function
07:46

DATEDIF() is a very useful but hidden function that allows you to calculate the number of days, months and years between two dates for example "12 years, 2 months and 5 days".

Using the DATEDIF() Function
06:00
About the Instructor
4.7 Average rating
11 Reviews
44 Students
2 Courses
Excel Trainer at Mousebytes

Director of UK based IT training company. IT trainer with 20 years IT training experience in a commercial and public sector context. I have run MS Office courses in literally hundreds of companies, providing tailored training made highly relevant to the workplace. These Udemy courses are the result of these many years' of experience.

Report Abuse