Dates Related Functions

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

Dates-Related Functions

Introduction

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 20th 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.

Get Today's Date

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.

Create a Date

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:

Date

Date Conversion

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.

Returns the Parts of a Date

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)