Last week during one of my Excel courses, I was talking about dates, Someone asked if Excel could calculate “how many sleeps until Christmas”.

In response to this, I’ve created a “how many sleeps until Christmas” calculator. There’s two versions…

• Excel version (XLS)
• Apple Numbers version – for all my Mac and iPad user friends

To make this example more interesting, I’ve added an image (you can download it from here) and used a fancy font in A29, though the downloadable versions use a plain font for compatibility purposes.

The great thing about this spreadsheet is that it can be used every year without having to change anything.

The notes below refer to Excel, however they are almost the same for Numbers.

I’ve turned off the gridlines to make it easier to read. The calculations are stored in cells under the image (so they are hidden, out of the way).

F4 contains the function =TODAY() which stores the current date in the cell. This is picked up from your computer’s internal clock and therefore is automatically updated.

F5 contains a formula: =DATEVALUE(“25/12/”&YEAR(F4)). This joins the text 25/12/ to the current year (allowing us to re-use the spreadsheet next year without manually changing the date). DATEVALUE converts a date that has been entered/calculated as a text string into something that Excel can recognise as a date.

F6 contains a formula that subtracts Christmas Day’s date from the current date and returns the number of days: =IF(F5-F4<0,DATEVALUE(“25/12/”&YEAR(F4)+1)-F4,F5-F4)

OK, that looks to be a complicated formula considering what I just said!

Originally the formula was F5-F4, however, during my testing (which I did by by overtyping the current date in F4 with various dates), I found that if F4 contained a date between 26th – 31st December, F6 contained a negative value.

So now the formula in F6 checks to see whether F5-F4 results in a negative number. If it does, it calculates Christmas Day NEXT YEAR and then subtracts the current day from it.

A29 contains the following formula:

=IF(F6=0,”Merry Christmas!!”,IF(F6=1,”1 Sleep”,F6 & ” Sleeps”) &” Until Christmas!”)

If F6 is 0 then it is Christmas Day, so the message is A29 is Merry Christmas!

If F6 does not equal 0, the message in A29 indicates how many sleeps there are until Christmas – the nested IF is needed to get the correct grammar (sleep v sleeps).

Merry Christmas Everyone!