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.

Difference between TODAY() and NOW() functions

Excel has two similar time functions TODAY() and NOW(). These functions are not the same. Difference is that TODAY() function returns only current date (time value is 12AM or 0:00) and NOW()-function returns current date and current time.

Following video demonstrates this:

How to use Excel's cell comments

You can add descriptive comments to Excel cells. Comment doesn't have any influence to content of the cell. Each comment can be always visible or visible only when mouse pointer is on cell. You can drag always visible comments to any location on the worksheet. Comment boxes are also resizable.

Following video demonstrates this:


Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

Excel VBA macro: How to implement running clock

Following video demonstrates how to implement simple VBA macro showing running clock in Excel worksheet. My solution has three macros:

• RunClock: Sets cell (A1) value to current time by using Now()-function. Then it recursively calls itself in every one second until global variable clockOn = FALSE. 
• StartClock: Sets global variable clockOn = TRUE and calls RunClock
• StopClock: Sets global variable clockOn = FALSE

Recursive call is made using Excel's function

Application.Ontime(EarliestTime, Procedure, LatestTime, Schedule) 

In this case only first two parameters are needed:

• EarliestTime: Time when Procedure is been run. In this case Now + TimeValue("00:00:01")
• Procedure: The name of the procedure. In this case RunClock itself 

If you want to create buttons for starting and stopping a clock, please check how to add button

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Excel COUNT functions

Excel has several functions for counting the number of cells that meet specific criteria:

• COUNT(value1, value2,...): Counts number of cells (or arguments) containing numbers
• COUNTA(value1, value2,...): Counts number of non empty cells (or arguments)
• COUNTIF(range, criteria): Counts number of cells meeting the criteria
• COUNTIFS(range1, criteria1, range2,criteria2,...): Counts number of cells meeting all given criteria
• COUNTBLANK(range): Counts number of empty cells
• There are also functions DCOUNT and DCOUNTA, but I will handle those later

Following video demonstrates the usage of COUNTX-functions:

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Remove Duplicates -feature

Very typical task with Excel is to clean tables by removing duplicates. Fortunately Excel has handy tool to do it automatically. There is the feature Remove Duplicates in Data tab of Ribbon bar.

Check the video below.


Tip: If you have difficulties to see the video, try to maximise it from lower right corner: