Date and Time Formulas and Functions in Excel (part 1)

Want to display todays date or add dates, then this is for you. Want to change the date format. To change a date in a different format then go to this page (it will explain you how to write for example, 12/3/2005 or 12 March 2005 or March 12 2005, etc....The choices are infinite).

Look at part 2 for Time and hours formulas here.

Part 3 deals with weeks, weekdays and years.

Date and Time functions help you deal with dates and durations, and birthdays and days until birthday, etc….. Beware it works only from 1 January 1900….. if you want to deal with the real past, then special tricks and add in are needed.

TODAY, DAY, MONTH, YEAR Functions

Display TODAY's date:

Just type in the cell   =TODAY()

Watch out how you can do =TODAY()+1 for exemple.....

TIP: the difference between TODAY and the function NOW(), is that NOW gives you the exact time, where TODAY only gives you the day.

today in excel

 

A trick to make long table with a lot of thin columns with days and month and year...

Have you ever tried to get a full date into a thin column? What happens is usually that you have to use a small font and then you can not read it, or write vertically or you get the famous ####### sign.

So by using the functions DAY(cell), MONTH(cell) and YEAR(cell) you can have a nice table.

thin table

Like this:

So here in B2 you will have DAY(B1)

in B3 you will have MONTH(B1)

and B4 you will have YEAR(B1)

thin table 

Of course you can do it also by having B2, B3, B4 equal to =B1 and the changing the FORMAT of the cell like shown in our Format Page.

Both ways are fine.

You can Download this here

 

DATE, DATEVALUE, DAY360  functions

Date allows you to create a date based on 3 numbers

Use it as =DATE(Year cell, Month cell, Day cell)

date

 

DATEVALUE is creating a number (an integer) from a date in text format. This integer starts at 1 on the 1st of January 1900 and counts until

DATEVALUE("1/1/1905")     =  1948  (integer format)  or you can just set the cell in date format 1/1/1905

DATEVALUE("1/1/1900")     =  1

DATEVALUE("12/5/2015")     =  42136

Why would you use such a function? Good question. For example if you have a text field, then you can directly convert it to date.

 

DAYS360(first date, second date) function

Day360 is a function that give you the difference between 2 dates based on a 360 days year.

Why would you use it? That is a specialized function used in the financial sector to simplify calculations for bonds. So use it wisely.

days360 

Here you will find listed all the Date and Time functions so that you can have a good overview. In Bold are the ones most used and useful.

All the Date and Time functions

DATE: The serial number of a particular date/p>

DATEVALUE: Converts a date in the form of text to a serial number

DAY: Converts a serial number to a day of the month

DAYS360: Calculates the number of days between two dates based on a 360-day year/p>

EDATE: The serial number of the date that is the indicated number of months before or after the start date

EOMONTH: The serial number of the last day of the month before or after a specified number of months

HOUR: Converts a serial number to an hour

MINUTE: Converts a serial number to a minute

MONTH: Converts a serial number to a month

NETWORKDAYS: The number of whole workdays between two dates

NOW: The serial number of the current date and time

SECOND: Converts a serial number to a second

TIME: The serial number of a particular time

TIMEVALUE: Converts a time in the form of text to a serial number

TODAY: The serial number of today's date

WEEKDAY: Converts a serial number to a day of the weeks

WEEKNUM: Converts a serial number to a number representing where the week falls numerically with a year

WORKDAY: The serial number of the date before or after a specified number of workdays

YEAR: Converts a serial number to a year

YEARFRAC: The year fraction representing the number of whole days between start_date and end_date

 

So that's it for the Date and Time Formulas and Functions in Excel. 

 Now, let's use them....