How to Subtract a Date From Today in Excel

Even though Microsoft Excel spreadsheet software was created for numerical calculations, we often have dates within our data, and we need to calculate elapsed days, weeks, months, years, or sometimes just working days.

Fortunately, Excel has you covered pretty well.

All of these operations are easily possible, and we can calculate anything related to dates – be in working days, months, or years, using a plethora of Excel functions and a number of ways.

How Are Dates Stored in Microsoft Excel

When you type a date in Excel, it appears as a Date. In the format section, you will even see it as a Date Format. But strangely, Microsoft Excel has no special way of determining dates as a Date data type. So a Date value is stored as a number in Excel. This trend was started by Lotus 1-2-3, the spreadsheet that predates Microsoft Excel.

So when you type a date in Excel, it is converted to a number – a serial number to be exact. This serial number starts from 1-March-1900. This first date was numbered 1, and then subsequent dates were calculated by adding days to this number.

If you don’t believe me, simply type a date and then change its type from Date to General. You’ll see a large number appear instead of the date. Something like 44762. The serial number you get might be higher or lower than this number, depending on the date you have keyed in.

While you may be puzzled by the fact that dates are being stored as serial numbers in Excel, it is in fact a boon for us.

Now we can perform arithmetic with the dates, as they are not really “dates” but rather “numbers”.

Do take note that based on your regional settings, you might see the date pre-formatted as DD/MM/YY or MM/DD/YY.

Further, you might see a two-digit year or a four-digit year. To tweak the way dates appear, you can change the format by picking up a suitable Custom Format.

How To Find Today’s Date

You can find the current date by using the Excel function TODAY. This is an empty function and does not take any arguments. Simply write =TODAY(), and you will get today’s date.

This makes it easy to make other dates, like tomorrow and yesterday, with the help of the today function.

Today =TODAY()

Tomorrow =TODAY() + 1

Yesterday = TODAY() – 1

Similarly, you can add or subtract any number of days from a starting date by using this method. Do try it out on any given date.

If you want to see the date with time, you can use the equivalent time function of Excel – NOW()

When you use NOW(), you get the date, along with the current time. Both the TODAY() & NOW() are dynamic functions, and every time any other function is evaluated in Excel, the Today & Now functions would get updated too to the latest current date and time.

How to Subtract a Date from Today

Instead of making future or past dates, you might already have a date, like the date of birth, and now you want to find the age of a person.

This can be achieved by subtracting the second date from the first date. So in our case, we will subtract the date of birth from Today’s date.

As an example, if the cell A2 contains the date of birth and the cell B2 contains Today’s date, then we can compute the difference between the two dates in cell C2 with the following formula:

=B2 – A2

You can try this on a blank Excel sheet.

The above formula will result in a number being shown in cell C2. The number obtained is the number of days difference between the two dates. Keep in mind that whenever you use this formula, you may receive a positive value or a negative value, depending on how you subtract dates.

If you subtract the earlier date from the current date, you will get a positive number, and when subtracting the current date from a previous date will give you a negative number.

How to Find the Age of a person in Excel

Calculating the number of days elapsed between 2 dates and getting a number is good, but if you want to calculate the Age of a person, having the difference in days isn’t much useful. We now need to convert the elapsed days into number of years, the number of months and the number of Days.

Calculating these three things manually can require a lot of calculations, using the Day functions of Excel. We can use many different ways to calculate the difference in Excel dates.

There’s an absolute gem of a function called the DATEDIF function in Excel, which is actually meant for subtracting dates, and find the difference between dates in elapsed days. It can tell you the difference in Years, Months & Days, depending on the third argument provided within the function.

I generally prefer this easy way to find the difference in days between specific dates. Datedif takes the following arguments.

DATEDIF(start_date, end_date, unit)

In this function, the first argument is the start date. The second argument is the end date or the due date. The unit can be anything from the list below.

“Y” returns the number of complete years in the period.

“M” returns the number of complete months in the period.

“D” returns the number of days in the period.

“MD” returns the difference between the days in start_date and end_date. The months and years of the dates are ignored.

“YM” returns the difference between the months in start_date and end_date. The days and years of the dates are ignored.

“YD” returns the difference between the days of start_date and end_date. The years of the dates are ignored. Only the difference between the dates without the years, is calculated.

So to calculate the complete years between any 2 selected dates, we can use the DATEDIF function in the third blank cell. The date_of_birth cell references the start date.

The todays_date cell references the cell where we have written =TODAY() to get Today’s date.

=DATEDIF(date_of_Birth, todays_date, “Y”)

To calculate the complete months after the years, we can use:

=DATEDIF(date_of_Birth, todays_date, “YM”)

And to calculate the complete days between 2 dates ignoring months and years, we can use:

=DATEDIF(date_of_Birth, todays_date, “MD”)

And to calculate the exact age, we can then combine the 3 functions, using the CONCATENATE Method, or using the & operator.

In the simple formula below, we calculate the complete age of a person born on 6-December-1966, and Today’s date being 30-July-2022.

You can copy and paste this formula and then hit the Enter key.

=DATEDIF(date_of_birth, todays_date, “Y”) & ” Years, ” & DATEDIF(date_of_birth, todays_date, “YM”) & ” Months and ” & DATEDIF(date_of_birth, todays_date, “MD”) & ” days”

For the formula result, you will see

How to Separate Day, Month & Year from any Date

There are several Date & Time Functions in Excel, that can be very clinical and separate out some components of the date, like the days, months or years, as desired.

To get the Day, we use the Day Function:

=DAY( TODAY() ) gives the date like 30

=MONTH( TODAY() ) gives the month as a number from 1 to 12.

As an example, the month function can be helpful in finding out the month of the invoice date. If you have multiple dates in a table, you can simply pull the fill handle and get Excel to calculate the months of all dates.

=YEAR( TODAY() ) gives the year of the year, like 1966 or 2022. Future values of the Year function will yield 2023, 2024 etc.

Calculating Working Days Between 2 Dates

The DateDif function calculates the number of days between two days. While it is suitable for age, sometimes you want to calculate working days, excluding the weekends.

So you may want to exclude Saturday or Sunday or both, or any other weekday from the calculation and work with only business days as the total number of days elapsed.

In this case, you can use the NETWORKDAYS() function of Microsoft Excel.

Here’s the step-by-step detailed article on how to calculate the difference between two days without the weekends.

Find Age in Years From the Date of Birth in Excel

Top 10 Time Saving Tips in Using Microsoft Excel

If you are a data warrior, chances are that you use Excel extensively in your day to day work. Learn to Analyze Data Quickly With Excel.

And there are many ways to boost productivity within Excel. Use these nifty tricks and improve productivity.

1. View All Formulas in Excel With a Single Click: I wrote about this trick some time back. It allows you to view all the formula with a single click of Control + ` Key. This acts like a toggle key. This can save a lot of time in finding where are the formulas to edit/view them.

2. Clean up Your Data  – Identify the Duplicates, and Remove Duplicates: There is no point in having duplicates in your tables. There are multiple ways to do this. You can identify the duplicates, and then choose to remove them manually,  or you can request Excel to remove the duplicate rows completely, automatically.

3. Colour Alternate Rows and Make it Easier to Read Large Data Sets: Prior to Excel 2007, we used to write formulas to colour alternate rows in Excel. This makes it easier to read the data.

=MOD(ROW(),2)=0

This function would tell us if the row is an even row or an odd row, and then we could colour it.

But since Excel 2007, we can do this in multiple ways. We can convert the data into a table. And in the table options, you can then colour alternate rows or columns as bands.

And then you can also use the formula above, and use conditional formatting.

4. Learn The In-Built Functions in Excel: Many people are amazed at the plethora of in-built functions available in Excel. But you must know that they exist, and also know the syntax to make a good use of them.

5. Learn and Use Pivot Tables Effectively: Pivot tables summarize data quickly. Learn to use them effectively.  Simple techniques like displaying both values and percentages in a Pivot Table adds extra value.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Similarly, learn to show different grouping for different pivot tables, even though it is based on the same data source. Also, creating a calculated field in a pivot table adds extra value.

If you are new to pivot tables, this introduction on how to analyze data using Excel pivots will be useful.

6. Charting Techniques Can Make People Notice Your Work: Creating nifty charts like the 2 Axis chart, or the PivotChart can be useful in the board room. Learn these techniques. Similarly, using Slicers in Pivot Charts can make dynamic Charts that can wow the audience. They are simple to use.

7. Get Back to Your Selection: Many times you will select or highlight some cells, but then move about the Excel file to find something. Then your are lost. Now you don’t remember where exactly is your selection. Simply click Control + Backspace. It will take you to the highlighted area in a blink!

8. Use Range Names to Refer to Cells: Instead of using G3, C3 or F3, you can give each cell a name. Not only single cells, you can actually define a name for a group of cells too. Then you can refer this group of cells by a particular name.

So instead of saying =G3+C3-F3, you can now calculate Salary as

=Basic_Pay + Allowance – Tax

This is much easier to read, and comprehend. No need to look up where the formulas are coming from, as the names are self evident.

9. Learn Excel Shortcuts: There are hundreds of shortcut combinations in Excel. It is impossible to remember all of them. But you must know a few to really speed up your work. Some of my favorites are:

  • Control N: New workbook
  • Control `: View All Formulas
  • Control Backspace: Jump to selected Cells
  • Alt + F1: Creates a inserts a chart based on the currently selected data as an embedded chart object.
  • Alt + =: Auto sum formula is inserted
  • Ctrl + Shift + Enter: Enter an Array Formula
  • F9: Calculates all the worksheets in all open workbooks

10. Learn to Use Dates Effectively: In Excel, dates are stored as numbers. They can be displayed in a wide variety of Formats. Understand these formats to display them in the way that suits the applications.

Also, you can calculate using dates, for example, you can find the difference between any two dates. You can add a fixed number of days to a date.

Hope you enjoyed these tips, and will use them to improve your productivity in using Excel. Do post a comment below to talk about your favorite tips in Excel.

If you wish, you can get a good book on Excel from the Excel BookStore. These books are usually quite inexpensive, and will give you many ideas to improve too.

Cheers,
Vinai Prakash, PMP, MBA, ITIL, GAP, Six Sigma

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

P.S.  : You can contact us to conduct a workshop on Excel Tips and Tricks at your office.

And I hope you subscribed to the ExcelChamp Excel Tips Newsletter already. 🙂

Difference Between 2 Dates, Without the Weekends

It has always been possible in Microsoft Excel to find the difference between 2 dates. And there are hordes of other date and time functions in Excel, some of which I wrote earlier about.

Today, let us look at a recently added function in Microsoft Excel, which can help you find the difference between any 2 dates, and this function works beautifully, even if your weekend does not fall on a Saturday/Sunday.

For example, recently I was in Cairo, Egypt to conduct Corporate Training, and found out that their weekends fall on Friday and Saturday.

The regular function that I used earlier – NetworkDays() –  is good at finding the days between any 2 dates, but it assumes that the weekend is on Saturday/Sunday.

Fortunately, Microsoft has added an International Function in Excel, which helps to work this out and makes it fairly easy.

The function is NETWORKDAYS.INTL().

It takes 4 arguments – Start_date, End_date, Weekend & Holidays. The last 2 are optional, but that is where the function shines and differs from the Networkdays() function.

The weekend argument can take one of these several values, and when you type the formula, this helpful guide pops up.

Weekends in Microsoft Excel

As you can see, you can easily select any single day or two consecutive days as your weekends.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


For Egypt and Dubai, their weekend of Friday, and Saturday has the value of 7.

Thus, I choose the following function:

=NETWORKDAYS.INTL(“1-Sep-2012”, “31-Sep-2012”,7)

and promptly got the result as 21, which is the number of working days in this period, excluding Fridays & Saturdays.

If you only use =NETWORKDAYS(“1-Sep-2012”, “31-Sep-2012”)

you will get the answer as 20 days, which is based on the Saturday/Sunday weekends.

So there you are. Have your cake and eat it too! You can check out these functions in the difference-between-dates Excel file, which you can use to practice and test out these functions.

Anything else you’d like covered here? Do post a comment and let me know.

Hope you enjoyed this Excel Tip. If you would like some more tips, simply subscribe to the ExcelChamp Tips Newsletter here.

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net – A community for Excel Tips & Tricks

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Using Date & Time Functions in Excel

There are many in-built Excel Functions that can help you in working with Dates and Time. It can assist you in showing today’s date, the current time, and represent dates as Months, Years, and even calculate difference in 2 dates.

1. Getting Today’s Date

In the cell where you want to display today’s date, key in the following formula:
=TODAY()

Depending on your PC’s Regional Settings (Setable in Control Panel), you will get today’s date displayed in MM/DD/YYYY or DD/MM/YYYY format.

The problem will this approach is that every the Excel file is re-calculated, the date will change and display the latest day’s date.

If you wish to only display today’s date, but do not want it to change in the future, there’s a little known shortcut to do this. Go to the cell where you want to get today’s date, and simply press Ctrl + ;

2. Getting Today’s Date & Time

In you want the date and time, both to be displayed, more like a time stamp, use the following formula:
=NOW()

It will display as 24/7/2012 12:33

If you only want the time, like a time stamp, and do not want the time to ever change, then simply press the following keys: Ctrl + Shift + ;

And if you want to display both the date and time stamp in a single cell, combine the 2 functions

Select a cell and press CTRL+; then press the SPACE BAR to add a space, and  then CTRL+SHIFT+;

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


3. Get the Number of Days in between 2 Dates in Excel

To get the difference in 2 dates, you can simply subtract them, like:

=B6 – A6

If you only want the Weekdays, excluding Sundays, you can use a hidden Excel gem – Networkdays function.

=NETWORKDAYS(A1, A2)

It will calculate the difference between A1 and A2, without counting the Sunday.

4. Get the Month of a Date 

To get the Month, displayed as a number – for example: July will be 7.

=MONTH( TODAY() ) will give you a 7 in July.

= MONTH(A1)

5. Getting only the Year of a Date

If you wish to extract the YYYY portion of a date, simply say:

=YEAR(A1)

For 31-July of the Year 2012, it will yield 2012.

As you can see, Excel’s in-built Date and Time Functions are really useful, and handy. Do master them, and you will save a lot of time.

Let me know how’s your experience in handling dates in Excel. You can post a comment below… 🙂

Cheers,

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!