Excel shortcuts

20 Excel date shortcuts that will save you time

If you work with dates in Excel, you know how frustrating it can be to enter them in manually. You have to remember the format (month/day/year or day/month/year), and if you make a mistake, it can be hard to spot. Fortunately, there are a few shortcuts that can make working with dates a lot easier. Here are 20 of our favorites:

1. Use slashes or dashes

When you enter a date in Excel, you can use either slashes (/) or dashes (-) as separators. For example, 3/14/2015 and 14-3-2015 both refer to March 14, 2015. This can be helpful if you're working with data from different sources that use different date formats.

2. Use two-digit years

If you enter a date with a two-digit year (like 3/14/15), Excel will interpret it as a date in the current century. So, 3/14/15 is interpreted as March 14, 2015. But 3/14/99 is interpreted as March 14, 2099. You can avoid this by always using four-digit years (like 3/14/2015).

3. Use leading zeros

If you enter a date like 3/4/2015, Excel will interpret it as March 4, 2015. But if you enter a date like 03/04/2015, Excel will interpret it as April 3, 2015. To avoid this, you can use leading zeros (like 03/04/2015).

4. Use the DATE function

If you need to enter a date that's not in the standard format (month/day/year or day/month/year), you can use the DATE function. For example, the formula =DATE(2015,3,14) will enter the date March 14, 2015, regardless of the current date format. You can also use the DATE function to enter a date in a specific format. For example, the formula =DATE(2015,3,14) will enter the date March 14, 2015, regardless of the current date format.

5. Use the YEAR function

If you need to enter the current year in a cell, you can use the YEAR function. For example, the formula =YEAR(TODAY()) will enter the current year. You can also use the YEAR function to extract the year from a date. For example, the formula =YEAR("3/14/2015") will return 2015.

6. Use the MONTH function

If you need to enter the current month in a cell, you can use the MONTH function. For example, the formula =MONTH(TODAY()) will enter the current month. You can also use the MONTH function to extract the month from a date. For example, the formula =MONTH("3/14/2015") will return 3.

7. Use the DAY function

If you need to enter the current day of the month in a cell, you can use the DAY function. For example, the formula =DAY(TODAY()) will enter the current day. You can also use the DAY function to extract the day from a date. For example, the formula =DAY("3/14/2015") will return 14.

8. Use the WEEKDAY function

If you need to enter the current day of the week in a cell, you can use the WEEKDAY function. For example, the formula =WEEKDAY(TODAY()) will enter the current day. You can also use the WEEKDAY function to extract the day of the week from a date. For example, the formula =WEEKDAY("3/14/2015") will return 6 (Saturday).

9. Use the NOW function

If you need to enter the current date and time in a cell, you can use the NOW function. For example, the formula =NOW() will enter the current date and time. You can also use the NOW function to enter the current date or time in a specific format. For example, the formula =NOW("mm/dd/yyyy") will enter the current date in the format month/day/year.

10. Use the TODAY function

If you need to enter the current date in a cell, you can use the TODAY function. For example, the formula =TODAY() will enter the current date. You can also use the TODAY function to enter the current date in a specific format. For example, the formula =TODAY("mm/dd/yyyy") will enter the current date in the format month/day/year.

11. Use the TIME function

If you need to enter the current time in a cell, you can use the TIME function. For example, the formula =TIME() will enter the current time. You can also use the TIME function to enter the current time in a specific format. For example, the formula =TIME("hh:mm:ss") will enter the current time in the format hour:minute:second.

12. Use the HOUR function

If you need to enter the current hour in a cell, you can use the HOUR function. For example, the formula =HOUR(NOW()) will enter the current hour. You can also use the HOUR function to extract the hour from a time. For example, the formula =HOUR("3:14:15") will return 3.

13. Use the MINUTE function

If you need to enter the current minute in a cell, you can use the MINUTE function. For example, the formula =MINUTE(NOW()) will enter the current minute. You can also use the MINUTE function to extract the minute from a time. For example, the formula =MINUTE("3:14:15") will return 14.

14. Use the SECOND function

If you need to enter the current second in a cell, you can use the SECOND function. For example, the formula =SECOND(NOW()) will enter the current second. You can also use the SECOND function to extract the second from a time. For example, the formula =SECOND("3:14:15") will return 15.

15. Use the DATEVALUE function

If you need to convert a date from text to a real date, you can use the DATEVALUE function. For example, the formula =DATEVALUE("3/14/2015") will return the date 3/14/2015. You can also use the DATEVALUE function to convert a date from one format to another. For example, the formula =DATEVALUE("14-3-2015") will return the date 3/14/2015.

16. Use the TIMEVALUE function

If you need to convert a time from text to a real time, you can use the TIMEVALUE function. For example, the formula =TIMEVALUE("3:14:15") will return the time 3:14:15. You can also use the TIMEVALUE function to convert a time from one format to another. For example, the formula =TIMEVALUE("3:14:15 PM") will return the time 3:14:15.

17. Use the NOW function

If you need to insert the current date and time in a cell, you can use the NOW function. For example, the formula =NOW() will insert the current date and time. You can also use the NOW function to insert the current date or time in a specific format. For example, the formula =NOW("mm/dd/yyyy") will insert the current date in the format month/day/year.

18. Use the TODAY function

If you need to insert the current date in a cell, you can use the TODAY function. For example, the formula =TODAY() will insert the current date. You can also use the TODAY function to insert the current date in a specific format. For example, the formula =TODAY("mm/dd/yyyy") will insert the current date in the format month/day/year.

19. Use the TIME function

If you need to insert the current time in a cell, you can use the TIME function. For example, the formula =TIME() will

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.