Excel 2019/365: Date Functions
Updated 220190808
· Learn how dates and times are represented internally in Excel.
· Learn how to format dates.
· Learn how to use the Today() function to get the current date.
· Learn how to use the Now() function to get the current date and time.
· Learn how to use the Date() function convert a month, day, and year into an Excel date (serial number).
· Learn how to use the DateDif() function to determine how much time has elapsed between two dates.
· Learn how to use the Month(), Day(), Year(), and WeekDay() functions to extract individual fields from an Excel date.
To enter a date in a cell, simply type the date in the format mm/dd/yyyy or mmddyyyy. Use slashes or hyphens to separate the month from the day and the day from the year. If you use any other separator character, it will look correct, but Excel will not represent it internally as a date and you will not be able to perform date arithmetic on it.
To enter a time in a cell, simply type the time in the format hh:mm or hh:mm:ss. Append either AM or PM (in either uppercase or lowercase) to the end of the time. The seconds field (ss) is optional. You must have a blank between the time and the AM or PM. You cannot use periods (e.g. A.M. or P.M.) Note that if you do not enter a date before the time, the number 0 will be to the left of the decimal point and the date 1/0/1900 will be assumed (and while this is not a valid date, it does not cause a problem)!
You should always enter years as 4digit numbers (entering years as 2digit numbers was the cause of the "Year 2000 problem"). However, if you enter the year portion of a date as a 2digit number, you need to know how Excel will interpret it. Excel interprets two–digit dates like this:
· 3099 – are treated as part of the 20^{th} century. That is, the digits "19" are put on the front of the number.
· 029 – are treated as part of the 21^{st} century. The digits "20" are put on the front of the number.
Excel stores dates using a serial number. Serial numbers are used to count the days. The counting started on 1/1/1900.
The serial number 1 is January 1, 1900, serial number 2 is January 2, 1900, etc., like this:
1 
1/1/1900 
2 
1/2/1900 
3 
1/3/1900 
4 
1/4/1900 
5 
1/5/1900 
etc. 

January 1, 2000, is 36,526 (note that this is 365 days * 100
years + 25 leap year days + 1). This is actually incorrect, because there were
only 24 leap year days (1900 was not a leap year), but Microsoft deliberately
put this bug into Excel to make it compatible with the Lotus 123 spreadsheet.
Times are stored as a fraction of a day. For example, .5 is half a day, or 12:00 noon. .75 is threefourths of a day, or 6:00 p.m., etc.
Dates and times are combined – the integer part of a serial number represents the date, and the fractional part of a serial number represents the time of day. So 12 noon on January 1, 2000 is represented as 36,526.50.
You can determine how much time has passed between two dates simply by subtracting them. Since Excel stores dates as serial numbers, subtracting two dates will give you the number of days from the first date to the second date. Note that Excel may sometimes format these numbers incorrectly; sometimes it will try to format the difference between two dates as a date, which will give a strangelooking result.
For some reason, the DATEDIF function is not documented in Excel. It is used to determine how many days, months, or years have passed between two dates. It will drop any fraction of a year. The format of the DATEDIF function is:
=DATEDIF(earlyDate,
lateDate, type)
earlyDate must be
a date that comes before lateDate.
Type must be one of the following:
· "d" or "D"—computes the number of days between the two dates
· "m" or "M"—computes the number of complete months between the two days (does not round up)
· "y" or "Y"—computes the number of complete years between the two days (does not round up)
Finding somebody's age in Excel can get a bit complicated, but with the DATEDIF function, all you need to do is provide it with the two dates, and "y" (for "year") as the third argument. For example, if somebody was born on 9/1/1985 (see B1 below) and you want to find out how old they are on 8/31/2010 (see B2), their age would be 24 because they have not yet reached their 25^{th} birthday.
If we change the "Current Date" (B2) to any day from 9/1/2010 (25^{th} birthday) to 8/31/2011, we will get 25.
To get the current date to always appear in a cell, enter the =TODAY() function in the cell. Note that the today() function has no arguments; however, the parentheses are still required.
To get both the current date and the current time of day to appear in a cell, enter the =NOW() function in the cell. The now() function also has no arguments.
There may be times when you have a date (as a serial number) and you want to extract one of its fields: the month, the day, or the year. Excel has three functions for extracting these values.
The MONTH() function requires a single argument – a serial number representing a date. It returns a number in the range 112 representing the month part of that date.
The formula in B3 will return the number 12 (the month from B1).
After pressing the Enter key:
The DAY() function requires a single argument – a serial number representing a date. It returns a number in the range 131 representing the day of the month part of that date.
The formula in B4 will return the number 25 (the day from B1).
After pressing the Enter key:
The YEAR() function requires a single argument – a serial number representing a date. It returns a number in the range 1900 and above (dates before 1/1/1900 are illegal in Excel) representing the year part of that date.
The formula in B5 will return the number 2012 (the year from B1).
After pressing the Enter key:
The WEEKDAY() function requires a single serial number representing a date. It will return a number in the range 17 representing the day of the week, where Sunday is day 1, Monday is day 2, etc.
If you enter the =WEEKDAY(A1) in B1 below:
The number 7 will appear, telling us that the year 2000 began on a Saturday.
If you already have a spreadsheet with the dates spread out over three columns (a month column, a day column, and a year column), you may want to convert those three values into a single date serial number (so you can use it in date arithmetic calculations later). To do so, use the Date() function. The Date() function requires three arguments:
Date(year, month, day)
Note that they are not in the usual m/d/y format that we are used to, but are in decreasing order of magnitude (years are the biggest units, then months, and days are the shortest)
Entering the formula, before pressing the Enter key:
After pressing the Enter key:
What was special about 7/20/1969?