Date and Time

How do Dates and Times work in Excel

In Excel, dates and times are considered as numbers : the date 0 is the first of January 1990, and each day since is 1. Therefore, the 16th of May 2014 is 41775, number of days since the 1st of January 1900.

In the same way, times are also considered as numbers. 24h equals 1. So, when it's midday, the time is equal to 0.5.

The cell containing a date or a time has to be in the correct "date & time" cell format

Considering date and times as numbers allows you to make standard operations with them : add, divide, average, etc.

For example :

• Add the length of two constructions
• Divide a project's duration by the number of participants
• Calculate the average processing time for a piece

How to use date & time functions in Excel

There are number of pre-programmed functions in Excel, to manipulate dates and times functions. (This is a true Excel file, please handle it!)

The date and time functions

You will see by clicking on the "start" button that there is some date and time functions. The most usefull are :

• The functions =TODAY () and =NOW () do not use arguments. They respectively display the current date and the current time.

• =YEAR (), =MONTH (), =DAY (), =HOUR (), =MINUTE () and =SECOND () use one argument : a date, an hour or both. They return respectively the year, the month (1 to 12), the day of the month (1 to 31) for that date, and the hour (0 to 23), minutes (0 to 59), and seconds (0 to 59) corresponding to this time.

• The WEEKNUM() function, which also takes as argument a date, returns the week number in the year (1-52) corresponding to that date.

• =WEEKDAY () function returns the day of the week corresponding to a date. It takes two parameters:

• a date
• a number from 1 to 17, which allows you to choose how you count the days of the week. For example, if the setting is 1, the days of the week start on Sunday: Sunday will be worth 1, Monday 2 and Saturday 7 If the parameter is 2, the week begins on Monday, will be worth 1, and Sunday will be worth 7.

Some tips

• The dates and times can add up and subtract in Excel, as you will see in the following exercises.
• When you encounter a problem or inconsistency in the dates and times, it often comes from the cell format, make sure it is consistent before giving up!