Excel shortcuts

15 Date Shortcuts in Excel That Will Save You Time

If you work with dates in Excel, you know that they can be a pain. You have to remember the order of the month, the day, and the year. You have to use a special format to get Excel to recognize a date. And if you want to do anything more than just look at the date, you have to use formulas.But it doesn't have to be this way. There are a few shortcuts that can make working with dates in Excel a lot easier. Here are 15 of them.

1. Use the DATE Function

The DATE function is the easiest way to create a date in Excel. You just need to enter the year, month, and day in that order. For example, if you wanted to enter the date January 1, 2020, you would use this formula:

=DATE(2020,1,1)

You can also use the DATE function to enter the current date. To do this, just leave the cell blank and enter this formula:

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

This will insert the current date into the cell, and it will update automatically every day.

2. Use the TEXT Function

The TEXT function is a great way to format dates the way you want them. For example, if you want to display the date as "January 1, 2020", you can use this formula:

=TEXT(DATE(2020,1,1),"mmmm d, yyyy")

You can use the TEXT function to format dates any way you want. You can even use it to display the day of the week or the month as a word. For a full list of the options available, see the Microsoft support page.

3. Use the DAY, MONTH, and YEAR Functions

If you want to extract just the day, month, or year from a date, you can use the DAY, MONTH, and YEAR functions. For example, if you have a date in cell A1, you can get the day with this formula:

=DAY(A1)

You can get the month with this formula:

=MONTH(A1)

And you can get the year with this formula:

=YEAR(A1)

You can also use these functions to get the day of the week or the week of the year. For a full list of the options available, see the Microsoft support page.

4. Use the WEEKDAY Function

If you want to know what day of the week a date is, you can use the WEEKDAY function. For example, if you have a date in cell A1, you can use this formula to get the day of the week:

=WEEKDAY(A1)

This will return a number from 1 to 7, with 1 being Sunday and 7 being Saturday. If you want the day of the week to be displayed as a word, you can use this formula:

=TEXT(WEEKDAY(A1),"dddd")

5. Use the EOMONTH Function

If you want to know the last day of the month for a given date, you can use the EOMONTH function. For example, if you have a date in cell A1, you can use this formula to get the last day of the month:

=EOMONTH(A1,0)

You can also use this function to get the last day of the month for the current date. To do this, just leave the cell blank and enter this formula:

=EOMONTH(TODAY(),0)

6. Use the NETWORKDAYS Function

If you want to know how many weekdays are between two dates, you can use the NETWORKDAYS function. For example, if you have a start date in cell A1 and an end date in cell B1, you can use this formula:

=NETWORKDAYS(A1,B1)

This will exclude weekends and any holidays that you have listed in a separate range of cells. You can also use this function to get the number of weekdays between the current date and a future date. To do this, just leave the cell blank and enter this formula:

=NETWORKDAYS(TODAY(),B1)

7. Use the WORKDAY Function

If you want to know what the date will be a certain number of weekdays from today, you can use the WORKDAY function. For example, if you want to know what the date will be 10 weekdays from today, you can use this formula:

=WORKDAY(TODAY(),10)

You can also use this function to add a certain number of weekdays to a given date. For example, if you have a date in cell A1 and you want to add 10 weekdays, you can use this formula:

=WORKDAY(A1,10)

8. Use the DATEDIF Function

If you want to know the number of days, months, or years between two dates, you can use the DATEDIF function. For example, if you have a start date in cell A1 and an end date in cell B1, you can use this formula to get the number of days between the two dates:

=DATEDIF(A1,B1,"d")

You can also use this function to get the number of months or years between the two dates. To get the number of months, use "m" as the third argument. To get the number of years, use "y" as the third argument.

9. Use the DAYS Function

If you want to know the number of days between two dates, you can use the DAYS function. For example, if you have a start date in cell A1 and an end date in cell B1, you can use this formula:

=DAYS(A1,B1)

You can also use this function to get the number of days between the current date and a future date. To do this, just leave the cell blank and enter this formula:

=DAYS(TODAY(),B1)

10. Use the DAYS360 Function

If you want to know the number of days between two dates using the 360-day year, you can use the DAYS360 function. For example, if you have a start date in cell A1 and an end date in cell B1, you can use this formula:

=DAYS360(A1,B1)

You can also use this function to get the number of days between the current date and a future date. To do this, just leave the cell blank and enter this formula:

=DAYS360(TODAY(),B1)

11. Use the EDATE Function

If you want to know what the date will be a certain number of months from a given date, you can use the EDATE function. For example, if you have a date in cell A1 and you want to know what the date will be two months from that date, you can use this formula:

=EDATE(A1,2)

You can also use this function to get the date a certain number of months before a given date. To do this, you just need to use a negative number as the second argument. For example, if you want to know what the date was two months before the date in cell A1, you would use this formula:

=EDATE(A1,-2)

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.