Date and time functions in Excel – An overview

There are a wide number of date and time functions in Excel, here is a brief tutorial on some of the most common ones with examples of how you might use them. Covered in this post:

 

TODAY()

NOW()

DATE()

DATEVALUE()

EDATE()

WEEKDAY()

EOMONTH()

DATEDIF()

NETWORKDAYS()

HOUR()

MINUTE()

SECOND()

TIME()

 

TODAY()

 

DateFunctions2

 

This formula returns today’s date. Beware, if you are putting this into your worksheets, whenever someone opens it in the future it will return that date not the date you created/modified the file. If you want those you’ll have to use VB as no functions exist to return them.

 

NOW()

 

DateFunctions3

 

This returns the date and time as of right now. In this case formatted as HH:MM but you can use any date/time format combinations. Caution: too many formulas like TODAY and NOW in your spreadsheet will slow it down, as they have to keep re-calculating every time anything is changed. If you need to persist with lots of them and your file is performing slowly, consider switching the calculations to manual for a while when you are making changes.

 

DATE()

 

DateFunctions4

 

This is useful when you are compiling a date together from several different cells, the required arguments are firstly Year, then Month, then Day to return the date.

 

DATEVALUE()

 

DateFunctions5

 

This is for converting dates which are stored in text format e.g. after you’ve imported some data from elsewhere and Excel is incorrectly reading it as text. In this example 24/9/2008 is text and the formula converts it to a date giving the Result of 39715.

 

Excel stores all dates as numeric values, in this case the date is the 39,715th date since 01/01/1900 (when the world began, according to Excel). You can easily right-click, Format Cells and change this to display as 24/09/2008.

 

EDATE()

 

DateFunctions7

 

EDATE gets around the problem of months have different numbers of days, by simply adding or subtracting from a date by a defined number of months.

 

WEEKDAY()

 

DateFunctions8

 

In this example we combine WEEKDAY with TODAY to return the dates this coming Friday and next Monday.

 

DateFunctions9

 

When you use the standard serial number 1 the week is ordered from Sun to Sat, the below image indicates the other alternative serial numbers.

 

DateFunctions10

 

EOMONTH()

 

DateFunctions11

 

EOMONTH returns the last date of the current month, when arguments are set to zero. As you can see in the examples above, this can be manipulated to return other first/last days of months.

 

DATEDIF()

 

DateFunctions12

 

DATEDIF returns the difference between two dates in your specified format

 

NETWORKDAYS()

 

DateFunctions13

 

Calculates the number of ‘network’ (working) days between two dates, to the assumption that Monday to Friday is the working week.

 

HOUR() / MINUTE() / SECOND()

 

DateFunctions14

 

The formula in D3 above is =HOUR(C3-B3) + MINUTE(C3-B3) / 60 + SECOND(C3-B3)/36000. This provides the time difference in hours converted to a decimal i.e. 30 minutes would correctly be reported as 0.5 hours rather than showing as 0.3.

 

TIME()

 

DateFunctions15

 

The TIME function requires inputs for hours, minutes and then seconds. The above formula demonstrates how to add time from other cells to another time entry.

 

DateFunctions16

 

This example shows how a time format can be converted, in this case to show that 1,451 seconds comes to 24 minutes 11.

 

Creating time slots

 

The possibilities are endless when you start getting creative with a combination of the functions we’ve covered. For example, this formula will return the time slots of a time within 15 minute windows.

 

=TEXT(FLOOR(B3,TIME(,15,)),”HH:MM”) & “-” & TEXT(CEILING(B3+(MOD(MINUTE(B3),15)=0) * (TIME(,1,)),TIME(,15,)),”HH:MM”)

 

DateFunctions17

 

This can easily be adapted to 30 minute slots, 1 hour slots, etc.

 

Hope you found this article useful. Please leave us some feedback in the comments and let us know what other types of Excel formulas you’d like to read about.

Leave a Reply

Your email address will not be published. Required fields are marked *