Excel is one of the easiest applications to do calculations in once you understand some of the basic functions.
Starting with some of the basic formulas you can quickly build up a repertoire of clever math skills.
To begin with, select cell A1 in a new workbook, type 25/12 (or 12/25 if you're American!) and press enter.
This will enter the 25th December for the current year in the cell.
In cell A2 type today's date again using the format dd/mm (or mm/dd), and press enter.
In cell A3, enter the simple formula =A1-A2 and press enter.
Depending on the version of excel that you are using, the result of the formula is either a number, or a date from the year 1900.
It is also possible that you get an error, in which case I'm guessing that you are trying this out on a date between 25th and 31st of December! If you are, change cell A1 to next years Christmas day in the format dd/mm/yy or mm/dd/yy.
If your answer is a number, congratulations, you now know how many days there are until next Christmas! If your answer is a date from the year 1900, right-click cell A3 and select the option to Format cells...
select the Number tab and the Number Category and click OK.
An explanation of the formula: Formulas begin with the equal sign =.
Without the equal sign Excel would interpret the data entered as text.
A1-A2 is the contents of cell A1 minus the contents of cell A2.
Cell A1 contains the date for Christmas day.
Excel doesn't really understand dates at all so it actually converts the date into a number, and that number is the number of days after the 1st January 1900.
e.
g.
25 December 2010 is seen by Excel as 40,537.
i.
e.
40,537 days after the 1st January 1900.
(It's actually one less but you don't have to worry about that trivia!) This number is known as a serial number.
The date you entered into cell A2 is converted into a serial number by Excel also.
One number minus the other number is just a simple subtraction and the answer is displayed in cell A3.
If the answer displayed as a date from the year 1900, that was just Excel being 'helpful' and converting the number of days between the 2 dates entered back into a date format! Not really helpful at all which is why you then had to format the cell as a number.
If you got an error, you were trying to subtract a larger number from a smaller number which Excel then tried to convert back to a date, and Excel can't count backwards from the 1st January 1900! If you formatted the cell as a number, you would see -1 or -2, etc, as the answer to how many days since Christmas.
You can of course change the values in cells A1 and A2 to calculate how many days between any 2 dates that you choose.
As a tip - always enter dates in the format of dd/mm/yy (or mm/dd/yy), and then format the cell to a date style with a right-click / Format cells...
Number tab, Date category.
As a variation of the theme, instead of entering today's date enter the function =TODAY() which will display the current date when you open the file.
By using the TODAY function you now have a countdown to a certain date.
My final workbook, a countdown to my birthday in 2011: Cell A1: 03/02/2011 Cell A2: =TODAY() Cell A3: =A1-A2.
Starting with some of the basic formulas you can quickly build up a repertoire of clever math skills.
To begin with, select cell A1 in a new workbook, type 25/12 (or 12/25 if you're American!) and press enter.
This will enter the 25th December for the current year in the cell.
In cell A2 type today's date again using the format dd/mm (or mm/dd), and press enter.
In cell A3, enter the simple formula =A1-A2 and press enter.
Depending on the version of excel that you are using, the result of the formula is either a number, or a date from the year 1900.
It is also possible that you get an error, in which case I'm guessing that you are trying this out on a date between 25th and 31st of December! If you are, change cell A1 to next years Christmas day in the format dd/mm/yy or mm/dd/yy.
If your answer is a number, congratulations, you now know how many days there are until next Christmas! If your answer is a date from the year 1900, right-click cell A3 and select the option to Format cells...
select the Number tab and the Number Category and click OK.
An explanation of the formula: Formulas begin with the equal sign =.
Without the equal sign Excel would interpret the data entered as text.
A1-A2 is the contents of cell A1 minus the contents of cell A2.
Cell A1 contains the date for Christmas day.
Excel doesn't really understand dates at all so it actually converts the date into a number, and that number is the number of days after the 1st January 1900.
e.
g.
25 December 2010 is seen by Excel as 40,537.
i.
e.
40,537 days after the 1st January 1900.
(It's actually one less but you don't have to worry about that trivia!) This number is known as a serial number.
The date you entered into cell A2 is converted into a serial number by Excel also.
One number minus the other number is just a simple subtraction and the answer is displayed in cell A3.
If the answer displayed as a date from the year 1900, that was just Excel being 'helpful' and converting the number of days between the 2 dates entered back into a date format! Not really helpful at all which is why you then had to format the cell as a number.
If you got an error, you were trying to subtract a larger number from a smaller number which Excel then tried to convert back to a date, and Excel can't count backwards from the 1st January 1900! If you formatted the cell as a number, you would see -1 or -2, etc, as the answer to how many days since Christmas.
You can of course change the values in cells A1 and A2 to calculate how many days between any 2 dates that you choose.
As a tip - always enter dates in the format of dd/mm/yy (or mm/dd/yy), and then format the cell to a date style with a right-click / Format cells...
Number tab, Date category.
As a variation of the theme, instead of entering today's date enter the function =TODAY() which will display the current date when you open the file.
By using the TODAY function you now have a countdown to a certain date.
My final workbook, a countdown to my birthday in 2011: Cell A1: 03/02/2011 Cell A2: =TODAY() Cell A3: =A1-A2.
SHARE
