Step-by-step tutorial on how to work with times and dates formulas in Excel

If you use Excel regularly, you will definitely see dates and times in your cells. The history of creating or updating data is usually recorded, so it is important to know how to work with this data.

In this article, we will teach you three basic skills:

  • How to format dates in Excel to your liking
  • Formulas for calculating the number of days, months and years between two dates
  • Excel date formula to enter today’s date and keyboard shortcut to add current time

Microsoft Excel can do basically anything with data, provided you are familiar with it. This article can be a big step towards improving your Excel skills. Let’s get started.

1- How to type the date

I recommend typing the dates in the same format as your system. For American readers, the format “year / month / day” will be appropriate. The European format is “year / day / month”.

When I type a date, I always type it in full: the month, the day, and the year. If I want to display only the month and year, I can easily format it (in less than a minute).

2- How to type time

Typing time in Excel is simple. We can record only one hour of the day, or the exact moment.

If you want to enter 4pm, type “4pm” in a cell in Excel and press Enter:

When we press Enter, Excel converts what we typed into something like this: seconds: minutes: hours.

Here is an example of a specific time on the spreadsheet:

The main thing is to use columns (two dots) to separate different parts of time, then type a space plus the terms AM or PM.

3- How to type date and time together

You can type a combination of date and time in Excel for very specific times.

Use what we learned earlier about typing date and time to type a combination of date and time.

Note that Excel converts time to a 24-hour format by default when time and date are used together. If you want to change the style of this date, read the rest of the article.

Bonus: Excel keyboard shortcut for the current time

One of my favorite keyboard shortcuts in Excel is to enter the current time on the spreadsheet. I often use this formula when I change my data. Try it:

Control + Shift +;

Date formatting in Excel

For this part of the article, use the “Formatting Dates & Times” tab in the sample exercise book.

What to do if your dates are European style? That is, they are year-month-day, and you want to convert them to year-day-month.

These cells contain the same information that is formatted in different ways.

In the image above, what may surprise you is that all six cells contain the same information – “1/22/2017”. What is different is how they are formatted in Excel. The original data is the same, but different formatting can be used to display it.

In most cases, it is best to use the formatting option to modify your date formatting  . You do not need to modify the data yourself – just change the way it is presented.

Excel cell formatting

To change how date and time information is displayed, you must use the Home tab in Excel. In the Ribbon (Excel top menu), find the Number section.

There is a small arrow in the lower right corner of that section. Click on it to open the Format Cells menu.

To format Excel cells that have different styles, use the Home tab and click on the drop-down arrow next to the word “Number” that you see in the image.

The Format Cells menu offers various options for formatting date and time. You can convert the template “1/22/2017” to “Sunday, January 22nd” by formatting. Then take the format painter and change the format of all your cells.

The Format Cells menu allows you to change your date and time styles without changing the original date.

Take a moment to review this menu and try different styles for your Excel dates and times.

Get information from the date and time

Suppose we have a list of data that has a specific date and time and we want to get simpler versions of those formulas. We may have a list of the exact date of the transaction, but we want to work on them at a higher level and categorize them by year or month.

With the following Excel formula you can get the year from a date:

= YEAR (CELL)

Use the following Excel formula to get the month from the date cell:

= MON (CELL)

Find the difference between date and time

For this section, use the “DATEDIF” tab in the sample exercise book.

In Excel, templates are used to change the presentation of date and time, while formulas are used to modify, calculate, or work with date and time.

The DATEDIF formula is used to calculate the difference between days. Put two dates in the formula to calculate the number of days, months and years between them. In the next section, we will examine how to use it.

1- Days between dates

This Excel date formula calculates the number of days between two dates:

= DATEDIF (A1, B1, ”d”)

The formula consists of two cells separated by commas and uses the letter “d” to calculate the difference between days.

The DATEDIF formula takes two history cells and calculates the days between them.

Here are some ideas for using the Excel date formula:

  • To begin, you can count down to your birthday, calculate the difference between today and your birthday.
  • Use DATEDIF to calculate the difference between two dates and divide your stock growth by the number of days to calculate your daily growth (or loss!).

2- Months between dates

DATEDIF also calculates the number of months between two dates. This date formula in Excel is very similar to the previous formula, but to calculate the difference between the months, the letter m is replaced by d:

= DATEDIF (A1, B1, ”m”)

However, there is a contradiction in how DATEDIF is applied in Excel: Excel calculates the full months between two dates. Look at the picture below.

In my opinion, there are three months between January 1st and March 31st (January, February, and almost the entire month of March), but since Excel calculates full months, it only counts January and February as full months. . So the result is “2”.

Here is my favorite way to calculate the number of months between two dates. First we find the date difference between the days, then we divide it by the average number of days in a month – 42.30%.

= (DATEDIF (A1, B1, ”d”) / 30.42)

Let’s apply our modified DATEDIF here on two dates:

It got much better. 2.99 is much closer to a full 3 months, and this will be very useful in later formulas.

Official Excel documents use a sophisticated method of calculating months between dates, but it will be a simple and easy way to do this. Writing a good formula in Excel requires precision and simplicity, and this formula has both options.

3- Years between dates

Finally, we calculate the number of years between the two dates. The official method of calculating the years between the two dates is to use the following formula.

= DATEDIF (A1, B1, ”y”)

Note that this formula is similar to the DATEDIF formulas above, but in the last part we replaced the y formula to calculate the number of years between two dates. Let’s look at it in practice:

Note that this formula works like DATEDIF for months: meaning it only takes full years. I prefer to calculate incomplete years as well. Here is a better DATEDIF formula for years:

= (DATEDIF (A1, B2, ”d”) / 365)

In principle, we just get the difference between two dates in terms of days, then divide it by 365 to calculate the result in terms of years:

DATEDIF is very powerful, but be careful how it works: it only calculates full months or years by default. Use my revised versions to increase the accuracy of the results.

Bonus: Business days between dates

The above Excel date formulas focus on the number of days between dates. However, sometimes it is necessary to calculate the number of working days between two dates.

In this case, NETWORKDAYS = should be used to calculate the number of working days between two dates.

= NETWORKDAYS (A1, B1)

The following image shows an example of using NETWORKDAYS. By looking at the calendar, you can see how the formula is calculated.

Date and time are very pervasive on spreadsheets. Excel date formulas and formatting options are useful. The techniques in this article can take your Excel skills to the next level so that you can integrate date-based data seamlessly into your spreadsheets.

Leave a Reply

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