Excel Formula To Calculate Age in Years Months and Days On a Specific or Current Date

Hello Friends,

As promised in this article I will show you the process to calculate the exact age in Days, Months and Years from two given dates be it current date or any specific date.

I will use DATEDIF function to get the age in year, month & day.

Ifyou need to know about this function please click hear to know how thisfunction works.

Just to recall I would like to quote from my own article, "Please note, DATEDIF function is not like other functions, Excel will not help opening function argument box to be filled with data but if you provide all the data in correct order, it will help you a lot in future."

Syntax

The syntax for the DATEDIF function in Microsoft Excel is as below,

=DATEDIF( start_date, end_date, interval )

*** End date must be greater than Start date

In the below article you can notice that I have taken Date of Birth in 'B' column and I have taken current date in the 'C' column and the result is in the next column.

Calculate Age in Year Month and Day

Calculate Age in Year Month and Day - Formula

Calculate Age in Year Month and Day - Formula

To get exact age from a specific or current date we need to use the same formula and we need to use one function three times with CONCATE function or using "&" operator.

the formula is :

=DATEDIF(B4,C4,"Y") & " Years " & DATEDIF(B4,C4,"YM") & " Months " & DATEDIF(B4,C4,"MD") & " Days"

Note : I can modify this function using CONCATE function and then the excel formula becomes...

=CONCATENATE(DATEDIF(B4,C4,"Y"), " Years " & DATEDIF(B4,C4,"YM"), " Months " & DATEDIF(B4,C4,"MD"), " Days")

I use '&' to avoid extra function.

Now if we analyze the formula then we notice that the this formula is using DATEDIF function thrice but the last parameter is changing.

The meaning of these last parameters are as below.

Y       - The of Complete Year

YM   - The difference between the Months (Days and Years are ignored)

MD   -  The difference between the Days (Months and Years are ignored)

I hope it is quite easy way to calculate the exact age in years and months and days.

Keep reading and keep growing your knowledge in Excel...

Thank you for reading...

DATEDIF Function

Today I will discuss about DATEDIF function which is very helpful function when dealing with Dates. In next article we will use DATEDIF function to get the actual age on a given date as well as on current date. Therefore, this function is very important function.

DATEDIF function returns the difference between two date values, based on the interval specified.

Please note, DATEDIF function is not like other functions, Excel will not help opening function argument box to be filled with data but if you provide all the data in correct order, it will help you a lot in future.

Let us first go through the syntax of this function.

The DATEDIF function is categorized as a Date/Time Function.

Syntax

The syntax for the DATEDIF function in Microsoft Excel is as below,

=DATEDIF( start_date, end_date, interval )

*** End date must be greater than Start date

Parameters or Arguments

start_date and end_date

The two dates to calculate the difference between.

interval

The interval of time is used to calculate the difference between two dates. Below is a list of valid interval values.

How to use DAYDIF Function

Returns

The DATEDIF function returns a numeric value.

Example :

DATEDIF is very simple function, you need to put the proper arguments within this function and the result will be displayed.

I have given below few examples, please go through it.

How to use DAYDIF Function

Limitations :

1.   Microsoft Excel will not help you fill in the DATEDIF function like other functions using argument window.

2.   Microsoft recommends not using the "MD" value for unit because it "may result in a negative number, a zero, or an inaccurate result" But there are ways to overcome these errors which I will discuss in next article.

If you still have some questions, feel free to ask in the comment box.

I hope you have enjoyed this small article. Take care…

Thank you for reading….

RAND Function - Generate Random 'Decimal' Numbers In Excel

In my last article I discussed about how to generate RANDOM numbers in Microsoft Excel which is very easy using the function RANDBETWEEN. You type the function in excel and provide the Bottom and Top number (as lower and upper limit) and you get the Random numbers.

But if you notice carefully all these randomly generated numbers are integer type numbers, therefore, in this article I will discuss with you how to generate Decimal Random Numbers.

One solution is to divide the output of RANDBETWEEN numbers by 10, 100 or 1000, i.e., in multiple of 10.

But the problem is the integer part of these numbers will reduce and the number will not be within bottom and top range.

For example, suppose the output of RANDBETWEEN is 4387 and we divide it by 10, it will turn into 438.7, divide by 100 it will be 43.87, further divide by 1000, the output will be 4.387.

Which is not desirable.

The solution is to use RAND function.

Lets first discuss about the RAND Function.

Syntax

The syntax for the RAND function in Microsoft Excel is very simple.

=RAND( )

Parameters or Arguments

No parameters or arguments require for the RAND function.

Note : The Microsoft Excel RAND function returns a random number which is greater than or equal to 0 and less than 1. The RAND function returns a new random number each time your spreadsheet recalculates, similar to RANDBETWEEN function.

Example :

To use RAND function type =RAND() and press enter and you are done.

RAND Function

But did you noticed integer part is missing, why ?

Because RAND function output is from 0 to 1.

So, you can modify your formula by multiplying 10 and you may or may not get the decimal number having integer part.

RAND Function

This is because if the output of RAND function is starting with 0 then the number will be without having any integer part.

Also notice that we have no control over these Random numbers i.e., no upper or lower boundary or limit is implemented.

The Solution :

The solution is to modify the formula as below :

=RAND()*(B-A)+A

Where B represents Upper limit or Top number and A presents the Lower limit or bottom number.

RAND Function

What is the function of (B-A)+A in RAND formula ?

The Ordering of Mathematical Operation teaches us that multiplication is first to be calculated in this formula and then addition and then Subtraction will be applied.

Ordering of Mathematical Operation

The steps are

       

1.   RAND generates a random number between 0 to 1

2.   This number gets multiplied by B and A

3.   New numbers are subtracted

4.   then summed up by A

Hope you have enjoyed this article ...

Keep reading, sharing & Stay blessed ...

RANDBETWEEN Function

In this article I will discuss about RANDBETWEEN Function which returns a new random number but these numbers get changed each time your spreadsheet recalculates.

What is meant by 'spreadsheet recalculates' ?

Every time you enter something or rectify your formula or data spreadsheet gets refreshed. This can better be understood after using this function.

So RANDBETWEEN Function generates random numbers between the specified ranges.

Description:

The Microsoft Excel RANDBETWEEN function returns a random number that is between a bottom and top range. You need to specify TOP and BOTTOM numbers.

Syntax:

The syntax for the RANDBETWEEN function in Microsoft Excel is simple

=RANDBETWEEN( bottom, top )

Parameters or Arguments

bottom

The smallest integer value that the function will return.

top

The largest integer value that the function will return.

Things to Remember

1.   RANDBETWEEN function will recalculate whenever worksheet is refreshed, therefore, if you have generated random numbers using RANDBETWEEN function, please use paste special to prevent changing these numbers whenever excel workbook or worksheet refreshes.

2.   These numbers are integer type, RANDBETWEEN function generates integer type output. In my next article I will show you how to generate decimal random numbers.

Example:

I have used BOTTOM number as 100 and TOP number as 999 to generate random integer numbers between 100 to 999.

How to use RANDBETWEEN Function in Excel

Hope you have enjoyed this article ...

Keep reading, sharing & Stay blessed ...

MROUND Function

We have discussed CEILING & FLOOR function with significance, in this article we will discuss about MROUND function to round a number to a given numbers' multiplication.

Description :

The Excel MROUND function returns a number rounded to a given multiple. MROUND will round a number up or down, depending on the nearest multiple.

Syntax :

=MROUND (number, multiple)

Arguments :

number - Its a number that should be rounded up or down.

multiple - Its a number which is used as 'multiple' when rounding.

Few Things to Remember :

1.   MROUND rounds up, away from zero.

2.   The rounding occurs when the remainder left from dividing number by multiple is greater than or equal to half the value of multiple.

3.   Number and multiple must have the same sign.

Difference Between CEILING and MROUND Function

CEILING, rounds a number up, to the nearest integer or nearest multiple of significance, it can only be away from Zero;

whereas, MROUND, rounds up or down a number to the specific multiplier.

Below examples will clarify the difference between MROUND and CEILING function.

Example :

In this example we will use different Sales Amount and will fix the second parameter as $ 1000 for both MROUND and CEILING function.

Our formula will be for MROUND =MROUND(C3, 1000)

and

=CEILING(C3, 1000) for CEILING function.

If you notice carefully, you will find that CEILING function is giving output as $ 1000, irrespective of the input number (150, 499, 501 & 999) but MROUND shows $ 1000 as output only when the number is greater than or equal to $ 500.

This means that, CEILING function rounds up a number away from ZERO in multiplication of the provided significance.

And, MROUND function rounds a number up or down to the specific multiplier.

In this case if we use 500 as multiplier in MROUND function it will display $ 500 if the number provided is 499 (=MROUND(C4, 500)).

MROUND Function in Excel

Difference between MROUND  and CEILING Function in Excel

Hope you have enjoyed this article ...

Keep reading, sharing & Stay blessed ...

Pivot Table From Single or Multiple Pivot Table

Pivot of a Pivot ? Is it possible in Microsoft Excel ?

Yes, it's possible in Excel even you can create Pivot Table from multiple Pivot Tables.

In this article, we will discuss how to create Pivot Table from another Pivot Table as data source.

We will also discuss how to create Pivot Table from multiple Pivot Table as data sources.

To get PivotTable from another PivotTable as source, we need to use PivotTable and PivotTable Chart Wizard.

 PivotTable and PivotTable Chart Wizard

Microsoft Excel 2007/2010/2013/2016/2019 hides the PivotChart Wizard, which is not shown in the Ribbon.

Without Classic Menu for Excel installed, you will not find it. 

To use 'PivotTable and PivotChart Wizard' as menu item, there still are several ways to configure it.

Excel also provided us shortcut to get this wizard, Alt+D+P.

If you press these keys combined, a new window will appear, the PivotTable and PivotChart Wizard.

The problem with this shortcut is that every time you need PivotTable and PivotChart Wizard you must press these key combination.

The permanent solution is to get it somewhere in the menu. Therefore, lets first learn how to get PivotTable and PivotChart Wizard in the menu or as a button permanently.

Steps to configure the Wizard :

1.   Click File / Office Tab, and click “Options” item.

2.   Click the Customize Ribbon / Excel Options.

3.   In the Excel Options window, you will see Customize the Ribbon or Customize option.

4.   Choose Commands Not in the Ribbon or All Commands by clicking the arrow, and then use the scroll bar to search for the PivotTable and PivotChart Wizard.

5.   When finally find out the Wizard, click Add to add it into the Ribbon / Quick Action Toolbar, and then click OK to finish the step.

The images are shown Step by step.

Excel Option to Create PivotTable and PivotTable Chart Wizard

Excel Option to Create PivotTable and PivotTable Chart Wizard

Excel Option to Create PivotTable and PivotTable Chart Wizard

Now, as we have configured the PivotTable wizard successfully, we can use this wizard to create PivotTable from another PivotTable as data source.

Steps :

1.   Select any blank cell in the Sheet

2.   Use the button in the Quick Access Toolbar or alternatively you can use Alt+D+P shortcut to start the PivotTable and PivotChart Wizard.

Quick Access Toolbar Button - PivotTable and PivotTable Chart Wizard

PivotTable and PivotTable Chart Wizard Step 1

3.   You will notice that multiple options are visible and the top option 'Microsoft Office Excel list or Database' is selected.

4.   Click 'Next', it will ask you to select the range, select the range from the existing PivotTable and click 'Next'.

PivotTable and PivotTable Chart Wizard Step 2

5.   The third and last step is to select the location of the new PivotTable either in Existing Worksheet or in a New Worksheet.

PivotTable and PivotTable Chart Wizard Step 3

6.   Click 'OK' and you will get your PivotTable using previous PivotTable as data source for the new PivotTable.

Example :

I am using Excel 2007 and in Excel I have inserted a PivotTable source is from a data table in this Excel Workbook which simply shows Day wise Date wise Target of various products.

To understand it better I have given a simple image.

PivotTable from another PivotTable as Data Source

PivotTable from Multiple PivotTables

So you learnt how to create PivotTable from another PivotTable, the second question comes in mind, How to create  PivotTable from Multiple PivotTables ?

1.   It is very simple, notice the PivotTable and PivotChart Wizard's third option, "Multiple consolidation ranges", click the radio button to select this option and click 'Next'.

PivotTable from Multiple PivotTable

2.   The next step will ask you to select 'Create a single page field for me' and 'I will create the page fields', select the first option, 'Create a single page field for me', click 'Next'.

PivotTable from Multiple PivotTable

3.   Now, in this step Excel wants to take input "Where are the worksheet ranges that you want to consolidate?"

        a) Select the first range i.e., from first PivotTable and click 'Add' which will add the location of the PivotTable in the below box

        b) Again select the second PivotTable and click 'Add'.

PivotTable from Multiple PivotTable

4.   Now, choose the location where you want to insert your new combined PivotTable, click 'Finish'.

PivotTable from Multiple PivotTable

Now your data is ready.

Example of PivotTable from Multiple PivotTable

Note : A report filter is automatically generated in this PivotTable which if selected will display data from the specific locations.

Hope you have enjoyed reading this article ...

Keep reading, sharing & Stay blessed ...