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()
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()
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()
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()
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()
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()
In this example we combine WEEKDAY with TODAY to return the dates this coming Friday and next Monday.
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.
EOMONTH()
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()
DATEDIF returns the difference between two dates in your specified format
NETWORKDAYS()
Calculates the number of ‘network’ (working) days between two dates, to the assumption that Monday to Friday is the working week.
HOUR() / MINUTE() / SECOND()
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()
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.
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”)
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.