Tag Archives: DATE()

How to use Excel’s DATE, DATEVALUE, MONTH, YEAR and DAY functions

Following video demonstrates the usage of Excel’s Date functions:

  • DATE() : Takes year, month and day as parameters and returns Excel date value.
  • DATEVALUE(): Takes date string as parameter and returns Excel date value.
  • YEAR():┬áTakes Excel date value as parameter and returns corresponding year number (1900..9999).
  • MONTH(): Takes Excel date value as parameter and returns corresponding month number (1..12).
  • DAY():┬áTakes Excel date value as parameter and returns corresponding day number (1..31).
If you are unfamiliar with Excel’s way to handle dates, please check
Excel date system basics.

Excel Date System Basics

Excel handles Date values as integer numbers. Number one means 1st of January 1900, two means 2nd of January 1900 and so on. Today (27th of February 2013) is day number 41332. So date number can be understood as the number of days since 31st of December 1899.

The first day that Excel supports is 1st of January 1900 and if you need earlier dates, Excel’s build in date functions are not working.

Excel actually supports two different Date Systems: In addition two this 1900 Date System you can also use 1904 Date System where number one means 1st of January 1904. This can be selected on Calculation settings but I recommend to use 1900 Date System to maintain better compatibility to e.g. other spreadsheet programs.

Excel handles times as decimal numbers between 0 and 1 and so 41332,41667 means that the date is 27th of February 2013 and time of the day is 10AM.

Following video demonstrates this.

 

Excel Filter: How to filter data according to dates

Filter Options

Filter Options

Today I will demonstrate Excel’s powerful capabilities to filter date columns. You have many options for selecting which time period to show or not to show.

In following video I will show how to select time period

  • by selecting start date and end date
  • by selecting “Last Year” from the menu
  • by selecting any year

How to highlight dates that are older than 70 days using Excel’s Conditional Formatting

Following video demonstrates how to use Excel’s conditional formatting to highlight dates that are older than 70 days. The formula takes into account that empty cells won’t be highlighted. Following formula in D1’s Conditional Formatting does the trick:

=AND(D1<>“”;DAYS360(D1;TODAY())>=70)

Copy the formula to all cells in column D.

Here is a video demonstrating this: