Tag: Excel Round

Calculate Years Between Dates In Ms Excel

If you are an avid Ms Excel user, then you might have come across a task in which you needed to calculate the years between the dates; you might take it easy and do this task manually, which is also feasible but only for calculating the years between 3 to 4 dates. But when it comes to hundreds of dates, it becomes a cumbersome and time taking task to calculate the years between them manually.

Calculate years between dates1

But don’t worry since this post will teach you the simplest technique to calculate years between dates in Microsoft Excel. All you have to do is thoroughly read the article.

So without any further ado, let’s dive into it

General Formula 

=YEARFRAC(start_date,end_date)

 Explanations Of Syntax:

You must understand the syntax used in this formula in order to apply it to complete your task.

  • Parenthesis (): The primary function of this symbol is to group the elements.
  • The Comma Symbol (,): This acts as a separator.
  • YEARFRAC: It computes the year’s percentage represented by the number of entire days between two dates (start date and end date).

Summary

The YEARFRAC function, which returns a decimal value indicating the fraction of a year between two dates, may be used to compute the number of years between two dates. The formula in D6 in the example is:

=YEARFRAC(A2, B2)

Calculate years between dates1

Explanation

YEARFRAC provides a decimal value that represents the fractional years between two dates. As an example:

=YEARFRAC("January 1, 2020","January 1, 2022") / yields 2

Here are a few instances of YEARFRAC’s calculated results:

Start Date End Date YEARFRAC result
2002/1/10 2022/6/1 20.39167
2003/3/20 2019/4/20 16.08333
1999/1/20 2019/7/10 20.47222

The formula in C2 in the example is: =YEARFRAC(A2, B2) / returns 20 Rounding outcome

You can round the number after you obtain the decimal value. For example, you might use the ROUND function to round to the nearest whole number:

=ROUND(YEARFRAC(A2,B2),0)

Calculate years between dates1

Only Whole Years

You may also wish to maintain only the integer component of the result, with no fractional values, to ensure that you only count entire years. In such instance, merely use the INT function to enclose YEARFRAC:

=INT(YEARFRAC(A2,B2))

Calculate years between dates1 

Example #2

Assuming you already have the data established in the excel sheets, you do not need to repeat the first step. For those doing it for the very first time, open Excel on your laptop, create a new or empty excel sheet, and record your chosen date values as shown below.

Calculate years between dates1

We’ll use the YEARFRAC function to calculate the years between the dates on the excel mentioned above. This is the function that computes the number of years between two dates. The YEARFRAC function has the ability to compute in decimal; for example, it can display 1.5 years to imply one and a half years.

Years are where we anticipate recording our outcomes in the column with the title. To compute the years between the dates in cells A2 and B2,

Enter the formula =YEARFRAC (A2, B2) in the formula bar.

Calculate years between dates1

Apply the same procedure to the remaining cells, or hold and drag the cursor to compute the remaining years automatically.

Enter this formula in cell D2 and drag the fill handle to get the additional computations.

Calculate years between dates1

To round the decimal or fractional value to the closest whole number, use the ROUND function, such as =ROUND(YEARFRAC(A2, B2),0)

Calculate years between dates1

the above example shows how to round a fractional value to the nearest whole number.

If we want to return the integer component of the year’s value and not the fractional part, wrap the YEARFRAC function inside the INT function, as in =INT(YEARFRAC(B2, C2)). Figure 4: Obtaining the Year Value Absent Fractional Value

Calculate years between dates1

 Note: There is an optional third input to the YEARFRAC function that determines how days are tallied when computing fractional years. The default approach is to calculate days between two dates using a 360-day year, with each month having 30 days.

Related Functions

  • Excel INT function
    The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
  • Excel Round function
    The Excel INT function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.The syntax of the ROUND function is as below:=ROUND (number, num_digits)…

How to Round the Calculated Average result in Excel

This post will guide you how to use average and round functions at the same time to average a given range, and then round off the last result in Excel. How to use Round function to round the calculated average values with a formula in Excel.

Assuming that you have a list of data in range B1:B6 in which contain numeric values. And you can easily to calculate the average value with AVERAGE function. And the average value may contain two or more decimal places. And you want to round off this number to the specified number of decimal places. How to do it. You can use another function called ROUND. This post will show you tow methods to accomplish the result.

Round the Calculated Average result with Decimal Command

To round the calculated average value for your data, you can use increase or decrease decimal command with the following steps:

Step1: select the range that to be averaged. Such as: B1:B6.

Step2: type the following formula based on the AVERAGE function to calculate the average value for the given range of cells:

=AVERAGE(B1:B6)

Type this formula into a blank cell and press Enter key on your keyboard. You would see that the average value has been calculated.

round average function3

Step3: select the calculated average value and go to Home tab, and click Increase decimal button or decrease decimal button under Number group.

round average function2

round average function1

Round the Calculated Average result with a Formula

You can also use a formula based on the ROUND function and AVERAGE function to accomplish the same result of rounding the calculated average result in Excel. Like the following formula:

=ROUND(AVERAGE(B1:B6),2)

Note: if you want to round off to the specified number of decimal places as you need, you just need to change the number 2 as you want.

round average function4

Related Functions

  • Excel Round function
    The Excel INT function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.The syntax of the ROUND function is as below:=ROUND (number, num_digits)…
  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….

How to Limit the Number of Decimal Places in Excel

This post will guide you how to limit the number of decimal places in Excel. How do I round a number to the specific decimal places with a formula in Excel. How to Limit Number of decimal places with Format Cell Feature in Excel.

Limit Number of Decimal Places with in Formula

Assuming that you have a list of data in range B1:B5, in which contain number values with 3-4 decimal places. And you want to limit the number of decimal places for all numeric values to 2. How to do it. You can use the ROUND function to create a formula to limit the number of decimal places for each numbers. So you can use the following formula:

=ROUND(B1,2)

Type this formula into the Cell C1, and press Enter key on your keyboard, and then copy this formula from Cell C1 to the range C2:C5 to apply this formula.

limit number of decimal places1

You should see that all numbers have been limited to two decimal places.

Limit Number of Decimal Places with Format Cell

You can also use the Format Cell feature to achieve the same result of limiting the number of decimal places for the selected range that contain numbers. Just do the following steps:

#1 select the range of cells that contain numbers with four decimal places.

limit number of decimal places2

#2 right click on it, and select the Format Cells from the popup menu list. And the Format Cells dialog will open.

limit number of decimal places3

#3 click the Number tab in the Format Cells dialog box, and click the Number option in the Category list box, and then type number 2 in the Decimal places box. Click Ok button.

limit number of decimal places4

#4 you should notice that all numbers in the selected range have been changed to two decimal places.

limit number of decimal places5

Related Functions

  • Excel Round function
    The Excel INT function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.The syntax of the ROUND function is as below:=ROUND (number, num_digits)…

 

 

How to Round a Range of Cells in Excel

This post will guide you how to round a range of cells with a formula in Excel. How do I round a range of cells using VBA macro in Excel 2013/2016. How to round up a range of numbers in a column in Excel.

Round a Range of Cells with ROUND Function

Assuming that you have a list of data in range B1:B5 which contain numbers with several decimal places. And you want to round them with only two decimal places. You can use a formula based on the ROUND function to achieve the result. Like as follows:

=ROUND(B1,2)

round range of cells1

Then you need to type the formula in a blank cell and drag the AutoFill handle over to other cells to apply this formula.

round range of cells2

ROUND a Range of Cells with VBA Macro

You can also use an Excel VBA Macro to achieve the same result of rounding a range of cells quickly in Excel. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

round range of cells3

Sub RoundNumber()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to round up", "RoundNumber", myRange.Address, Type:=8)
    dNum = Application.InputBox("Please type the number of decimal", "RoundNumber", Type:=1)
    For Each myCell In myRange
        myCell.Value = Application.WorksheetFunction.Round(myCell.Value, dNum)
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

round range of cells4

#6 Select one Range that you want to round up, such as: B1:B5

round range of cells5

#7 Please type the number of decimal that you want to round.

round range of cells6

#8 Let’s see the last result:

round range of cells7

Video: Round a Range of Cells

 

Related Functions

  • Excel Round function
    The Excel INT function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.The syntax of the ROUND function is as below:=ROUND (number, num_digits)…

 

Excel ROUND Function

This post will guide you how to use Excel ROUND function with syntax and examples in Microsoft excel.

Description

The Excel ROUND function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.

The ROUND function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.

The ROUND function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the ROUND function is as below:

=ROUND (number, num_digits)

Where the ROUND function arguments are:

  • number -This is a required argument. Any real number that you wan to round up.
  • num_digits – This is a required argument. The number of digits to which numbe you wnat to round up.

Note:

  • If num_digits argument is greater than 0, then the number is rounded to the specified number of digits. So it will round  to the right of the decimal point.
  • If num_digits argument is equal to 0, then the number is rounded to the nearest integer.
  • If num_digits argument is less than 0, then the number is rounded to the left of the decimal point.
  • If you want to round up a number (away from 0), you can use the ROUNDUP function
  • If you want to round down a number (toward 0), you can use the ROUNDDOWN function.

Excel ROUND Function Examples

The below examples will show you how to use Excel ROUND Function to round a given number up or down to a specified number of decimal point.

1# to round a number 2.25 to one decimal places, enter the following formula in Cell B1.

=ROUND(2.25,1)

excel round examples1

2# to round a number 3.249 to one decimal places, enter the following formula in Cell B2.

=ROUND(3.249,1)

excel round examples2

3# to round a number -5.432 to two decimal places, enter the following formula in Cell B3.

=ROUND(-5.432,2)

excel round examples3

4# to round a number 25.632 up to one decimal places to the left of the decimal point, enter the following formula in Cell B4.

=ROUND(25.632,-1)

excel round examples4

Related Functions

  • Excel ROUNDDOWN function
    The Excel ROUNDDOWN function round the number down to the specified number of digits. The syntax of the ROUNDDOWN function is as below:=ROUNDDOWN (number, num_digits)…
  • Excel ROUNDUP function
    The Excel ROUNDUP function rounds the number up to a specified number of decimal places. It will round away from 0.The syntax of the ROUNDUP function is as below:=ROUNDUP (number, num_digits)…
  • Excel CEILING Function
    The Excel CEILING function returns a given number rounded up to the nearest multiple of a given number of significance. The syntax of the CEILING function is as below:= CEILING (number, significance)…
  • Excel CEILING.PRECISE Function
    The Excel CEILING.PRECISE function returns a given number rounded up to the nearest multiple of a given number of significance or returns a number that is rounded up to the nearest integer number.The syntax of the CEILING.PRECISE function is as below:=CEILING.PRECISE (number, [significance])…

How to Calculate Days, Weeks, Months and Years between Two Dates

This post will teach you how to calculate days, weeks, months and years between two dates in excel. How do I count the number of days, weeks, months and years between 2 dates in excel.

Calculate days between two dates

If you want to calculate the difference in days between two dates, you can use the DATEDIF function to create an excel formula as follows:

=DATEIF(B1,B2, "D")

This formula will calculate days between two dates in cell B1 and B2, then returns the value in days.

Calculate months between two dates

If you want to calculate the difference in months between tow dates, you can also use the DATEDIF function to create the following generic formula:

=DATEDIF(B1,B2,"M")

You should note that the third argument is “M” in the DATEDIF function. So this formula returns the value in months between two dates in excel.

Calculate years between two dates

You can also use the DATEDIF function to calculate the number of years between two dates in excel, just refer to the following excel formula based on the DATEDIF function:

=DATEDIF(B1,B2,"Y")

This formula returns the value in years.

Calculate weeks between two dates

If you want to calculate the number of weeks between two dates, you just need to subtract start date from the end date and then the returned result is divided by 7. So you can write the below generic formula:

=(B2-B1)/7

calculate days weeks months between two dates1

This formula will return a decimal number, you can change the number format as you need.

Or you can use the INT function to rounds down to the nearest whole number or use ROUND function to round to nearest whole number.

=INT((B2-B1)/7)

calculate days weeks months between two dates2

=ROUND((B2-B1)/7,0)

calculate days weeks months between two dates3

You may be want to display the word “week” before the week number in the cell, you can do it as following steps:

1# right-click on the selected cells, then select Format Cells

2# on the Number Tab, choose Custom under Category, then select # “weeks” type.

calculate days weeks months between two dates4

calculate days weeks months between two dates5

Calculate Years, Months and Days between two dates

If you want to determine how many years, months and days between two dates, you can use the DATEDIF function to create the following complex formula:

=DATEDIF(B1,B2,"Y") & " Years, " & DATEDIF(B1,B2,"YM") & " Months, " & DATEDIF(B1,B2,"MD") & " Days"

If you do not want to use the DATEDIF function, and you can also use the following formula to achieve the same result:

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”

Related Functions

  • Excel INT function
    The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
  • Excel DATEDIF function
    The Excel DATEDIF function returns the number of days, months, or years between tow dates.The syntax of the DATEDIF function is as below:=DATEDIF (start_date,end_date,unit)…
  • Excel TODAY function
    The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
  • Excel Round function
    The Excel INT function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.The syntax of the ROUND function is as below:=ROUND (number, num_digits)…