Number formats, date and time in Excel

Number formats allow you to preserve the original cell value while displaying it in another format such as percentage, currency, date or time format.

Calculating with dates is on of the most needed functionalities on Excel but not very well understood. Once you understand Excel’s calendar logic then it’s easy to do basic math with date and time. In the video tutorial you will also see some date and time functions such as MONTH(), MINUTE() and EOMONTH().

Exercises

Calculate how old you are in days, months and hours. Hint: NOW() and DATE() functions might be helpful.

If you are interested you can check out the famous date bug in Excel from Wikipedia.

See the anwser

Let’s suppose you have born 25th of April 1990. Here are examples yout age in different units.

Days

Simply count the difference between dates and format as number. You could replace NOW() function with TODAY() which gives you the current date without a time.

=NOW()-DATE(1990;4;25)

Months

There are not very neat options to calculate or format months but this would be the simplest method.

=(NOW()-DATE(1990;4;25))/30

Hours

Count the difference between dates and format as continuous time.

=NOW()-DATE(1990;4;25)

Lecture materials

Lecture 5 – Number formats, dates and time

Mikael Ahonen

Lecture 5 – Number formats, dates and time in Excel