Dates

A date is a number that represents the number of days that have elapsed from a set period in history. Microsoft Excel uses natural numbers to represent a date value as the number of days that have passed since 1/1/1900 on MS Windows (or since 1/2/1904 on Macintosh).

To make a date easily recognizable, there is a formula you use, depending on the language. The first place you can check about representing dates is in Control Panel from the Regional Settings. In US English, this is represented as follows:

Based on this, to represent a date (in US English), you use placeholders for numbers and a special symbol called the Date Separator. The default and most used date separator (in US English is the forward slash "/". Here is an example of representing a date: 8/5/2000.

Besides the numbers, you can use words to represent a date. The days of a week have names. There are three main ways to represent a day of a week. You can use a complete name as Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday. Each of these names also has an equivalent short name you can use instead. They are Sun, Mon, Tue, Wed, Thu, Fri, and Sat, respectively. A day of a week can also be represented by a natural number. By default, this would be 1 for Sunday, 2 for Monday, 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday, and 7 for Saturday.

The months of a year have names. As done with weekdays, there are three main ways to represent a month. You can use a complete name as January, February, March, April, May, June, July, August, September, October, November, and December. Each of these names also has an equivalent short name you can use instead. They are Jan, Feb, Mar, Apr, May, Jun, Jul, Sep, Oct, Nov, and Dec respectively. A month can also be represented by a natural number. This would be 01 or 1 for January, 02 or 2 for February, 03 or 3 for March, 04 or 4 for April, 05 or 5 for May, 06 or 6 for June, 07 or 7 for July, 08 or 8 for August, 09 or 9 for September, 10 for October, 11 for November, or 12 for December.

When names to represent a date, you can use empty spaces to separate the components of a date. You can also use the comma and the empty space, where appropriate, to separate two sections of a date.

Microsoft Excel as a powerful mathematical tool, recognizes all the rules of date representation but because there can be so many ways of stating the same value, it completely allows you to indicate how you want your date values to be represented. Microsoft Excel also provides suggestive formats you can use.

To support dates values and operations, Microsoft Excel provides many functions for various scenarios. With some functions, you will only use the value they provide (or return, as we studied about functions). With some other function, you will have to create a date by providing the necessary value(s) as argument(s). There are two categories of date values you will need to keep in mind when creating your expressions: deterministic and non-deterministic:

• A date value is referred to as deterministic when you know its value with certainty. For example, suppose that a company decides that nobody will work on 25 December of every year. This is a fixed day. If you involve it in an expression, you know with certainty when this date occurs, which is 25 December of the year blah blah blah (the year is not important for this example)
• A date value is referred to as non-deterministic when you cannot know in advance when the exact date would occur but you know it will occur. For example, suppose that a company decides that, if December 20^th occurs on Sunday, then the employees would receive their paychecks the Friday before, otherwise the employees would receive their paychecks the following Monday. In this case, when you write the expression, you cannot know with certainty when the date would occur (don't think that you should/can write an long expression that covers dates from 2000 to 2050; although you can, this is not professional and it is poor development). In this case, the date value you are involving in an expression is non-deterministic

For the sake of this lesson, we will use a data type called DateTime that represents a date (and/or a time) value.

The TODAY() function can be used to get the current date of the computer. Its syntax is:

DateTime TODAY()

This function takes no argument but it returns a value that represents the date of the computer in which it is called.

To involve a fixed date in an expression, you can create it using the DATE() function. Its syntax is:

DateTime Date(int y, int m, int d)

This function takes three arguments and each must be an integer. The first argument is a year value. It can be passed as a 2-year or a 4-year digit. That is, the year can be as a number between 0 and 9999. If you pass the year with a single digit or two digits, like 2, 1900 would be added to it. For example, the number 2 would produce the year 1902. The number 08 would produce 1908 and not 2008. Therefore, you should make it a habit to pass the year with 4-digits. This is not an anomaly of Microsoft Excel. It was designed like that (if this appears like a problem to you, keep in mind that you are not the only one using MS Excel; someone else at the NIST or the Mossad is using it for something else, so 02 producing 1902 and not 2002 would suit him perfectly).

The second argument of this function represents the numeric month of the year. January is represented with 1 or 01, February with 2 or 02, etc.

The third argument of this function represents the numeric day of the month. 

Here is an example typed in a cell:

=Date(1988, 06, 05)

After calling this function and passing the required arguments, it produces a date value. When passing the value of the day, make sure you follow the rules of day values; otherwise, you may use an unpredictable result. If you provide an invalid date for either argument, Microsoft Excel would follow some algorithm to try to figure it out. For example, suppose you provide the following date:

=DATE(1975, 02, 35)

Fortunately, instead of displaying an error, Microsoft Excel would find out if the day number is valid. In this case it is not. Therefore, in this case, maximum number of days allowed for the month would be subtracted from the number you provided, the corresponding number of months would be added to the month value and the rest of days would be applied to the day argument:

Suppose a certain cell displays some text and that text might be the value of a date. Even if you believe that it is a date, it may be safe to convert such text first to a recognizable date value before using it. To convert text to a date value, you can use the DATEVALUE() function. Its syntax is:

DateTime DATEVALUE(Text)

This function takes one argument. The argument can be the content of a cell. When this function is called, it analyzes the text provided as argument. If the argument holds a valid date value, the function returns it. Here is an example:

DATEVALUE("05/2/1977")

If the argument is not a valid date, you would receive an error (#VALUE). A remedy is to check if the text represents a date, then convert it, otherwise ignore it or do something else.

If a date has already been created but you want to retrieve only the year part, the month part, the day part, or all of the, Microsoft Excel provides three functions that can take care of this.

Year: The YEAR() function is used to retrieve the numeric year of a date value. Its syntax is

int YEAR(DateTime Value)

This function takes one argument. When called, it analyzes the argument. If the argument is a valid date, then the function returns its year, a value between 1900 and 9999. For example, YEAR(12/05/2002) returns 2002. If you pass the argument as a (Long) natural number or a decimal, Microsoft Excel would use some algorithm, first to create a valid date from the number, second to retrieve the year part. For example YEAR(37940.574) would produce 2003. If the argument is not a valid date and Microsoft Excel cannot convert it a valid date, then the function would produce an error (#VALUE).

Month: The MONTH() function is used to get the numeric value of the month of a date value. Its syntax is:

int MONTH(DateTime Value)

Day: The Day() function is used to produce the day part of a date value.  Its syntax is:

int DAY(DateTime Value)

A time is a decimal number that represents a fraction of a day. Unlike a date whose starting point can depend on various factors, a time is usually stated to start on a non-spatial point called midnight.

Like a date, a time is represented with a combination of numbers and special characters. Like a date, the character used depends on the language you are using. The character used can be checked in the Regional Settings of Control Panel in the Time property page:

 To represent time, a day is divided into 24 non-spatial fractions and each of these fractions is called an hour. Each hour is divided into 60 fractions and each fraction is called a minute. The minutes are counted from 0 to 59. Each minute is divided into 60 fractions and each fraction is called a second. The seconds are counted from 0 to 59. Each second in divided into 1000 fractions and each fraction is called a millisecond. The milliseconds are counted from 0 to 999. The milliseconds are hardly used, only in extreme mathematically-oriented operations. To separate the sections of a time, you use a special character. In US English, that character is the colon. Therefore, 8 o'clock can be represented as 8:00

There are two ways to represent a time in a day. First, a day with light is divided into two sections. The first part is called morning. The other part is called afternoon. The period that divides them is called noon. One way you can represent a time, also called the military standard, counts the hours from 0 to 23. Using this technique, any time before 10 is represented with a leading 0. A time commonly called "8 o'clock" or "8 in the morning" is represented as 08:00. An equivalent time in the second part of the day is represented as 20:00. Using this military time, you don't actually don't indicate whether the time occurs in the morning or in the afternoon. The time stated provides complete information. The other technique of representing time consists of formally indicating in which part (also called a quadrant), the time period occurs. Using this technique, the first part of the day is represented with am, Am, or AM. This first part is counted from 0 to 11. Therefore, to indicate that the time occurs in the morning, after stating the time, you add an empty and type the two characters. An example is: 8:16 AM. The second part of the day is represented with the combination pm, Pm, PM. The hours of this part are also counted from 0 to 11. To indicate that the time occurs in the afternoon, after stating the time, you add an empty and type the two characters. An example is: 8:16 PM.

Microsoft Excel recognizes all the rules of time representation. Since there can be different scenarios to represent a value, it lets you customize how your time should appear.

In some circumstances, you may want a date and a time value values to be considered as one entity. This is possible.

To get the current date and time with one call, you can use the NOW() function. This function returns a value in the formula M/D/YYYY HH:MM.