0
Mynda Treacy
MICROSOFT EXCEL MVP AND CO-FOUNDER MY ONLINE TRAINING HUB
Excel Date & Time
EVERYTHING YOU NEED TO KNOW ABOUT WORKING
WITH DATES AND TIME IN EXCEL
1
CONTENTS
Introduction .................................................................................................................................................................................................. 4
Regional Settings ................................................................................................................................................................................... 4
Excel Date & Time 101 .............................................................................................................................................................................. 5
In a nutshell .............................................................................................................................................................................................. 5
Dates ........................................................................................................................................................................................................... 5
Time ............................................................................................................................................................................................................. 6
Date & Time Together ......................................................................................................................................................................... 6
Good to know .......................................................................................................................................................................................... 7
Entering Dates & Times in Excel ........................................................................................................................................................... 8
Entering Dates ......................................................................................................................................................................................... 8
Entering Dates with Two Digit Years .............................................................................................................................................. 9
Entering Time ........................................................................................................................................................................................... 9
Entering Dates & Time Together .................................................................................................................................................. 10
Simple Date & Time Math .................................................................................................................................................................... 11
Adding/Subtracting Days from Dates ......................................................................................................................................... 11
Subtracting Dates from one another .......................................................................................................................................... 11
Adding Times to one another ........................................................................................................................................................ 12
Subtracting Time from Times ......................................................................................................................................................... 12
Subtracting Times from one another .......................................................................................................................................... 12
Date & Time Shortcuts .......................................................................................................................................................................... 13
Formatting Dates & Time ..................................................................................................................................................................... 14
Custom Formats .................................................................................................................................................................................. 15
Custom Date Formatting Characters ........................................................................................................................................... 17
Custom Date Formatting Examples ............................................................................................................................................. 17
Custom Time Formatting Characters .......................................................................................................................................... 18
Custom Time Formatting Examples ............................................................................................................................................. 19
Identifying if Dates are Entered as Text .......................................................................................................................................... 20
Fixing Text Dates ...................................................................................................................................................................................... 22
Converting Date Serial Numbers to Text ....................................................................................................................................... 26
Extract Month Number from Text Month Name......................................................................................................................... 27
Extract Date/Time Elements ................................................................................................................................................................ 29
Common Date Calculations ................................................................................................................................................................. 29
Calculate Age or Years of Service ................................................................................................................................................. 29
2
Difference between Dates ............................................................................................................................................................... 31
Finding Dates ........................................................................................................................................................................................ 32
Difference between Times (same day) ........................................................................................................................................ 32
Difference between Times (different days) ............................................................................................................................... 33
Rounding Time and Time Intervals (using CEILING & FLOOR) ......................................................................................... 33
Rounding Time and Time Intervals (using MROUND).......................................................................................................... 34
Convert Time to Decimals ............................................................................................................................................................... 34
Convert Minutes to Hours, Hours to Days etc. in Decimals ............................................................................................... 34
Excel Date and Time Functions .......................................................................................................................................................... 35
DATE Function ...................................................................................................................................................................................... 38
TIME Function ....................................................................................................................................................................................... 40
DATEVALUE Function ........................................................................................................................................................................ 41
TIMEVALUE Function ......................................................................................................................................................................... 42
NOW Function ...................................................................................................................................................................................... 43
TODAY Function .................................................................................................................................................................................. 44
HOUR Function .................................................................................................................................................................................... 45
MINUTE Function ................................................................................................................................................................................ 46
SECOND Function ............................................................................................................................................................................... 47
DAY Function ........................................................................................................................................................................................ 48
MONTH Function ................................................................................................................................................................................ 49
YEAR Function ...................................................................................................................................................................................... 50
WEEKNUM Function .......................................................................................................................................................................... 51
ISOWEEKNUM Function ................................................................................................................................................................... 53
WEEKDAY Function ............................................................................................................................................................................ 54
EDATE Function .................................................................................................................................................................................... 55
EOMONTH Function .......................................................................................................................................................................... 56
WORKDAY Function ........................................................................................................................................................................... 57
WORKDAY.INTL Function ................................................................................................................................................................. 58
DAYS Function ...................................................................................................................................................................................... 60
DAYS360 Function .............................................................................................................................................................................. 61
NETWORKDAYS Function ................................................................................................................................................................ 62
NETWORKDAYS.INTL Function ...................................................................................................................................................... 63
YEARFRAC Function ........................................................................................................................................................................... 65
DATEDIF Function (use with caution) .......................................................................................................................................... 66
3
DATEDIF Alternatives ......................................................................................................................................................................... 68
More resources ......................................................................................................................................................................................... 69
About ............................................................................................................................................................................................................ 71
Thanks .......................................................................................................................................................................................................... 71
4
INTRODUCTION
The objective of this book is to teach you how Excel handles date and time and provide you with all the tools
you will need.
It’s designed to be read in conjunction with the accompanying Excel file, which you can download here.
REGIONAL SETTINGS
When reading this eBook keep in mind that my regional settings format dates as dd/mm/yyyy and so the
screenshots throughout this book are in this format. However, if you open the accompanying Excel file you
may see some dates have switched to match your regional settings, which may be different to mine e.g.
mm/dd/yyyy.
Dates and times with a format that begins with an asterisk (*) automatically update based on your PC’s
regional settings. You can see an example in the Format Cells dialog box below:
5
EXCEL DATE & TIME 101
IN A NUTSHELL
Excel stores dates and time as a number known as the date serial number, or date-time serial number.
When you look at a date in Excel it's actually a regular number that has been formatted to look like a date. If
you change the cell format to 'General' you'll see the underlying date serial number.
The integer portion of the date serial number represents the day, and the decimal portion is the time. Dates
start from 1st January 1900 i.e. 1/1/1900 has a date serial number of 1.
Caution! Excel dates after 28th February 1900 are actually one day out. Excel behaves as though
the date 29th February 1900 existed, which it didn't.
Microsoft intentionally included this bug in Excel so that it would remain compatible with the spreadsheet
program that had the majority market share at the time; Lotus 1-2-3.
Lotus 1-2-3 was incorrectly programmed as though 1900 was a leap year. This isn't a problem as long as all
your dates are later than 1st March 1900.
DATES
Excel gives each date a numeric value starting at 1
st
January 1900. 1
st
January 1900 has a numeric value of 1,
the 2
nd
January 1900 has a numeric value of 2 and so on. These are called ‘date serial numbers’, and they
enable us to do math calculations and use dates in formulas.
The Date Serial Number column displays the
Date column values in their date serial
number equivalent.
e.g. 1/1/2017 has a date serial number of
42736. i.e. 1
st
January 2017 is 42,736 days
since 31st December 1899.
Tip: format the date serial number column as a Date and you'll see they look the same as the Date column
values.
6
TIME
Times also use a serial number format and are represented as decimal fractions.
Hours: since 24 hours = 1 day, we can infer that 24 hours has a time serial number of 1, which can be
formatted as time to display 24:00 or 12:00 AM or 0:00. Whereas 12 hours or the time 12:00 has a value of
0.50 because it is half of 24 hours or half of a day, and 1 hour is 0.041666' because it's 1/24 of a day.
Minutes: since 1 hour is 1/24 of a day, and 1 minute is 1/60 of an hour, we can also say that 1 minute is
1/1440 of a day, or its time serial number is 0.00069444'
Seconds: since a second is 1/60 of a minute, which is 1/60 of an hour, which is 1/24 of a day, we can also say
one second is 1/86400 of a day or in time serial number form it's 0.0000115740740740741...
DATE & TIME TOGETHER
Now that we know how dates and times are stored we can put them together - ddddd.tttttt
For example, the date and time of 1
st
January 2012 10:00:00 AM has a date-time serial value of
40909.4166666667
40909 being the serial value representing the date 1
st
January 2012, and .4166666667 being the decimal
value for the time 10:00 AM and 00 seconds.
More examples below.
7
GOOD TO KNOW
- Dates prior to 1st January 1900 are not recognised in Excel.
- A negative date will display in the cell as #######
- Times stored without a date effectively inherit the date 0 Jan 1900 i.e. the month is Jan and the year 1900
and the day is zero. Remember, there are no dates prior to 1/1/1900 from Excel's perspective. This means
that times stored without a date e.g. 0.50 for 12:00 PM is the equivalent of 0 Jan 1900 12:00 PM.
This is important because if you try to take 14 hours from 12 hours (without a date) you'll get the dreaded
###### display in the cell, because negative dates and times cannot be displayed. We'll cover workarounds
for this later, but for now keep in mind that math on dates and time that result in negative date-time serial
numbers cannot be formatted as a date.
- Excel actually has two date modes. The other mode is called 1904 Date System and is used for compatibility
with Excel 2008 for Mac and earlier Mac versions. You can change the date system in the Advanced Options.
In the 1904 date system dates are calculated using 1st January 1904 as the starting point. The difference
between the two date systems is 1,462 days. This means that the serial number of a date in the 1900 date
system is always 1,462 days greater than the serial number of the same date in the 1904 date system. 1,462
days is equal to four years and one day (including one leap day).
Caution; the date setting you choose applies to all dates within the workbook. You can't mix and
match modes and you shouldn't reference workbooks that use a different date system in formulas.
Bottom line; don't use the 1904 date system unless absolutely necessary! Click here for more on date systems
in Excel.
- Excel applies date number formats based on your system region settings. For example, my system is set to
display dates in dd/mm/yyyy format, but if you're in the U.S. your system is likely to format them as
mm/dd/yyyy. Excel will automatically convert the format of date serial numbers to suit your system settings
as long as it's one of the default date formats and not a custom number format.
8
ENTERING DATES & TIMES IN EXCEL
ENTERING DATES
You can type in various configurations of a date and Excel will automatically recognise it as a date and upon
pressing ENTER it will convert it to a date serial number and apply a date format on the cell.
For example, try typing (or even copy and paste) the following dates into an empty cell:
1-1-2009
1-1-09
1/1/2009
1/1/09
1-Jan-09
1-Jan 09
1-Jan-2009
1 Jan 09
1/1
You can see that entering numbers that look like dates which are separated by a forward slash or hyphen will
be recognised as a date. Even typing in a date with the month name gets converted to a date.
However, dates separated with a period like this 1.1.2009, or spaces with between numbers like this 01 01
2009, will end up as text, not a date. Gotta have some limits!
Tip: Dates that display ##### in a cell usually indicate that the column is
simply not wide enough to display it.
However, if you make the cell really wide and it still displays ##### then this
indicates that the date is a negative value and Excel can't display negative
dates
9
ENTERING DATES WITH TWO DIGIT YEARS
When you enter a date with two digits for the year e.g. 1/1/09, Excel has to decide if you mean 2009 or 1909.
It goes by the rule that dates with years 29 or before, are treated as 20xx and dates with the year 30 or older
are treated as 19xx. See examples below.
Tip: You can enter the day and month portions of a date and Excel will insert the year based on your
computer's clock. Nice to know for data entry.
ENTERING TIME
When you enter time you must follow a strict format of at least h:mm. i.e. the hour and minutes are
separated by a colon with no spaces either side. Entering the h:mm components will result in a time
formatted in military time e.g. 2:00 PM is 14:00 in military time.
If you enter a time that includes a seconds component e.g. 3:15:40, Excel will automatically format the cell in
h:mm:ss.
If you want the time to be formatted with AM/PM you can simply enter a space after the time and then type
AM or PM, or apply the number format to the cell later. Here are some examples:
10
ENTERING DATES & TIME TOGETHER
Now that we know how to enter dates and time separately we can put them together to enter a date and
time in the same cell.
You can even enter time then date and Excel will fix the order for you.
You'll find that even if you enter AM/PM, that Excel will convert it to military time by default. You can
override this with a custom number format. More on that later.
11
SIMPLE DATE & TIME MATH
Now that we understand that Excel stores dates and time as serial numbers, you'll see how logical it is to
perform math operations on these values. We'll look at some simple examples here and tackle the more
complex scenarios later when we look at Date and Time Functions.
ADDING/SUBTRACTING DAYS FROM DATES
Tip: you can also add/subtract the days directly in the formula e.g. =B10+10 or =B11-5 Although, it's better
to place the values you're adjusting by in their own cell or a named range.
SUBTRACTING DATES FROM ONE ANOTHER
Tip: format the cell to General or Number to see the number of days between two dates.
Note: the ‘result’ is exclusive of the start day i.e. it assumes the start day is at the end of that day.
12
ADDING TIMES TO ONE ANOTHER
The time being added is input as a time serial number. Notice there are no negative times in the table below.
Remember we can't display negative times. Instead we need to use the math operator to tell Excel to subtract
time. See examples below.
Note: Times that roll over to the next day result in a time-date serial number >= 1. Cell E28 actually contains
a time-serial number of 1.08333', but since the cell is formatted to display time formatted as h:mm:ss, only
the time portion is visible.
If you want to show the cumulative time (like cell E29) then you need to surround the 'h' part of the time
format in square brackets like so: [h]:mm:ss
SUBTRACTING TIME FROM TIMES
Notice the last result in the table below shows ######, this is because it results in a negative time and Excel
can't display that, but notice it can return a negative time serial number. More on how to solve this later.
SUBTRACTING TIMES FROM ONE ANOTHER
Again, here the last result shows ###### because it results in a negative time.
15
CUSTOM FORMATS
We can also create our own custom date and time formats to suit our needs. Let's take a look.
1. Select the cell(s) containing the dates you want to format.
2. Press CTRL+1, or right-click > Format Cells to open the Format Cells dialog box.
3. On the Number tab select 'Date' in the Categories list. This brings up a list of default date formats
you can select from in the 'Type' list. Likewise for the Time category.
17
CUSTOM DATE FORMATTING CHARACTERS
Excel recognises the following characters and sets of characters for date formatting.
Character
Explanation
Date
Formatted
d
Displays the day as a number without a leading
zero.
3/09/2016
3
dd
Displays the day as a number with a leading zero
when appropriate.
3/09/2016
03
ddd
Displays the day as an abbreviation (Sun to Sat).
3/09/2016
Sat
dddd
Displays the day as a full name (Sunday to
Saturday).
3/09/2016
Saturday
m
Displays the month as a number without a leading
zero.
3/09/2016
9
mm
Displays the month as a number with a leading zero
when appropriate.
3/09/2016
09
mmm
Displays the month as an abbreviation (Jan to Dec).
3/09/2016
Sep
mmmm
Displays the month as a full name (January to
December).
3/09/2016
September
mmmmm
Displays the month as a single letter (J to D).
3/09/2016
S
yy
Displays the year as a two-digit number.
3/09/2016
16
yyyy
Displays the year as a four-digit number.
3/09/2016
2016
CUSTOM DATE FORMATTING EXAMPLES
We can bring the characters together to create our own custom formats. Some examples below:
Remember; the custom format doesn't alter the underlying date serial number, it is still the same.
18
CUSTOM TIME FORMATTING CHARACTERS
Like dates, time also has its own set of custom formatting characters, as listed below:
Character
Explanation
h
Displays the hour as a number without a leading zero.
[h]
Displays elapsed time in hours. If you are working with a formula that returns a
time in which the number of hours exceeds 24, use a number format that
resembles [h]:mm:ss or [h]:mm
hh
Displays the hour as a number with a leading zero when appropriate. If the
format contains AM or PM, the hour is based on the 12-hour clock. Otherwise,
the hour is based on the 24-hour clock.
m
Displays the minute as a number without a leading zero.*
[m]
Displays elapsed time in minutes. If you are working with a formula that returns
a time in which the number of minutes exceeds 60, use a number format that
resembles [mm]:ss.
mm
Displays the minute as a number with a leading zero when appropriate.*
s
Displays the second as a number without a leading zero.
[s]
Displays elapsed time in seconds. If you are working with a formula that returns
a time in which the number of seconds exceeds 60, use a number format that
resembles [ss].
ss
Displays the second as a number with a leading zero when appropriate. If you
want to display fractions of a second, use a number format that resembles
h:mm:ss.00.
AM/PM,
am/pm, A/P,
a/p
Displays the hour using a 12-hour clock. Excel displays AM, am, A, or a for times
from midnight until noon and PM, pm, P, or p for times from noon until
midnight.
*Note: The m or mm code must appear immediately after the h or hh code or immediately before the ss
code; otherwise, Excel displays the month instead of minutes.
19
CUSTOM TIME FORMATTING EXAMPLES
Note: if your PC region settings have the Date & Time formats set to show the Short Time as hh:mm tt or the
Long Time as hh:mm:ss tt then this may override any single 'h' formats and display them as 'hh'.
The screenshot above is what I see with my PC region settings for the Short Time as h:mm tt. If you see
something different when using a single ‘h’ format, then it will be down to your PC region settings.
20
IDENTIFYING IF DATES ARE ENTERED AS TEXT
Dates incorrectly entered or imported as text are a showstopper. We need our dates entered as date serial
numbers to be able to leverage the Date Functions, use them in math and lookups, or even use them in
PivotTables.
It can be tricky to tell if a date is text, because unlike regular values, a date will often appear the same in the
formula bar as it does on the face of the cell. Don't worry, I've got several never fail, dead easy ways to tell if
dates are 'dates' or 'text'.
Quick Test 1:
Only one set will display a sum in the status bar (and average, min and max if you have them set to display):
That's right, the dates that are correctly entered as date serial numbers will display the SUM result in the
status bar when you select more than one cell containing a date. Whereas text will only display the count.
So, a quick test to check if dates are text is to select more than one cell containing your dates, then glance
down to the status bar to see if you get a sum result. The status bar is in the bottom right of the Excel
window:
21
Quick Test 2:
Another quick test is to change the cell format to
General.
If your date format displays the date serial number (as
shown in the second list of dates to the right) you're
good to go, but if it still displays a date (as shown in the
first list of dates on the right) then you've got yourself
some text dates that need fixing.
It's not as simple as just changing the cell format to
'Date'. Those text dates are stubborn. I show you how to
fix them in the next chapter.
Quick Test 3:
Widen the column; any dates that are text will align to the
left, assuming no other alignment formatting has been
applied to the cells.
22
FIXING TEXT DATES
Option 1: Use Built in Error Checking to Fix Dates
More recent versions of Excel are good at detecting dates incorrectly entered as text and tagging them with
a warning notification. Hovering your mouse over the warning tells you the date is entered as text:
Clicking on the warning reveals a list of options for fixing the date, or
ignoring the error, as you can see in the image on the right:
Tip: if you have a lot of dates to fix, or a lot of formulas referencing
these dates then converting them to date serial numbers with this
method could be slow. Option 2 may be quicker.
In cases where the warning doesn't display, as well as in earlier
versions of Excel, we need to resort to other methods of fixing dates
formatted as text. Let's take a look.
23
Option 2: Paste Special
1. In an empty cell enter 1 and copy
the cell to the clipboard
2. Select the cells containing the text
dates you want to convert to date
serial numbers.
3. Home tab > Paste > 4. Paste
Special
Tip: Paste Special > Values shortcut
keys:
ALT > E > S > V > Enter or
CTRL+ALT+V > V > Enter
In the Paste Special dialog box select:
- Values
- Multiply (or Divide)
Alternate approach: You can also use
Paste Special with 'Add'.
Simply copy an empty cell > Select your
date cells > Paste Special :
Values & Add!
No need to go back and delete the '1'.
These methods will return a list of date
serial numbers which you can then apply a
date number format to.
Tip: on long lists with lots of formulas referencing your dates this method is often quicker than the error
checking method in option 1.
24
Option 3: Text to Columns
If your text dates aren't formatted based on the standard structure for your region e.g. dd/mm/yyyy or
mm/dd/yyyy, then a great tool to use is Text to Columns, because this allows you to specify the order of the
date characters ensuring that they are converted to date serial numbers correctly.
1. Select the cells
containing your dates
2. Data tab
3. Text to Columns
4. Delimited
5. Next
In step 2 of the wizard
simply click Next.
25
In step 3 of the wizard you
can select the order of the
date data from the drop
down.
Note: this is the format of
the text you're converting,
not your final desired
format.
Your final desired format
can be applied with
custom number
formatting.
Option 4: DATEVALUE Formula
The DATEVALUE function takes a date text string and converts it to a date serial number, as you can see in
this example:
Tip: You'll probably want to convert those DATEVALUE formulas in column C to values with Paste Special >
Values and get rid of the text dates in column B. No need to have them cluttering up your spreadsheet.
26
CONVERTING DATE SERIAL NUMBERS TO TEXT
If you want to use a date in a chart label, or you want to concatenate a date with some other text, then you'll
want to convert the date serial number to text first to ensure it displays correctly.
We can use the TEXT function for this. The second argument of the text function allows us to specify the date
format we want applied.
Syntax:
=TEXT(value, format_text)
value
This is typically a reference to a cell containing the date serial number, but could also
be a DATE formula.
format_text
This is the date number format you want to apply (surrounded by double quotes).
Tip: Use formatting principles described in the Formatting section.
Tip: You can use the date number formats to construct the date's appearance in many different ways. You
can also concatenate additional text using the & symbol, as shown in C19 and C20.
Tip: The last example on row 22 shown above will always return a date formatted as text in English,
irrespective of the PC’s regional language settings. Change the values in CHOOSE to return your preferred
language.
Note: the TEXT function converts the dates to text, obviously! But bear in mind that this means you can't use
the values in column C in any math formulas, or many of the Date functions.
27
EXTRACT MONTH NUMBER FROM TEXT MONTH NAME
Often, we'll have a list of month names that we want to convert to a date, or even just extract the month
number.
How it works: By concatenating a 1 to the text month name in column B we provide the MONTH function
with a text date it can recognise/use. We can see how the formula in cell C7 evaluates in the Evaluate
Formula dialog box below:
28
Note: The formula in column C converts the month to the first day of each month in the current year.
Tip: it also works with abbreviated dates!
You can replace the 'YEAR(TODAY())' part of the formula with a different year, if you prefer.
*Special thanks to Sunny Kow for reminding me about this tip.
29
EXTRACT DATE/TIME ELEMENTS
We often need to extract the date or time portion from a date-time serial number for use in other functions.
We can use the INT function to extract the date portion, and the MOD function to extract the time portion.
See examples below.
*Special thanks to Sunny Kow for reminding me about this tip.
COMMON DATE CALCULATIONS
CALCULATE AGE OR YEARS OF SERVICE
Warning: The DATEDIF function can return errors.
33
DIFFERENCE BETWEEN TIMES (DIFFERENT DAYS)
When time starts on the day before the end time, as is common in shift work, the start and end time either need to be entered with the date. As shown on row
66, or you require a different formula to account for the missing date information. As shown in cell E68.
ROUNDING TIME AND TIME INTERVALS (USING CEILING & FLOOR)
Tip: Alter the minutes entered in red to change the rounding intervals.
34
ROUNDING TIME AND TIME INTERVALS (USING MROUND)
Tip: Alter the minutes entered in red to change the rounding intervals.
CONVERT TIME TO DECIMALS
Often, we want to use time in other calculations that require a decimal format, like wage calculations. For this we need to convert time to its decimal
equivalent. Since we know there are 24 hours in a day, and 60 minutes in an hour and 60 seconds in a minute, we can multiply the time serial number to
convert it to a decimal equivalent. The table below provides the logic for converting to hours, minutes or seconds.
CONVERT MINUTES TO HOURS, HOURS TO DAYS ETC. IN DECIMALS
35
EXCEL DATE AND TIME FUNCTIONS
Date & Time
Functions
Available
From*
Syntax - FUNCTION(argument1,
argument2,[argument3]…)
Description
DATE
Excel 2003
DATE(year,month,day)
Returns a date in serial number from separate year, month
and day values
TIME
Excel 2003
TIME(hour,minute,second)
Returns a time serial number, from separate hour, minute
and second values
DATEVALUE
Excel 2003
DATEVALUE(date_text)
Converts a date text string to a date serial number
TIMEVALUE
Excel 2003
TIMEVALUE(time_text)
Converts a time text string to a time serial number
NOW
Excel 2003
NOW()
Returns the current date & time
TODAY
Excel 2003
TODAY()
Returns today's date
HOUR
Excel 2003
HOUR(serial_number)
Returns the hour part of a time serial number
MINUTE
Excel 2003
MINUTE(serial_number)
Returns the minute part of a time serial number
SECOND
Excel 2003
SECOND(serial_number)
Returns the seconds part of a time serial number
DAY
Excel 2003
DAY(serial_number)
Returns the day (of the month) from a date serial number
36
Date & Time
Functions
Available
From*
Syntax - FUNCTION(argument1,
argument2,[argument3]…)
Description
MONTH
Excel 2003
MONTH(serial_number)
Returns the month from a date serial number
YEAR
Excel 2003
YEAR(serial_number)
Returns the year from a date serial number
WEEKNUM
Excel 2003
WEEKNUM(serial_number, [return_type])
Returns an integer representing the week number (from 1
to 54) of the year from a date serial number
ISOWEEKNUM
Excel 2013
ISOWEEKNUM(date)
Returns the ISO week number of the year for a given date
WEEKDAY
Excel 2003
WEEKDAY(serial_number, [return_type])
Returns an integer representing the day of the week from
a date serial number
EDATE
Excel 2003
EDATE(start_date,months)
Returns a date that is the specified number of months
before or after an initial supplied start date
EOMONTH
Excel 2003
EOMONTH(start_date,months)
Returns a date that is the last day of the month that is a
specified number of months before or after the date serial
number
WORKDAY
Excel 2003
WORKDAY(start_date,days, [holidays])
Returns a date a number of working days (excluding
weekends & holidays) before or after a given start date
WORKDAY.INTL
Excel 2010
WORKDAY.INTL(start_date,days,
[weekend] , [holidays])
Returns a date a number of working days (excluding
weekends & holidays) before or after a given start date,
using supplied parameters to specify weekend days
DAYS
Excel 2013
DAYS(end_date,start_date)
Calculates the number of days between 2 dates
DAYS360
Excel 2003
DAYS360(start_date, end_date, [method])
Calculates the number of days between 2 dates, based on
a 360-day year (12 x 30-day months)
37
Date & Time
Functions
Available
From*
Syntax - FUNCTION(argument1,
argument2,[argument3]…)
Description
NETWORKDAYS
Excel 2003
NETWORKDAYS(start_date, end_date,
[holidays])
Returns the number of whole networkdays (excluding
weekends & holidays), between two dates
NETWORKDAYS.
INTL
Excel 2010
NETWORKDAYS.INTL(start_date,end_date,
[weekend], [holidays])
Returns the number of whole networkdays (excluding
weekends & holidays), between two dates, using
parameters to specify weekend days
YEARFRAC
Excel 2003
YEARFRAC(start_date, end_date, [basis])
Calculates the fraction of the year represented by the
number of whole days between two dates
DATEDIF
Excel 2003
DATEDIF(start_date,end_date,unit)
Calculates the number of days, months, or years between
two dates. This function is useful in formulas where you
need to calculate an age.
Warning: Excel provides the DATEDIF function in order to
support older workbooks from Lotus 1-2-3. The DATEDIF
function may calculate incorrect results under certain
scenarios. Please see the DATEDIF section for further
details.
38
DATE FUNCTION
The DATE function combines separate year, month and day values and converts them into a date serial
number formatted as a date.
Syntax:
=DATE(year, month, day)
year
The value of the year argument can include one to four digits from 1900 to 9999.
month
A positive or negative integer representing the month of the year from 1 to 12 (January to
December).
day
A positive or negative integer representing the day of the month from 1 to 31.
Tip: We can use the DATE function to add or subtract months or years to a date. See below.
Tip: We can also use the EDATE function to roll dates forward by a set number of months.
Note: If we add days and they exceed the number of days in the month, DATE will add the excess days to the
start of the following month. See examples below.
The Year argument must be a positive value, but month and day can be negative! If month is
greater than 12, month adds that number of months to the first month in the year specified.
If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from
the first month in the year specified. See examples below.
40
TIME FUNCTION
The TIME function combines separate hour, minute and second values and converts them into a time serial
number. If the cell format was General before the formula is entered, the result is formatted as a date.
Syntax:
=TIME(hour, minute, second)
hour
A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be
divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) =
TIME(3,0,0) = .125 or 3:00 AM.
minute
A number from 0 to 32767 representing the minute. Any value greater than 59 will be
converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30
PM.
second
A number from 0 to 32767 representing the second. Any value greater than 59 will be
converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) =
.023148 or 12:33:20 AM
Tip: We can use the TIME function to add or subtract hours, minutes or seconds to a time. See below.
Notice the last result shows the #NUM! error. This is because it results in a negative time and Excel can't
calculate that. More on how to solve this later.
Note: If we add time that takes us past 24 hours it will add the excess time to 0 time, effectively starting the
clock again. See examples below.
41
DATEVALUE FUNCTION
The DATEVALUE function converts dates stored as text to a date serial number.
Remember; my dates are dd/mm/yyyy
Syntax:
= DATEVALUE(date_text)
Tip 1: Notice DATEVALUE doesn't like dates separated with periods.
Tip 2: if you don't have the year present Excel will input the year based on your PC's built in clock!
So, you can see the DATEVALUE function can fix many date formats, but you may find it doesn't work with
everything. Don't worry, I’ll cover more ways to fix dates formatted as text soon.
42
TIMEVALUE FUNCTION
The TIMEVALUE function converts times stored as text to a date-time serial number.
Syntax:
= TIMEVALUE(time_text)
Tip: The TIMEVALUE function is handy for extracting the time portion from a date-time text value.
Note: TIMEVALUE can't fix times where the AM/PM isn't entered with a space after the time.
43
NOW FUNCTION
The NOW function is unusual in that it doesn't have any arguments. It simply returns the current date and
time from your computer clock.
Caution: The NOW function is volatile, meaning it recalculates evertime virtually anything changes in your
workbook...best use it sparingly!
Syntax:
=NOW()
Tip: When you enter the NOW function Excel will format the cell in the date - time format based on your
regional settings, (where no prior formatting was applied to the cell).
Use NOW to calculate the difference between a date-time serial number and now. e.g. how many hours since
x or what will the time be in x hours.
44
TODAY FUNCTION
The TODAY function is unusual in that it doesn't have any arguments. It simply returns the current date serial
number from your computer clock.
Caution: The TODAY function is volatile, meaning it recalculates every time virtually anything changes in your
workbook...best use it sparingly!
Syntax:
=TODAY()
Tip: When you enter the TODAY function Excel will format the cell in the date format based on your regional
settings, (where no prior formatting was applied to the cell).
Use TODAY to calculate the difference between a date serial number and today's date. e.g. how many days
since x, or what will the date be in x days' time.
45
HOUR FUNCTION
The HOUR function returns the hour integer from a time serial number ranging from 0 to 24.
Syntax:
=HOUR(serial_number)
Tip: cell C11 returns 12 because 12 hours is 50% of 24 hours.
The HOUR function will also accept a text string, no need to convert the text to a date-time serial number
first e.g. "4:15 AM"
46
MINUTE FUNCTION
The MINUTE function returns the minute integer, ranging from 0 to 59, from a time serial number.
Syntax:
=MINUTE(serial_number)
Tip: 0 is returned in cell C13 because there is no time portion to this date.
The MINUTE function will also accept a text string, no need to convert the text to a date-time serial number
first e.g. "4:15 AM"
47
SECOND FUNCTION
The SECOND function returns the second integer from a time serial number ranging from 0 to 59.
Syntax:
=SECOND(serial_number)
Tip: 0 is returned in cell C13 because there is no time portion to this date.
The SECOND function will also accept a text string, no need to convert the text to a date-time serial number
first e.g. "4:15:58 AM"
48
DAY FUNCTION
The DAY function returns the day of the month between 1 and 31 from a date serial number or text.
Syntax:
=DAY(serial_number)
Tip: Rows 11:14 contain dates as text in column B, but DAY is still able to return the day! Don't rely on this
though as results may be hit and miss.
You can also nest the DATE Function inside DAY, like so:
Formula: =DAY( DATE(2017,03,15) )
Result: =15
Or DATEVALUE like so:
Formula: =DAY( DATEVALUE("2017/03/15") )
Result: =15
49
MONTH FUNCTION
The MONTH function returns the month of a date serial number between 1 and 12, representing January
through December.
Syntax:
=MONTH(serial_number)
Tip: Rows 11:15 contain dates as text in column B, but MONTH is still able to return the month number! We
can exploit this feature to convert text months to month numbers which I’ll show you later.
You can also nest the DATE Function inside MONTH, like so:
Formula: =MONTH( DATE(2017,03,15) )
Result: =3
Or DATEVALUE like so:
Formula: =MONTH( DATEVALUE("2017/03/15") )
Result: =3
50
YEAR FUNCTION
The YEAR function returns the year from a date serial number as an integer between 1900 and 9999.
Syntax:
=YEAR(serial_number)
Tip: Rows 11:15 contain dates as text in column B, but YEAR is still able to return the year number! Where a
year isn't specified Excel will assume the year from your PC clock. Don't rely on dates stored as text as results
may be hit and miss.
You can also nest the DATE Function inside YEAR, like so:
Formula: =YEAR( DATE(2017,03,15) )
Result: =2017
Or DATEVALUE like so:
Formula: =YEAR( DATEVALUE("2017/03/15") )
Result: =2017
51
WEEKNUM FUNCTION
The WEEKNUM function returns the week number (between 1 and 54) of a date serial number. e.g. 1st Jan
2017 is in week number 1 of the year.
There are two types of systems available with this function:
- System 1 where the week containing January 1 is the first week of the year, and is numbered week 1.
- System 2 starts with the first Thursday of the year being in week 1. This system is the methodology
specified in ISO 8601, which is commonly known as the European week numbering system.
Syntax:
= WEEKNUM(serial_number, [return_type])
Note: The return type argument is optional, if omitted it will default to return type 1.
Below is a list of the return type options available:
Tip: Return types 2 and 11 for Monday are the same as one another. Type 2 is only included for backward
compatibility with earlier versions of Excel. Likewise, return type 1 for Sunday.
The next page contains some examples of WEEKNUM formulas using different return types.
System 1
System 2
52
Date
Return Type
Return Type Day
WEEKNUM Formula
Result
Sun, Jan 01, 2017
1
Sunday
1
Sun, Jan 01, 2017
2
Monday
1
Sun, Jan 01, 2017
11
Monday
1
Sun, Jan 01, 2017
12
Tuesday
1
Sun, Jan 01, 2017
13
Wednesday
1
Sun, Jan 01, 2017
14
Thursday
1
Sun, Jan 01, 2017
15
Friday
1
Sun, Jan 01, 2017
16
Saturday
1
Sun, Jan 01, 2017
17
Sunday
1
Sun, Jan 01, 2017
21
Monday
52
Mon, Mar 13, 2017
1
Sunday
11
Mon, Mar 13, 2017
2
Monday
12
Mon, Mar 13, 2017
11
Monday
12
Mon, Mar 13, 2017
12
Tuesday
11
Mon, Mar 13, 2017
13
Wednesday
11
Mon, Mar 13, 2017
14
Thursday
11
Mon, Mar 13, 2017
15
Friday
11
Mon, Mar 13, 2017
16
Saturday
11
Mon, Mar 13, 2017
17
Sunday
11
Mon, Mar 13, 2017
21
Monday
11
Thu, Jan 05, 2017
1
Sunday
1
Thu, Jan 05, 2017
2
Monday
2
Thu, Jan 05, 2017
11
Monday
2
Thu, Jan 05, 2017
12
Tuesday
2
Thu, Jan 05, 2017
13
Wednesday
2
Thu, Jan 05, 2017
14
Thursday
2
Thu, Jan 05, 2017
15
Friday
1
Thu, Jan 05, 2017
16
Saturday
1
Thu, Jan 05, 2017
17
Sunday
1
Thu, Jan 05, 2017
21
Monday
1
53
ISOWEEKNUM FUNCTION
The ISOWEEKNUM function (new in Excel 2013) returns the week number of a date serial number. The first
Monday of the year marks the start of week 1.
Tip: it also works with dates input as Text, but this could be unreliable so it's always best to work with date
serial numbers.
Syntax:
=ISOWEEKNUM(date)
54
WEEKDAY FUNCTION
The WEEKDAY function returns the day number of the week from a date serial number. The default return
type ranges from 1 for Sunday, through to 7 for Saturday, but you can choose from a list of return types.
Syntax:
=WEEKDAY(serial_number, [return_type])
serial_number
A date serial number
return_type
The return type argument is optional, if omitted it will default to return type 1.
Tip: Return types 1 and 17 for Sunday are the same as one another. Type 1 is only included for backward
compatibility with earlier versions of Excel. Likewise, return type 2 and 3 for Monday.
55
EDATE FUNCTION
The EDATE function rolls a date serial number (ignoring any time element) forward or back based on the
number of months specified in the 'months' argument.
Syntax:
=EDATE( start_date, months)
start_date
A date serial number or text
months
The number of months before or after start_date. A positive value for months yields a
future date; a negative value yields a past date.
Notice the number of days varies depending on the month being adjusted.
Tip: EDATE works with dates stored as text (B15), but this is not reliable. It's always best to work
with date serial numbers.
Note: only whole numbers are recognised by the 'months' argument. 6.8 in cell C16 is rounded down to 6.
56
EOMONTH FUNCTION
The EOMONTH function returns the last day of the month before or after a start date specified with a date
serial number (ignoring any time element).
Syntax:
=EOMONTH( start_date, months)
start_date
A date serial number or text
months
The number of months before or after start_date. A positive value for months yields a future
date; a negative value yields a past date.
Note: only whole numbers are recognised by the 'months' argument. e.g. 1.7 months would be rounded
down to 1.
Tip: EOMONTH works with dates stored as text (e.g. cell B14), but this is not reliable. It's always best to work
with date serial numbers.
Bonus Tip: There's no 'STARTOFMONTH' function, but you can also use EOMONTH to find the start of a
month, e.g. let's say your 'start date' is 30th January and you want to return 1st January. You simply use
EOMONTH to find the end of the previous month and then add 1:
Formula: =EOMONTH(DATE(2017,01,30), -1) +1
Result: = 1st January 2017
57
WORKDAY FUNCTION
The WORKDAY function returns a date serial number that is the specified number of working days before or
after the start date. Working days exclude weekends and any dates identified as holidays.
WORKDAY is handy when calculating due dates that exclude weekends (Saturday & Sunday) or holidays.
Syntax:
=WORKDAY( start_date, days, [holidays])
start_date
A date serial number or text
days
Number of non-weekend and non-holidays to add or subtract
[holidays]
Optional list of holidays to exclude
Note 1: only the date portion of a date-time serial number is used by WORKDAY. Any time element is
ignored.
Note 2: only whole numbers are recognised by the 'days' argument. e.g. 1.7 days would be rounded down to
1 day.
58
WORKDAY.INTL FUNCTION
The WORKDAY.INTL function (new in Excel 2010) returns a date serial number that is the specified number of
working days before or after the start date. Working days exclude weekends, which can be customized, and
any dates identified as holidays.
WORKDAY.INTL is handy when calculating due dates that exclude weekends or holidays where your weekend
days aren't Saturday and Sunday.
Syntax:
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
start_date
A date serial number or text
days
Number of non-weekend and non-holidays to add or subtract
[weekend]
Optional - choose which days of the week are not working days (see list below). If omitted,
the default is Saturday and Sunday.
[holidays]
Optional list of holidays to exclude
59
Note 1: only the date portion of a date-time serial number is used by WORKDAY.INTL. Any time element is
ignored.
Note 2: only whole numbers are recognised by the 'days' argument. e.g. 1.7 days would be rounded down to
1 day.
Bonus Tip: the weekend argument can be stipulated using a text string of 7 characters, with each character
representing a day, starting with Monday. A 1 represents a non-working day and a 0 represents a workday.
See examples below which stipulate non-workdays for Thursday, Friday, Saturday and Sunday.
The text string can be placed in a cell e.g. D50 or directly in the formula, see cell E51.
60
DAYS FUNCTION
The DAYS function (new in Excel 2013) returns the number of whole days between two date serial numbers.
Ignores time portions of date-time serial numbers.
Syntax:
=DAYS( end_date, start_date)
start_date
A date serial number or text
end_date
A date serial number or text
Notice the first day isn't included in the count. You need to add 1 to the result if you want inclusive
dates.
Tip: DAYS works with dates stored as text (cell B13), but this is not reliable. It's always best to work with date
serial numbers.
61
DAYS360 FUNCTION
The DAYS360 function returns the number of days between two date serial numbers based on a 360-day
year, or twelve 30-day months. This method is used in some accounting calculations.
Syntax:
=DAYS360(start_date, end_date, [method])
start_date
A date serial number or text
end_date
A date serial number or text
[method]
Optional logical value TRUE/FALSE – see more below.
The optional 'method' argument is a logical value (TRUE/FALSE) that specifies whether to use the U.S. or
European method. If omitted it is treated as FALSE.
FALSE: U.S. (NASD) method. If the starting date is the last day of a month, it becomes equal to the 30th day
of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th
day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date
becomes equal to the 30th day of the same month.
TRUE: European method. Starting dates and ending dates that occur on the 31st day of a month become
equal to the 30th day of the same month.
Notice the difference between the TRUE/FALSE method results.
Tip: DAYS360 works with dates stored as text (cells B27 & B35), but this is not reliable. It's always
best to work with date serial numbers.
Note: only the date portion of a date-time serial number is used by DAYS360. Any time element is ignored.
As a result, only whole days are returned.
62
NETWORKDAYS FUNCTION
The NETWORKDAYS function returns the number of whole working days between two date serial numbers,
excluding weekends (Saturday & Sunday) and holidays.
Syntax:
=NETWORKDAYS(start_date, end_date, [holidays])
start_date
A date serial number or text
end_date
A date serial number or text
[holidays]
This optional argument can be a range of cells containing holiday dates that you want to
exclude from the workday count.
In the example below, my list of holidays are in cells J11:J18.
Note: NETWORKDAYS results are inclusive of the start and end dates.
Tip: NETWORKDAYS works with dates stored as text, but this is not reliable. It's always best to work with date
serial numbers.
63
NETWORKDAYS.INTL FUNCTION
The NETWORKDAYS.INTL function (new in Excel 2010) returns the number of working days between two date
serial numbers, excluding weekends and holidays. Weekend days can be specified.
Syntax:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
[weekend]
This optional argument allows you to specify which days are included in the weekend. See
list below:
[holidays]
This optional argument can be a range of cells containing holiday dates that you want to
exclude from the workday count. In the example table below, my holidays are in cells
J24:J31.
64
Note: NETWORKDAYS.INTL results are inclusive of the start and end dates.
Tip: NETWORKDAYS.INTL works with dates stored as text, but this is not reliable. It's always best to work with
date serial numbers.
Bonus Tip: the weekend argument can be stipulated using a text string of 7 characters, with each character
representing a day, starting with Monday. A 1 represents a non-working day and a 0 represents a workday.
See examples below which stipulate non-workdays for Thursday, Friday, Saturday and Sunday.
The text string can be placed in a cell e.g. D45 or directly in the formula, see cell E46.
65
YEARFRAC FUNCTION
The YEARFRAC function returns the year fraction representing the number of whole days between start_date
and end_date serial numbers.
This function is useful for calculating the portion of a whole year for use in other calculations and is
particularly useful in HR/employee related calculations.
Syntax:
=YEARFRAC(start_date, end_date, [basis])
start_date
A date serial number or text
end_date
A date serial number or text
[basis]
This optional argument allows you to specify the day count method from the list of options
below. If omitted, it defaults to 0.
Tip: You're most likely to use basis 1, unless your business uses a specific system.
Note 1: #NUM! Errors are returned if the basis argument is <0 or >4.
Note 2: only the date portion of a date-time serial number is used by YEARFRAC. Any time element
is ignored. As a result, only whole days are returned.
66
DATEDIF FUNCTION (USE WITH CAUTION)
For a long time, the DATEDIF function was undocumented. It is only included in Excel for backward
compatibility with Lotus 1-2-3! If you try to enter the function it won't display in the auto-complete list, you
must be brave and press on, confident in the fact that when you press ENTER it will evaluate!
DATEDIF calculates the number of days, months or years between two dates, but it is known to return
incorrect results in some situations and is particularly buggy in Excel 2007. See details below.
Syntax: =DATEDIF(start_date, end_date, unit)
Syntax:
=DATEDIF(start_date, end_date, unit)
start_date
A date serial number
end_date
A date serial number
unit
The unit specifies what you want returned. The table below outlines the options.
Unit
Returns
"Y"
The number of complete years in the period.
"M"
The number of complete months in the period.
"D"
The number of days in the period.
"MD"
The difference between the days in start_date and end_date.
The months and years of the dates are ignored.
"YM"
The difference between the months in start_date and end_date.
The days and years of the dates are ignored.
"YD"
The difference between the days of start_date and end_date.
The years of the dates are ignored.
Caution! The "M" and "MD" arguments have known limitations. See examples over.
Note: DATEDIF calculations do not include the start date in the resultant count, and they only return
whole numbers.
Tip: when entering the Unit direct in the formula, surround it with double quotes e.g. =DATEDIF(start_date,
end_date, "M")
68
DATEDIF ALTERNATIVES
Now you see how unreliable DATEDIF can be, it's probably better to err on the side of caution and use a
different function to achieve the same results. *Note the M substitutes either round the months up or down.
There is no direct equivalent to DATEDIF with Unit "M". Likewise, the alternate to MD doesn't replicate
perfectly in every scenario.
69
MORE RESOURCES
Weekly Newsletter
100 Tips & Tricks eBook & Newsletter..................
https://www.myonlinetraininghub.com/sign-up-for-100-excel-tips-and-tricks
Tutorials
Excel Formulas.................................................................
https://www.myonlinetraininghub.com/excel-formulas
PivotTable Blog Posts..................................................
https://www.myonlinetraininghub.com/category/excel-pivottables
Power Query Blog Posts..............................................
https://www.myonlinetraininghub.com/category/power-query
Power Pivot Blog Posts................................................
https://www.myonlinetraininghub.com/category/power-pivot
Excel Macros and VBA Blog Posts...........................
https://www.myonlinetraininghub.com/category/excel-vba
Charting Blog Posts.......................................................
http://www.myonlinetraininghub.com/category/excel-charts
Excel Dashboard Blog Posts.......................................
http://www.myonlinetraininghub.com/category/excel-dashboard
Free Webinars
Excel Dashboards...........................................................
http://www.myonlinetraininghub.com/excel-webinars
Power BI.............................................................................
https://www.myonlinetraininghub.com/excel-dashboard-webinar-
reg.htm#pbi_anchor
Support
Excel Forum.......................................................................
https://www.myonlinetraininghub.com/excel-forum
Courses
Complete Course Library............................................
https://www.myonlinetraininghub.com/
Premium Bundle (Excel, Word & Outlook)..........
https://www.myonlinetraininghub.com/microsoft-office-online-training-courses
Advanced Excel...............................................................
https://www.myonlinetraininghub.com/excel-expert-upgrade
PivotTables.......................................................................
https://www.myonlinetraininghub.com/excel-pivottable-course
70
Power Query.....................................................................
https://www.myonlinetraininghub.com/excel-power-query-course
Power Pivot.......................................................................
https://www.myonlinetraininghub.com/power-pivot-course
Excel for Decision Making...........................................
https://www.myonlinetraininghub.com/excel-for-decision-making-course
Excel for Customer Service.........................................
https://www.myonlinetraininghub.com/excel-for-customer-service-professionals
Excel for Finance.............................................................
https://www.myonlinetraininghub.com/excel-for-finance-course
Excel Analysis ToolPak.................................................
https://www.myonlinetraininghub.com/excel-analysis-toolpak-course
Excel Dashboards...........................................................
http://www.myonlinetraininghub.com/excel-dashboard-course
Power BI.............................................................................
http://www.myonlinetraininghub.com/power-bi-course
71
ABOUT
One of the most challenging aspects of Excel is working with times and
dates. It's certainly one of the most common topics I get asked about.
So, I wanted to put together a comprehensive resource that you could
use to learn and master working with dates and times, as well as a tool
you could refer back to from time to time as required.
Please feel free to share this eBook with your friends and colleagues.
The more people I can help, the better. And if you have any feedback
or suggestions, I'd love to hear from you.
Mynda Treacy
P.S. If you're interested, click here to read more about my background.
© Copyright MyOnlineTrainingHub.com
THANKS
I'd like to thank and give credit to the following sites that helped me understand date and time in Excel:
• Chip Pearson: http://www.cpearson.com/excel/datetime.htm
• Microsoft: https://support.office.com/en-US/article/Date-and-time-functions-reference-
fd1b5961-c1ae-4677-be58-074152f97b81
And finally, I'd like to thank Sunny Kow for his tech checking and suggestions for additional topics.
