Tag Archives: day

XL FORMULA FOCUS: TODAY, YEAR, MONTH, DAY, DATE (an introduction to Excel date functions)

Excel Date Functions - TODAY, YEAR, MONTH, DAY & DATE
Excel Date Functions - TODAY, YEAR, MONTH, DAY & DATE

 This post will teach you five Excel functions to help you deal with dates. We will work our way through in the order that they are shown in the picture above.

[showmyads]

TODAY

=TODAY(). That’s it. That’s all you have to type, the brackets are empty. And it gives you today’s date, updating accordingly over time (whenever the worksheet recalculates). This makes it great for keeping things like age or length of service up to date.

YEAR, MONTH, DAY

You just point these at a cell containing a date (in the picture we use the date returned by the TODAY function). These functions then return the applicable data about that date. For example, if your date in C1 is in 2013 and you put =YEAR(C1) it will return 2013. If C1 contains a January date, =MONTH(C1) will return 1 (ie the first month of the year). And if you enter =DAY(C1) where the date is the first of a month, it returns 1 (note that this refers to the day of the months rather than the day in a week (which you can use WEEKDAY for).

[showmyads]

DATE

So DATE works kind of like the opposite of YEAR, MONTH and DAY:
=DATE(year,month,day)
If you tried this =DATE(2013,1,1), it would give you 01/01/2012 (or 41275, the Excel serial number that represents that date). So you can see the example in the picture above goes full circle – it extracts the day, month and year from the TODAY function in cell C1, and then compiles them together again to form a date again using the DATE function.

Just for Fun… Countdown

Last week I wrote a London 2012 Olympics-themed post showing how to use Excel to flip a coin. Keeping in the Olympic spirit I thought I’d share a spreadsheet that helps you count down the number of days, hours, minutes and seconds between now and the opening ceremony.

[showmyads]

It uses a simple macro to calculate each second, and you can adapt it to any deadline date and time, and to show either just workdays or all days. Beyond that I’ll let you explore the tool yourself and understand how it works.

When you open it you’ll need to enable macros, and then click the Countdown button on the bottom right of the Display tab.

Enjoy!