How to Subtract a Date From Today in Excel

by

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

How To Center a Title in Excel

by

A centred title looks so much better in Excel reports or charts. I have seen many users and even management requests for the chart title to be in the center of the report or Excel chart.

There are several ways to center a report title in Microsoft Excel. And most of these techniques will work in any version of Excel. So if you have had any of these versions of Microsoft Excel, you are safe.

This tip is applicable to Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 or the latest Microsoft Office Excel 365.

title in center in Excel

Please check out both of these methods and my warning about their usage. Finally, read my preferred and recommended actions while centring titles in Excel spreadsheets.

Method 1: Merge and Center Title Text

Step 1: Select the range of cells where you want to center the text item. Take note that the text you want to center must be in one of these cells. Further, there should be no other text in the selected range, or it will be overwritten. Microsoft Excel will keep the text in the top-left cell of the selected range, and discard any other text in the selected range.

Step 2: Click on the Merge and center button. This is present on the Home Tab, under the Alignment group. It looks like 2 cells on top, a double-headed arrow, and 2 cells at the bottom.

Merge and center in Excel

The selected text is merged and centered in the selection. If multiple rows were selected, the centered text will be at the bottom row.

In case you want to align the text in the middle too, you have to click on the Middle Align icon in the formatting toolbar. Now the text should be exactly in the middle horizontally and vertically.

Middle Align in Excel

CAUTION: While you may like this method of merging and centering text, because of its ease and convenience, we do not recommend to merge cells in Excel worksheets.

All Excel pros adhere to this rule – never merge cells, unless you are presenting it in a report or dashboard, as an end result. Raw data should never be in merged cells.

Merged cells can cause havoc while trying to sort data.

Basically, you can’t sort data if it contains even a single merged cell. So you have to unmerge the cells before you can sort it.

Further, data in an Excel Table can’t be merged too. This is because sorting and filtering get affected if you merge cells in Excel. In this case, you have to convert the Excel table into a normal range. Then only you can centre the text.

Method 2: Center Across Selection

Some of the older versions of Excel did not contain the Merge and center option in the ribbon. This method 2 works when you are using such older versions,s but it also works in newer versions of Microsoft Excel, including Office 365.

And this method does not merge the cells. It only centers the text. Thus, our earlier problem with merged cells is actually eliminated.

To use this “Center across Selection” method, do the following steps:

Step 1: Select the multiple cells where the text is to be centered. The text should be present within this range.

Step 2: Click on Format Cells (Shortcut Control + 1 on Windows). Or right click the mouse button on the selection, and pick Format Cells from the Options menu.

center across selection in Excel

Step 3: On the Format Cells dialog box, stay on the Alignment Tab. In the Text alignment dropdown showing “Horizontal“, you will see the default alignment set to “General“. Pick “Center across Selection” from the selection. See screen shot below.

This will center the text across the selection. But the cells won’t be merged. This is the preferred way of centering a title in Excel.

How to Unmerge Cells in Excel

If you have clicked on Excel’s Merge and center text icon, and now decided to unmerge it, just select the text, and click on the “Merge and center” icon again. This button acts like a toggle switch. The merged cells will be unmerged, and the centered text will go back into the first unmerged single cell.

How to Align Text within a Single or Merged Excel Cell

If large cells, the text may appear at the bottom left corner. If you want to change the cell alignment vertically and horizontally, you need to click the Middle Align button & Center buttons under the Alignment group under Home Tab. Now the text will move to the center of the cell.

How to Center a Title in Excel Charts

When you create an Excel Chart, by default a Chart Title is added in the centre. In some old versions of Excel, you might not get the Title in the Chart. And in some cases, Excel can’t figure out what title to put, because the column name happens to be blank. In this case, you will end up without a chart title at all. But you can add a chart title by hand, manually and then center it.

Chart With Title in Center

Steps to Center the Chart Title in Excel

  1. Click the Green + icon on the top right of the Chart (applies to Excel 2013, 2016, 2019, Office 365 spreadsheets).
  2. The Chart Elements will be displayed. Click the Checkbox on Chart Title. A Chart title is now placed in the top center of the Chart.

    Adding a Chart Title in Excel

  3. You can click inside the text box, and type any appropriate title text. It will be centered by default.

Print Excel worksheets with Worksheet titles in header or footer of the print out

  1. Go to Page Layout.
  2. Choose Page Setup popup from the bottom right flyout menu.

    File Name in center of page

  3. Choos Header/Footer tab from the Page Setup popup
  4. Click Custom Header or Custom Footer option. Another Header or Footer Popup will open.
  5. Click the Green Excel icon and the File name will be printed in the Left section.
  6. Click inside the Right section, and then click on the page number # icon. The page number will now be printed in the right section.
  7. You can display the current date in the Center section.
  8. When you do a Print Preview, you will see a beautifully formatted Report, with the date centred, the file name on the left, and page number in the right section.

At ExcelChamp, we conduct a training course for Excel Formatting & Formula Tips & Tricks, which covers such formatting tips for beginner & advanced users alike.

Hope you enjoyed the tips presented here. They are pretty simple and easy to implement. Go ahead and amaze your audience with amazing charts, reports and analysis done in Excel.

Cheers,
Vinai Prakash,
Founder & Master Trainer at ExcelChamp.Net

 

Find Age in Years within Microsoft Excel [Video Tutorial]

by

Wondering How To Find the Age in Years from Date of Birth, in Microsoft Excel?

See multiple ways to calculate Age in Excel from any date of birth.

This simple, step-by-step tutorial on finding the age in Excel from date of birth or any other date, work on Microsoft Office 365, Microsoft Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 & Excel 2003 too.

We cover Microsoft Excel Functions & Formulas that allow you to calculate the following things in Excel:

  1. Find the age in Years
  2. Find the Age in Months
  3. Find the Age in Days
  4. Finding the exact age in Years, Months & Days.
  5. Combining Years & Months to create a Text String the way we want.

Here’s how to calculate the number of years since the date of birth

Not only the years we can also calculate the months. I will show you multiple techniques to calculate the age in years, months & days.

If you want a whole number or you want the age as a fraction, there are so many ways to do it.

What we want to calculate is the age in years –  something like Age is 55 years. Or Age is 55.56 years.

Before we start i want to highlight to you that the date of birth that you are using should be a date field so you can select it and make sure the formatting is not General or text, but it should be set as a short date or any kind of date.

To calculate the years,  the formula is extremely simple. We will be using the DATEDIF function of Excel.
=DATEDIF(DateofBirth, TODAY(), “Y”)

Similarly, we can calculate the age in months too.

=DATEDIF(DateofBirth, TODAY(), “M”)

This will result in the number of months from the date of birth to today’s date. You might see a huge number like 666 here.

But you may not want this.  Perhaps we only want how many months after the 55 whole years have elapsed.

In this case,  we modify the formula from only “M” to “YM”

=DATEDIF(DateofBirth, TODAY(), “YM”)

YM means it is only the months after the completed years. By doing this we can see that only six months have elapsed after 55 years.

Finally, we only need the days that have elapsed after the completed months in the age.

=DATEDIF(DateofBirth, TODAY(), “D”)

This will tell you that 20,000 days have passed from the date of birth, but, of course, we don’t want this.

So you change D to MD. The “MD” tells Excel to calculate how many days after the month have elapsed. 

As you can see, we have the age calculated in different cells – the age is 55 years 6 months and 9 days exactly till today.

If you wish to see a complete combined date, you can use the CONCAT function to combine multiple formulas, or use the & to combine text as well.

As an example, we can write

=CONCAT(DATEDIF(dateofbirth,TODAY(),”Y”),” Years, “,DATEDIF(dateofbirth,TODAY(),”YM”),” Months & “,DATEDIF(dateofbirth,TODAY(),”MD”),” Days”)

For a DateofBirth date of 6-December-1966, it generates, 55 Years, 6 Months & 28 Days.

Hope you find this DATEDIF function quite handy for such use. Do give it a try and let us know how you use it.

Cheers,
Vinai

Recommended Reading for Next Steps:

  1. Using Date & Time Functions in Excel
  2. Difference Between 2 Dates, Without the Weekends

Beginner’s Guide to Microsoft Excel

by

Most people just start using Microsoft Excel without much thought. After all, it seems so easy to use Excel.

Yet, if you don’t have a strong foundation of Microsoft Excel, you will begin to struggle after a while… Most people begin to feel overwhelmed when they see

  • begin doing data entry,
  • need to sum or count the data quickly,
  • want to format the data properly.

And if you don’t know the shortcuts to do such things quickly, you’ll be wasting a lot of time, causing delays.

Step by Step Beginner’s Tutorial on Microsoft Excel [2022]

In this step-by-step Excel Beginners tutorial by ExcelChamp Founder Vinai Prakash, we teach you to get started with Excel easily.

This video is created for beginners in Excel. You can use any version of Excel, but you can still benefit from the steps and it will help you in using Excel easily.

In this Excel Beginners Tutorial Video, We show you step by step, how to do the following tasks:

  • Start Excel,
  • Open a blank workbook,
  • Get used to the rows, columns, and cells in Excel
  • How to do data entry in Excel
  • How to cut, copy, paste, move and select multiple cells, rows and columns in Excel,
  • Where is the formula bar in Excel,
  • How to use the formula bar in Excel,
  • Using the name box for navigating within an Excel file
  • Adding multiple sheets in Excel
  • Renaming a sheet in Excel
  • Deleting a sheet in Excel,
  • How to do formatting in Excel – Bold, Center etc.
  • How to Merge Headings in Excel
  • How to add borders
  • How to format the values and text in Excel.

Improve Your Speed, Accuracy, and Competence in Microsoft Excel with a strong foundation in Microsoft Excel.

After viewing the video tutorial carefully, a couple of times,

  • you will become an expert in the basic usage of Microsoft Excel,
  • You will be able to finish your data entry in Excel faster,
  • You will be able to work with multiple Excel sheets, rows, columns, and
  • begin to do data formatting in Excel like a Pro!

I am sure you will enjoy this Excel Beginner’s Video tutorial. And let me know what you would like to learn next… I’ll be happy to receive a message from you – Vinai 🙂

Author: Vinai Prakash

I help Excel users on how to properly and effectively use Microsoft Excel formulas and functions, Pivot Tables, Charts, and Shortcuts with Tips & Tricks to get their day-to-day work like data entry, consolidation, analysis, charting, reporting etc. is done quickly and efficiently, without any errors, every time!

Excel Pivot Table Tutorial

by

Excel Pivot Table Tutorials: Step By Step Training Videos By ExcelChamp

Pivot Tables are one of the most important features of Microsoft Excel. Pivots help to quickly summarize and analyze any data set.

Almost all Executive, Managerial, Analyst and Serion Management roles require solid understanding and knowledge of Excel Pivot Tables.

Overview of Pivot Tables Training Course

Part 1: Introduction to Pivot Tables in Excel

Part 2: Introduction to Pivot Tables Formatting, Styles & Presentation in Excel

Part 3: Customize a Pivot Table For Best Results in Excel

Part 4: Create Subtotals in Pivot Tables in Excel

Part 5: Sorting Options in Excel Pivot Tables

Part 6: Filtering Data in Excel Pivot Tables

Part 7: Create Calculated Fields & Columns in Excel Pivot Tables

Part 8: Summarize Data With Excel Pivot Tables

How Did You Like This Training?

We hope you enjoyed this Free Pivot Table Training that helps in analyzing data quickly with Excel.

Do let us know how you liked this training. Simply write a comment below the YouTube Videos.

We read each and every comment, and will really appreciate it.

Cheers,
Vinai Prakash
Founder, ExcelChamp.Net