2. Date functions
Before I show you some Excel date functions, I will show you how Excel handles dates and times.
How does Excel handle dates and times? Notion of Serial Number
When you write values with certain formats, Excel can recognise them as dates and times. Here are some examples:
- 03/08/2018 17:20
Excel will not recognize "20/15/2018" as a date. Besides, it is not a valid date.
How do you know if Excel has recognised your entry as a date and time?
Then enter these values in an Excel sheet as follows:
Then select the A2:A6 range and convert its format to "standard". I remind you how:
- Select the range A2:A6
- Use the Format button in the Cells group on the Home tab of the Ribbon
- Click on Format Cells... in the menu that appears
- The Format Cells dialog box appears. Click on the Number tab
- Finally click on General in the Category list. Validate with the OK button.
Here is the result:
Values recognised by Excel as dates and times are converted to numbers.
How does Excel handle dates and times?
Excel stores dates and times as numbers with a decimal part. The number related to a date-time is called the Serial Number.
So, for Excel, dates and times are numbers. When you enter, for example, "14/05/2018", Excel saves it as a number, and retains roughly the format you entered. You can then change its format as you wish.
You can even enter a number and convert its format to a date and time.
What is the correspondence between date-time and number?
First, the integer part corresponds to the date and the decimal part corresponds to the time.
Let's detail this with an example. i.e. the value 43315.7258680556 which corresponds to the date-time 03/08/2018 17:25:15.
The decimal part 0.7258680556 corresponds to the hour-minute-second. To find the time, I multiply it by 24:
0.7258680556 * 24 = 17.42083333
The integer part "17" of this value is the hour. Then I multiply the decimal part of this value by 60:
0.42083333 * 60 = 25.25
The integer part "25" of this value is the minute. Then I multiply the decimal part of this value by 60:
0.25 * 60 = 15
15 is the number of seconds.
Regarding the dates:
- The value 1 corresponds to 01/01/1900. Moreover, Excel does not manage dates prior to 01/01/1900.
- The difference between the serial numbers of two successive days is 1.
The calculation of the day, month and year from a serial number is a bit complicated because it is necessary to take into account the number of days of each month and taking into account the leap years.
Excel functions allow to find the components of a date-time from its serial number.
TODAY and NOW functions
Both functions take no arguments.
The TODAY function returns today's date
The NOW function returns the current date and time
DATE and TIME functions
year : number between 1900 and 1999 which defines the year of the returned date
month : number between 1 and 12 which defines the month of the returned date
day : number between 1 and 31 which defines the day of the month of the returned date
hour : number between 0 and 23 which defines the hour of the returned value
minute : number between 0 and 59 which defines the minute of the returned value
second : number between 0 and 59 which defines the second of the returned value
The DATE function returns a date value corresponding to the values given in the argument representing the year, month and day.
The TIME function returns a time value corresponding to the values given as arguments representing the hour, minute and second.
Consider a spreadsheet where the values for a date are written in 3 columns, column A for the year, B for the month and C for the day. We want to have the "complete" date in column D. The formula to write in D2 is :
Note that when the day is greater than 31 or the month is greater than 12, the DATE function does not return an error. Instead, the excess is transformed into months and years.
Similarly, when the second or minute is greater than 60, the TIME function does not return an error. On the contrary, the excess is transformed into minutes and hours.
The DAYS function returns the number of days between two dates.
end_date : end date of the period to be calculated
start_date : start date of the period to be calculated
The function DAYS returns the number of days between two dates. If end_date is earlier than start_date, the returned value is negative.
NB. Since dates in Excel are numbers, we can calculate the number of days between two dates by writing :
end_date - start_date
Is there a difference to the result of the DAYS function?
The answer is Yes when one or both dates also contain the time, because DAYS ignores the time and always returns an integer value.
Consider a spreadsheet where we record the date of completion of two steps in a process. We want to calculate the time between the two steps. The formula to write in C2 is: "=DAYS(B2,A2)".
How to add a number of days to a date?
I remind you that dates in Excel are represented by numbers called serial numbers and that the difference between the serial numbers of two successive days is 1. So, to add a number of days to a date, you simply add that number to the date.
For example, the value in cell A2 is a date, and we want to have in B2 the date one week later. In cell B2, we must write the formula :
I remind you that the date in A2 can be displayed in any format, but must be recognised by Excel as a date.
And how to add a number of months to a date? Is it enough to add this number multiplied by 30?
The answer is that the result in this case would not be accurate. The reason is that not all months are 30 days. For example, if I add 30 to "01/07/", I will get "31/07/" and not "01/08/". Similarly, if I add 30 to "01/02/", it will be "02/03/" or "03/03/".
To add a number of months and get the same day of the month, Excel has provided the function EDATE.
start_date : the start date to which we want to add a number of months
months : number of months to be added
The date after start_date by the number of months specified by the months argument when this is positive. The date before start_date by the number of months specified by the months argument when this is negative.
NB. To add years to a date, you must use the function EDATE, with the number of years to be added multiplied by 12 as the 2nd argument.
In the following spreadsheet extract, the promotion date is determined, which is 2.5 years after the recruitment date. The formula in cell C2 is :
YEAR, MONTH, DAY, WEEKDAY, HOUR, MINUTE and SECOND functions
serial_number : a date (represented in Excel by a number called serial number) that we want to know one of its components : the year, the month, the day of the month, the day of the week, the hour, the minute or the second.
The YEAR function returns the year of the date given in argument
The MONTH function returns the month of the given date in argument: 1 to 12
The DAY function returns the day of the month of the given date in argument : 1 to 31
The WEEKDAY function returns the day of the week of the given date in argument : 1 to 7
The HOUR function returns the hour of the date-time given in argument : 0 to 23
The MINUTE function returns the minute of the date-time given in argument : 0 to 59
The SECOND function returns the second of the date-time given in argument : 0 to 59