Tag Archives: date

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.

XL FORMULA FOCUS: Find end of probation period using EDATE

So you’ve got a new employee, and you want to know when their probation period is over? The EDATE function is really straight forward.

=EDATE(Start_date,Months)

So type in “=EDATE(“, click on the cell that contains their start date, type a comma, then enter the number of months. 3 months? 6 months? Type a 3 or a 6 or whatever it is. Then close your brackets “)” and press enter.

Done.

RELATED: If you need to calculate length of service, you may want to look here.

Excel Keyboard Shortcuts – Control with Symbols

Another selection of keyboard shortcuts. Press Control and the following symbols:

 

` – Toggles between displaying cell values and formulas in the current worksheet.

~ – Cell format is General.

! – Cell format is Number with two decimal places, commas, and minus symbol for negative values.

@ – Cell format is Time hour and minute, and AM or PM.

# – Cell format is Date (dd-mmm-yy).

$ – Cell format is Currency (with 2 decimal places and negatives in brackets).

% – Cell format is Percentage (with no decimal places).

^ – Cell format is Exponential with two decimal places.

& – Adds outline border on selected cells.

* – Selects the range of cells around the active cell.

( – Unhides all hidden rows in the selection.

) – Unhides all hidden columns in the selection.

– – Delete the selected cells.

_ – Removes outline border on selected cells.

+ – Insert cells.

; – Enters the current date.

: – Enters the current time.

‘ – Copies a formula from the cell above.

” – Copies the value from the cell above.

XL FORMULA FOCUS: Need to calculate length of service or age? Use YEARFRAC

Length of service is used all the time in HR – for example long service awards or calculating redundancy payments. Those of you fortunate enough to have a solid HR system in place will hopefully have some automation to keep you aware of upcoming key dates, but for others Excel can be the next best thing.

We will be using the YEARFRAC function:

YEARFRAC(start_date, end_date, [basis])

 

Let’s do an example…

1. In cell A1, enter your date of birth (for arguments sake use the format dd/mm/yyyy).

2. In cell B1, enter  =TODAY()  . This will return today’s date, which will update automatically as time goes on.

3. In cell C1, enter  =YEARFRAC(A1,B1,1)  .

 

The number one at the end of the formula is what is referred to above as the ‘basis’. It tells Excel on what basis to calculate the difference between start_date and end_date. Personally I’ve always used 1, which is described as Actual, and it has always been accurate and fit for purpose. If you’re unsure, while working on the YEARFRAC formula hit the fx button next to the formula bar, and then select ‘help on this function’. It will give you a bit more information about the YEARFRAC function and detail the various basis options.

 

So, cell C1 should now show you the number of years between your start date and end date (in this case how old you are today). Depending on how you format this cell (and assuming its not your birthday – if it is happy birthday!) you will probably have a number of decimal places shown. This is fine if you want a very precise figure, but in many practical applications you will want completed years (e.g. I am 30 years old, not 30.153663 years old). So for this we’re going to use the INT function (short for Integer).

 

4. In cell D1, enter INT(C1)  .

 

You will now have your age shown as an integer.

If you wanted to you could condense this into fewer cells.

 

5. Select cells B1, C1 and D1 and press delete.

6. In cell B1, enter this:

=INT(YEARFRAC(A1,TODAY(),1))

You can now use this formula repeatedly, changing the contents of cell A1 to any date that you want.

If you’ve made it this far Well Done! You’ve used 3 different functions in one formula to give a practical output.

If you liked that, here’s a post about EDATE, a really straightforward date function that I use to calculate the of probation periods or notice periods.