Top 7 Excel Date functions

May 6, 2020 | Excel Hints and Tips

Excel date functions can be used to manipulate dates in many different ways.  You may need to know how many days it is since you sent an invoice or analyse when your sales take place in the month.  You may need to summarise your data based on the month or calculate how many days people are working taking into account holidays.  Here are my top 7 date functions in Microsoft Excel.

TODAY

The TODAY function inserts todays date into Excel.  This updates automatically and is based on the system settings.  This is really useful if you need to calculated the number of days since a specific date for example, how many days overdue are my invoices.

Structure

=TODAY()

This function is one of the few that have nothing in the brackets.

NOW

The NOW function inserts todays date and the current time into Excel.  This updates automatically and is based on the system settings.  This isn’t used as much as the Today function but is more specific if your data is time dependent, not just date dependent.

Structure

=NOW()

This function also has nothing in the brackets.

DAY

The DAY function returns the day of the month as a number between 1 and 31.  This could be used to analyse whether sales are more common near the beginning or the end of the month.  In certain sectors, is there an increase after pay day.

Structure

=DAY(serial number)

The serial number would be a cell containing a date.

MONTH

The MONTH function returns the month as a number between 1 and 12 with 1 indicating January, 2 for February and so on.  It could be used work out the number of months between certain dates or summarise the data based on the month.  It could be used in conjunction with a VLOOKUP to convert the month number back to a month name if required.

Structure

=MONTH(serial number)

The serial number would be a cell containing a date.

YEAR

The YEAR function returns the year of a date as a number between 1900 to 9999.  It could be used to work out the number of years between 2 dates.

Structure

=YEAR(serial number)

The serial number would be a cell containing a date.

NETWORKDAYS

The NETWORKDAYS function returns the number of whole workdays between 2 dates.  The assumption is that Saturday and Sunday are non-working days and holidays can be taken into account if required.

Structure

=NETWORKDAYS(start date, end date,holidays)

Holidays is a list of cells containing the holiday dates.  It is optional and if left out it is assumed there are no holiday dates.

NETWORKDAYS.INTL

The NETWORKDAYS.INTL function returns the number of whole days between 2 dates with custom weekend parameters.

Structure

=NETWORKDAYS.INTL(start date, end date, weekend, holidays)

The weekend number can be a code as shown in the list below or by using the code of 0 for workdays and 1 for non-workdays starting on a Monday e.g. 0010010 would make Wednesday and Saturday non-working days.  This is optional with the default assuming that Saturday and Sunday are non-working days.

1 or omitted – Saturday, Sunday

2 – Sunday, Monday

3 – Monday, Tuesday

4 – Tuesday, Wednesday

5 – Wednesday, Thursday

6 – Thursday, Friday

7 – Friday, Saturday

11 – Sunday only

12 – Monday only

13 – Tuesday only

14 – Wednesday only

15 – Thursday only

16 – Friday only

17 – Saturday only

Holidays is a list of cells containing the holiday dates.  It is optional and if left out it is assumed there are no holiday dates.

There are plenty more date and time functions than just these.  The full list can be accessed from the Formulas tab and the Date & Time icon.  To find out what any of these do just hover over them for a screen tip.  If you click on the function then it takes you into the function wizard which guides you though using the function.  The help on this function link provides more online help if required.

What are you waiting for?!!

Want to learn more about Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.