How to Calculate Retirement Date and Remaining Years refer to Birth Date in Excel

Every company has its own police about the retirement date for employees. Suppose employees will be retired from a certain age of 60, how can we calculate the retirement date for everyone base on their birthdays? Actually, there are several functions can calculate the retirement date in excel, you can use them in proper formula, then we will get the correct result. This article will provide you some useful functions.

For example. We have a list of employee’s information. In this table, we can see the employees’ ID, name, birth date. And base on the birth date, we want to know their retirement date and remaining years. Now let’s follow below steps to find the solution.

Calculate Retirement Date 1

1. Calculate Retirement Date refer to Birth Date in Excel

Suppose you will be retired at age of 60-year-old. If you were born in 3/14/1985, you will be retired at 3/14/2045. You can add 60 on you birth year. As there are many employees need to be calculated in a company, so we need a simple formula to auto calculate the retirement date.

Step1: In D2 which shows the retirement date, enter the formula:

=EDATE(C2,12*60)
Calculate Retirement Date 2

In this formula, EDATE function can return a date which has the same date with the entered start date but comes in the future after several certain months. As the retirement year is 60, so parameter for months is 12*60 in this case.

Step2: Click Enter to get the result. Verify we get a five-digits number. That’s because the returned number is General format.

Calculate Retirement Date 3

Step3: Select D2, click Home, in Number panel, click dropdown list (the default load value is General). Select Short Date.

Calculate Retirement Date 4

Step4: Verify that D2 is updated to short date format properly.

Calculate Retirement Date 5

Step5: Drag the fill handler to fill D3-D6. Verify that retirement date is calculated properly in this column.

Calculate Retirement Date 6

2. Calculate Remaining Years in Excel

Except the retirement date, we also want to know the remaining years before the retirement date.

Step1: In E2, enter the formula:

=YEARFRAC(TODAY(),D2)
Calculate Retirement Date 7

In this formula, YEARFRAC Function calculates the ratio of days (take the number of full days) between two dates (start_date and end_date) to one year.

Step2: Click Enter to get the result. Verify that left year is calculated properly.

Calculate Retirement Date 8

Step3: Drag the fill handler to fill E3-E6. Verify that remaining years is calculated properly in this column. Verify that remaining years is not always an integer due to dates between today and retirement date don’t equal to entire years.

Calculate Retirement Date 9

Step4: If you want to just keep the integer part without rounding, you can use INT function. For example in E2, edit formula:

=INT(YEARFRAC(TODAY(),D3))

Verify that 14 is displayed.

Calculate Retirement Date 10

3. Video: Calculate Retirement Date and Remaining Years

This video will show you how to use Excel to calculate your retirement date and the remaining years until retirement based on your birth date, making retirement planning a breeze.

4. Related Functions

  • Excel EDATE function
    TThe Excel EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date.The syntax of the EDATE function is as below:=EDATE (start_date, months)…
  • 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 MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • 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)…

How to Check If a Number is Integer in Excel

This post will guide you on how to check if a number is an integer in Excel using different methods. Excel provides built-in functions such as the INT and MOD functions that you can use to determine if a number is an integer.

Additionally, you can create a user-defined function using VBA code to check if a number is an integer. By using these methods, you can easily determine if a number is an integer and use it in your calculations or data analysis in Excel.

1. Check If Value is Integer Using INT Function

Assuming that you have a list of data in range B1:B5, in which contain numeric values. And you want to test each cell value if it is an integer, if true, returns TRUE, otherwise, returns FALSE. How can I do it. You can use a formula based on the INT function to achieve the result. Like this:

=INT(B1)=B1

Type this formula into a blank cell, such as: Cell C1, and press Enter key on your keyboard. Then copy this formula from cell C1 to range C2:C5 to apply this formula to check values.

check if number is integer1

Let’s see how this formula works:

The INT function try to extract the integer portion from a given cell value, if the returned value is equal to the default cell value, it indicated that numeric value is an integer.

2. Check If a Number is Integer Using MOD Function

You can also use the MOD function in combination with IF function to check if a cell value is integer in Microsoft Excel Spreadsheet. Just use the following MOD formula:

=IF(MOD(B1,1)=0, "TRUE", "FALSE ")

Select a cell where you want to display the result, then enter this formula, press Enter.

How to Check If a Number is Integer in Excel 10.png

The MOD function in Excel is used to return the remainder after dividing one number by another. If a number is divided by 1 and the remainder is 0, then the number is an integer.

3. Check If a Number is Integer with User Defined Function (VBA Code)

You can create a user-defined function in VBA (Visual Basic for Applications) to check if a number is an integer in Excel. Here’s how you can do it:

Step1: Open Excel and press Alt + F11 to open the Visual Basic Editor.

Step2: In the editor, click on Insert > Module to create a new module.

Step3: Type the following code into the module:

How to Check If a Number is Integer in Excel 11.png
Function IsInteger_ExcelHow(ByVal num As Double) As Boolean
    If num = Int(num) Then
        IsInteger_ExcelHow = True
    Else
        IsInteger_ExcelHow = False
    End If
End Function

Step4: Save the module and return to the Excel worksheet.

Step5: In the cell where you want to display the result, enter the formula:

 =IsInteger_ExcelHow(B1)

 where B1 is the cell containing the number you want to check.

How to Check If a Number is Integer in Excel 12.png

If the number in cell B1 is an integer, the formula will return TRUE. If it is not an integer, the formula will return FALSE.

4. Video: Check If Cell Value is Integer

This video will guide you how to check if a number is integer in Cells in Excel with different methods.

5. 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)…

Cash Denomination Calculator

Every country has different cash denominations, so you may need to calculate the number of different denominations based on the total amount. If it is just a small amount of cash, then you can calculate the different cash denominations manually, but if multiple cash amounts are needed to calculate the number of denominations, then it is not a good way to do it by manually.

This article will explain how to quickly calculate a given cash denomination value using formulas in a Microsoft Excel spreadsheet or Google Sheets.

General Formulas for Money Denomination Calculators

In Excel or Google Sheets, you can calculate the cash denomination number by using the following INT function and in combination with the SUMPRODUCT function to create a nested formula as shown below.

=INT(($A4-SUMPRODUCT($B$3:B$3,$B4:B4))/C$3)

For the denomination values in the first column, the following formula needs to be used.

Note: When you execute this formula, you need to make sure that iterative calculation is turned on, otherwise you will see the error message #VALUE!

To turn on the Iterative calculation, do the following:

For Microsoft Excel, you need to go to the Excel options window, then go to the Formulas category, and in the Calculation options area, check the box “Enable iterative calculation“. See the following screenshot.

For Google sheets, you need to select the File menu, then click on the Settings submenu, the “Settings for this Spreadsheet” window will pop up, click on the Calculation tab, set the Iterative calculation status to On, and then click on “saving settings“.

Cash denomination calculator2

Let’s See How To Use This Formula

STEP 1: Enter the following formula in cell B4 and press Enter to make the formula take effect.

=INT($A4/$B$3)
Cash denomination calculator

STEP 2: Select cell B4, drag Fill Handler down to the other cells, so that other cells also apply the formula.

Cash denomination calculator

As you can see from the above figure, the value of the first denomination column has been calculated.

STEP 3: Enter the following formula in cell C4 and press Enter to make the formula take effect.

=INT(($A4-SUMPRODUCT($B$3:B$3,$B4:B4))/C$3)

Cash denomination calculator

STEP 4: Select cell C4, drag Fill Handler to the right to the other cells, so that other cells also apply the formula.

Cash denomination calculator

STEP 5: Select the cell range C4:G4, drag the Fill Handler on cell G4 down to cell G8, so that other cells also apply the formula.

Cash denomination calculator

Let Me See How This Formula Works

=INT($A4/$B$3)

For the value in column B, since this column is in the first column of the entire denomination column, all we need to do is divide the denomination by Cash Amount, and after that we will remove the decimal part of the returned value by the INT function. For the value of column B will be much easier than other columns, because you do not need to consider the previous counting values.

For example, we can use the following simple formula for the cells in column B.

=INT($A4/$B$3)
Cash denomination calculator

=SUMPRODUCT($B$3:B$3,$B4:B4)

For column C and the subsequent columns, because you need to consider previous count of the denomination column, so you need to sum up the value of all the previous denomination column through the SUMPRODUCT function for a specific column, which contains two arrays, such as $B$3: B$3 used to specify the previous denomination column, $B4: B4 used to specify the number of denominations have been counted.

Here it should be noted that in the first array, the first cell range reference is absolute reference, the second cell range reference is mixed, so that you can lock a row, while the column will change.

=($A4-SUMPRODUCT($B$3:B$3,$B4:B4))/C$3

After that, the number of denominations in the current column is calculated by subtracting the original value from the denomination value returned by the SUMPRODUCT function for the previous columns and dividing it by the denomination value in the current column. Finally, the decimal part is removed by the INT function.

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 SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…

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)…

Calculate Number of Hours between Two Times

Calculating the difference between two times might be a valuable statistic for subsequent computations or averages, whether you’re producing a time sheet for staff or recording personal exercises. While Excel has a plethora of complex functions, including date and time functions, calculating time elapsed is not always obvious. If you’re calculating time differences of less than 24 hours, you may use the simple HOUR function; otherwise, you’ll need to create a new formula that accounts for longer time disparities. In this post we’ll calculate number of hours between two times.

Calculate number of hours between two times1

How to use Excel to determine time difference (elapsed time)

To begin, let’s look at how to easily compute elapsed time in Excel, that is, how to determine the difference between a starting and ending time. Additionally, as is often the case, there are several formulas for doing time computations. Which one to choose is determined on the size of your dataset and the precise nature of the outcome you are attempting to attain. Therefore, let us go through each approach one by one.

When There Are Less Than 24 Hours

Step 1: In the first cell, enter a time in the “hh:mm:ss” format and, if required, add “AM” or “PM” Enter the same information a second time in another cell using the same format. Excel will default to “AM” if you do not input “PM,” or “AM.”

Calculate number of hours between two times1

Step 2: Select both cells and then click the formatting window in the “Home” tab’s Number group. The formatting window’s title may read “General” or something else entirely. To format the cells as time, choose “Time

Calculate number of hours between two times1

Step 3:To begin, click an empty cell. Enter an equal sign followed by the unquoted phrase “hour” Substitute an open parenthesis for the preceding. After entering the later time, click the cell with the “-” symbol. Select the first cell that corresponds to the earlier time you entered. Replace the opening parenthesis with a closing parenthesis. Subtract the first time from the second time by pressing “Enter” For instance:

=HOUR(B2-B1)

Calculate number of hours between two times1

When 24 Hours or More

Step 1: In one cell, enter the date and time in the “mm/dd/yyy hh:mm:ss” format; in another cell, enter the date and time in the “mm/dd/yyy hh:mm:ss” format. If required, substitute “AM” or “PM” Separate the date, time, and AM or PM with spaces. For instance: 2022/5/10  9:48:00.

Step 2: Select both cells and then click the formatting window in the “Home” tab’s Number group. The formatting window’s title may read “General” or something else entirely. To open the Format Cells window, click “More Number Formats” Pick “Time” from the Category list on the left, and then navigate through the Type window’s choices to select a date and time format. Select “OK.”

 

Step 3: To begin, click an empty cell. Enter a “=” symbol followed by the unquoted word “INT“. Two open parentheses are required. Select the cell that contains the more recent time you entered, followed by a “” symbol. Select the first cell that corresponds to the earlier time you entered. Replace the opening parenthesis with a closing parenthesis. Without quotations, type “*24” and add the last parenthesis. “Enter.” the key. For instance:

=INT((E8-D8)*24)

Calculate number of hours between two times1

Related Functions


  • Excel HOUR Function
    The Excel HOUR function returns the hour of a time value. Or returns an integer value that represent the hour component of a given time. The syntax of the HOUR function is as below:=HOUR (serial_number) …
  • 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)…

Extract date from multiple cells containing both date and time

As if you have got a cell containing date along with the time in the same cell and you want to extract the date into a separate cell, then for one or two cells, you can extract the date manually from the cell, but if you have got multiple cells in excel containing both date and time in the same cell then doing it manually would be very tough you would get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting date from the multiple cells containing both date and time would become a piece of cake for you, as it is done in the following screenshot, So let’s dive into it:

General Formula:


The Following formula would help you out for extracting date from the multiple cells containing both date and time to another cell; in the above example, the formula is shown in the cell B2:

=INT(date_value)

Let’s See How This Formula Works:


You would definitely wonder that how excel efficiently and precisely extracts date from the multiple cells containing both date and time? Then here is the answer that the excel handles it by several numbers and fractional values, which means that Excel uses a pattern in which it denotes the dates as the several numbers and time to the fractional values.

For instance, suppose that both date and time  Feb 2, 2022, 19:00 PM are in the same cell, as now in Ms excel this date and time are represented in terms of numbers as “44594.7916666667, where the “44594” is the date portion, and the fractional part “.7916666667” is the time portion.

So if you want to extract date from the cells that include both the date and time, you can use the INT function to extract only the date part. The INT function returns only the decimal portion from each cell by excluding the fractional part, and you would get the date from each cell efficiently and precisely.

As by the aid of an example, you would understand it more clearly, so let suppose in A2 there are both date and time which is Feb 2, 2022, 19:00 PM, which is equivalent to the number 44594.7916666667 in MS Excel, so when you would use the INT function which is given below then it would return the date portion (44594):

=INT(A2)

extract date from date and time 1

Obviously, the fractional part or the time portion is discarded to get the date portion, but for that, be sure you are using a date format that does not include a time. Otherwise, according to the above example, you will see the time displayed as 19:00 PM instead of the date portion.

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)…

 

How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then we can get a list of days.

But for some special requirement, we just want to fill weekdays or weekends into a list, for example if weekdays exist in the list, weekends are excluded. So, we need to find a way to only insert weekdays or weekends simply. In this article, we will show you the ways to fill days into list per your requirement properly, we provide two ways to fill weekdays or weekends, by Auto Fill function or the formula.

Method 1: Auto Fill Weekdays in Excel


Step 1: Launch excel open a new worksheet, enter the start date into any cell, if you want to fill only weekdays into the list, please enter a weekday, then drag it down to the next cells till we get the end date in date list. For example, we want to fill weekdays from 9/1/2020 to 9/15/2020, then enter 9/1/2020 in any cell, then drag it down to auto fill days till we get 9/15/2020.

Step 2: You may notice that when we dragging the cell down to apply formula to other cells, Auto Fill Options icon is displayed and attached to the last cell. Click on Auto Fill Options icon to load all options. Verify that ‘Fill Weekdays’ option is included.

Step 3: Check on ‘Fill Weekdays’ option, verify that days are updated from previous 9/1/2020 to 9/21/2020 automatically, that’s because weekends are removed from the list and weekdays are filled instead.

Step 4: If you want to keep the end date as 9/15/2020 in your list, just remove the extra days from the list. Now date list with only weekdays is created properly.

Method 2: Auto Fill Weekends by Formula in Excel


You can see that there is no fill weekends option in Auto Fill Options list. So, we need to find another way to fill weekends only. Actually, you can through below formula to fill weekends properly.

Step 1: Launch excel open a new worksheet, enter the formula =TEXT(“09/05/2020″+INT(ROW(A1)/2)+(CEILING(ROW(A1)/2,1)-1)*6,”mm/dd/yyyy”) into any cell. As you want to fill only weekends into the list, please enter a weekend date into the formula, in this sample we enter 09/05/2020 as start date, you can also change date format ‘mm/dd/yyyy’ to another format.

Step 2: Then drag it down to the next cells to apply the formula. Till we get the end date in date list. For example, we want to fill all weekends in September, drag it down till weekend date from October occurs.

Step 3: Just remove the date from October, then weekends from September are listed.

Related Functions


  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • 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 Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
  • 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)

How to Stack Data from Multiple Columns into One Column in Excel

Are you tired of having your data spread out across multiple columns, making it difficult to analyze and understand? It can be overwhelming to have to constantly switch back and forth between columns, hunting for the information you need. This post will show you how to stack data from multiple columns to one column in Microsoft Excel Spreadsheet.

This article will introduce you the two methods. After reading the article below, you will find the two ways are verify simple and convenient to operate in excel.

In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function.

For example, see the initial table below:

Stack Data 1

And we want reverse data into one column and make it looks like:

Stack Data 2

Now we can follow below two methods to make it possible. Let’s start it.

1. Stack Data in Multiple Columns into One Column by Formula

Step 1: Select range A1 to F2 (you want to do stack), in Name Box, enter a valid name like Range, then click Enter.

Stack Data 3

Step 2: In any cell you want to locate the first cell of destination column, enter the formula

=INDEX(Range,1+INT((ROW(A1)-1)/COLUMNS(Range)),MOD(ROW(A1)-1+COLUMNS(Range),COLUMNS(Range))+1).

Please be aware that you have to replace ‘Range’ in this formula to your defined name in Name Box.

Stack Data 4

Step 3: Click Enter. Verify that ‘ID’ (the value in the first cell of selected range) is displayed properly.

Stack Data 5

Step 4: Drag the fill handle to fill I column. Verify that data in previous initial location is reversed to one column properly.

Stack Data 6

Note:

If you drag the fill handle to cells extend selected range cell number, error will be displayed in redundant cell.

Stack Data 7

2. Stack Data in Multiple Columns into One Column by VBA

Step 1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

Step 2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1:

Sub StackDataToOneColumn()
    Dim Rng1 As Range, Rng2 As Range, Rng As Range
    Dim RowIndex As Integer

    Set Rng1 = Application.Selection
    Set Rng1 = Application.InputBox("Select Range:", "StackDataToOneColumn",        Rng1.Address, Type:=8)
    Set Rng2 = Application.InputBox("Destination Column:", "StackDataToOneColumn", Type:=8)

    RowIndex = 0
    Application.ScreenUpdating = False

    For Each Rng In Rng1.Rows
        Rng.Copy
        Rng2.Offset(RowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        RowIndex = RowIndex + Rng.Columns.Count
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Step 3: Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Step 4: Click Developer->Macros to run Macro. Select ‘StackDataToOneColumn’ and click Run.

Stack Data 9

Step 5: Stack Data to One Column dialog pops up. Enter Select Range $A$1:$F$2. Click OK. In this step you can select the range you want to do stack.

Stack Data 10

Step 6: On Stack Data to One Column, enter Destination Column $I$1. Click OK. In this step you can select the first cell from destination range you want to save data.

Stack Data 11

Step 7: Click OK and check the result. Verify that data is displayed in one column properly. The behavior is as same as the result in method 1 step# 4.

Stack Data 12

3. Merge Cells into One in Excel

To merge or consolidate cells in Microsoft Excel, follow these steps:

Step 1: Select the cells you want to merge.

Stack Data from Multiple Columns into One Column in Excel20

Step 2: Right-click on the selected cells and choose “Merge Cells” from the context menu.

Step 3: Alternatively, you can click on the “Merge & Center” button in the “Alignment” section of the “Home” tab on the ribbon.

Stack Data from Multiple Columns into One Column in Excel21

4. Combine two columns in excel without losing data

To combine two columns in Microsoft Excel without losing data, you can use the following formula:

Step 1: In a new column C1, enter the following formula:

=A1 & B1
combine two columns in excel without losing data1

If you want to combine data from two or multiple cells in Excel, you can also use this formula.

Step 2: Press Enter to apply the formula.

combine two columns in excel without losing data2

Step 3: Drag the formula down to the end of the data in the columns.

combine two columns in excel without losing data3

The formula uses the & operator to concatenate the contents of two cells. In this example, the formula combines the contents of cells A2 and B2 into a single cell. By dragging the formula down, you can combine the contents of all the cells in the two columns.

You can also use the CONCATENATE function to combine columns, which works in a similar manner. The formula would look like this:

=CONCATENATE(A1, B1).
combine two columns in excel without losing data

5. Combine 2 Columns with a Space

If you want to combine two columns with a space between them, you can use the following formula:

=A1 & " " & B1

In this example, the formula combines the contents of cells A1 and B1 into a single cell, separated by a space. By dragging the formula down, you can combine the contents of all the cells in the two columns.

6. Append Columns in Excel

If you want to append two columns in Microsoft Excel, just follow these steps:

Step 1: Select the first column of data that you want to append.

excel append columns1

Step 2: Right-click on the selected cells and choose “Copy” from the context menu.

excel append columns1

Step 3: Select the second column of data that you want to append, and right-click on the first cell in the column.

excel append columns1

Step 4: Choose “Insert Copied Cells” from the context menu.

excel append columns1

This will insert a copy of the first column of data after the second column, effectively appending the two columns.

excel append columns1

7. Conclusion

Stacking data from multiple columns into one is a useful operation in Microsoft Excel when you need to combine and analyze data from different sources. The process can be easily achieved by using the built-in functions such as the & operator or the CONCATENATE function, which allow you to concatenate the contents of multiple cells into one.

Hope you can like this post.

8. Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel MOD function
    he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
  • 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 COLUMNS function
    The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….

How to List Only Whole Numbers From Decimal Numbers in Excel

This post will guide you how to filter only whole numbers from decimal numbers in a list in Excel. How do I filter cells with whole numbers or non-whole numbers in Excel.

List Only Whole Numbers


Assuming that you have a list of data in range A1:A6, which contain decimal numbers and whole numbers. And you want to filter only the whole numbers or all non-whole numbers from the given list of data in Excel. You need to create a helper column next to the given data or range, and then using a formula based on INT function to check if the given number is integer or not. If return True, it indicates that this number is an integer or whole number. Otherwise, it is a non-whole number.  Do the following steps:

#1 create a new column next to the numbers column, and type the following formula into cell B1.

=INT(A2)=A2

#2 press Enter key to apply this formula, and drag the AutoFill Handle down to other cells to apply this formula.

list only whole numbers 1

#3 select the helper column B, and go to DATA tab, click Filter button under Sort & Filter group. And one Filter icon added into the first cell in helper column.

list only whole numbers 2

#4 click filter icon in the Cell B1, and select TRUE or FALSE value as you what to filter out whole numbers or non-whole numbers. Click Ok button to apply for changes.

list only whole numbers 3

 

#5 you should see that all whole numbers have been filtered out . And if select FALSE option from the drop down list, and it will filtered out all decimal numbers.

list only whole numbers 4

Note: you can also use another formula based on the IF function and the MOD function to achieve the same result. Like this:

=IF(MOD(A1,1)<>0,0,A1)

Type this formula into Cell C2 in a help column, and press Enter key.

list only whole numbers5

You should see that all whole numbers have been listed. And all decimal number will be shown as 0.

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)…

How to Calculate Number of Weekends between Two Dates in Excel

This post will guide you how to count the number of weekends between two given dates in Excel. How to find out how many weekends are between two dates in Excel. How to count the number of Sundays between two dates with a formula in Excel.

Calculate the Number of Weekdays between Two Dates


Assuming that you have a list of data in range A1:B4, in which contain start dates and end dates. And you want to calculate the number of weekend days between start date and end date. How to do it. You can use a formula based on the NETWORKDAYS function and the DAYs function to calculate the number of weekends between two given dates.

Here is the formula that it will count the total number of weekend days between two given dates:

=DAYS(B2,A2)+1-NETWORKDAYS(A2,B2)

Type this formula into cell C2, and press Enter key on your keyboard, and then copy this formula from cell C2 to range C3:C4 to apply this formula.

calculate number of weekdays1

Let’s see how this formula works:

The NETWORKDAYS function try to calculate the number of working days (weekdays) between two dates, and the DAYS function will count the number of days between two dates. So you can use days number to subtract working days, then you will get the number of weekends days.

Calculate the Number of Sundays between Two Dates


If you only want to count the number of Sundays between two given dates in Excel, you can use an Excel formula based on the INT function and the WEEKDAY function. Like this:

=INT((WEEKDAY(B1- 1)-A2+B2)/7)

Type this formula into cell D2, and press Enter key on your keyboard.

calculate number of weekdays2

Note: if you want to count the number of other week numbers (1-7, 1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday, 7 is Saturday ) between two dates, such as: Saturday, you just need to replace the number 1 with 7 in the above formula. Like this:

=INT((WEEKDAY(B1- 7)-A2+B2)/7)

calculate number of weekdays3

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 WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
  • Excel DAYS function
    The Excel DAYS function returns the number of days between two dates.The syntax of the DAYS function is as below:= DAYS (end_date,start_date)…

 

How to Delete Numbers after Decimal Point in Excel

This post will guide you how to remove decimals without rounding in Excel. How do I delete numbers after decimal point with a formula in the given cell in Excel.

Deleting Numbers after Decimal Point


Assuming that you have a list of data in range B1:B5, in which contain decimal values. And you want to remove all decimal without rounding for a given cell in Excel. You can use the TRUNC function to achieve the result. Like this:

=TRUNC(B1,0)

Type this formula into a blank cell and press Enter key on your keyboard, and then drag the AutoFill Handle over to other cells to apply this formula.

delete numbers after decimal point1

You can also use another formula based on the INT function to achieve the same result of deleting numbers after decimal point in Excel. Like this:

=INT(B1)

delete numbers after decimal point2

 

 

 

Count the Number of Weekends between Two Date

This post will guide you how to count the number of weekends between two dates in Excel. How do I get the number of weekends between 2 dates with a formula in Excel. How to count weekend days between dates in Excel.

Count the Number of Weekends between Two dates


If you want to calculate the number of weekends between two dates, you need to create a formula based on the SUM function, the INT function and the WEEKDAY function. For example, you have two dates in Cell B2:C2, and the start date is in Cell B2, and the end date is in Cell C2. You can use the following formula to achieve the result.

=SUM(INT((WEEKDAY(B2-{1,7})+C2-B2)/7))

Type this formula into a blank cell and then press Enter key in your keyboard.

count number of weekend between two dates1

The number of Weekends between those two dates is calculated.

Or you can use another formula to achieve the same result. Type the following formula into a blank cell and press Enter key.

=C2-B2+1-NETWORKDAYS(B2,C2)

count number of weekend between two dates2

Related Functions


  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • 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 WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…

 

Split Dates into Separate Cells in Excel

This post will guide you how to split date and time into separate cells in Excel. How do I Separate dates and times into two separate cells in Excel. How to split a data into multiple cells in Excel. How to separate Date and time data into 2 different cells with a formula in Excel.

Split Dates into Separate Cells


Assuming that you have a list of data in a range B1:B4 that contain date and time data, and you want to split those date and time into two separate cells in Excel. How to achieve it. You just need to do the following steps:

#1 Select one range of cells that you want to put the date into it.

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

 

split dates into separate cells1

#3 switch to Number tab, select Date in the Category list box, and select one type as you need. Click OK button.

split dates into separate cells2

#4 select another range of cells that you want to put the time into it.

#5 right click on it, and select Format Cells from the popup menu list. And the Format Cells dialog will open. And select Time in the Category list box, and select one time type as you need. Click OK button.

split dates into separate cells3

#6 type the following formula into the date range, and then press Enter key, and drag the AutoFill Handle over other cells.

=INT(B1)

split dates into separate cells4

#7 type the following formula into the time range, and press Enter key, and then drag the AutoFill Handle over other cells.

=B1-C1

split dates into separate cells5

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)…

 

Generate All Possible Combinations of Two Lists in Excel

This post will guide you how to generate all possible combinations with a formula in excel. How do I create list with all possible combinations of two lists in Excel. How to list all combinations from two separate list or range in Excel.

Generate All Possible Combinations of Two Lists in Excel


Assuming that you have two list of data in different column, A and B, and you want to get a list of all possible combinations from those two list in Column A and B. How to achieve it.

You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function. Like this:

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

Type this formula into Cell F1, and then drag the AutoFill Handle down column F until you get cells that look empty.

generate combination list1

Note: if you want to use this formula into other cell, you need to change the absolute cell reference to that cell. Such as: form F1 to E1.

=IF(ROW()-ROW($E$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($E$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($E$1),COUNTA(B:B))+1))

generate combination list2

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel MOD function
    he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
  • 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 COUNTA function
    The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…

 

Create a Five Star Rating System

This post will guide you how to create a five star rating system in your current worksheet in excel. How do I change the five points system to five star rating in excel. How to use the conditional formatting function to create a five star rating system in excel.

Create a Five Star Rating System


The five star rating system is a very useful way of visualizing rating like you see on Amazon site.

For example, you have a list of data in your current worksheet and it contains rating values in each cell in rating column, and you want to create a five star rating system in your worksheet, just do the following steps:

#1 select the range of cells (C1:G1) and merging it as one cell, then type “Star Rating” text in this cell.

create five star rating1

#2 type the numbers from 1 to 5 into each cells of range H1:L1

create five star rating2

#3 type the following formula into the formula box of Cell C2, then drag the Auto Fill Handle over other cells (C2:G3) to apply this formula.

=IF($B2>=H$1,1,IF(INT($B2)=H$1-1,MOD($B2,1),0))

create five star rating3

#4 select the range of star rating (C2:G3), go to HOME tab, click Conditional Formatting command under Styles group, then select New Rule from the drop down list. The New Formatting Rule dialog will appear.

create five star rating system1

#5 click Format all cells based on their values from Select a Rule Type list. Choose Icon Sets from the Format Style, select 3 stars icon from Icon Style. Check Show Icon Only box. Then set a full star is shown if the value is greater than or equal to 1, and a half star if the value is greater than or equal to 0.5.

create five star rating4

#6 let’s see the result.

create five star rating5

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel MOD function
    he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
  • 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)…

Extract Date from a Date and Time

This post will guide you how to extract date part from a date with time values in Excel. How do I remove time part from a date in Excel. How to get only date part from the date with time values via Format cells feature or Excel function. How to extract date from a date and time with VBA code in Excel.

Extract date with Format Cells

You can use the Format cells feature to extract date from a date and time value, just do the following steps:

#1 select the cells from which you want to extract date

#2 right click on those cells, select Format Cells… menu from the drop down menu list. And the Format Cells window will appear.

extract date from date and time1

#3 switch to Number tab, choose Date category under Category: section. Then select one type of dates such as:

extract date from date and time2

#4 click OK button, you will see that the date has been extracted from each date and time value.

extract date from date and time3

 Extract date with Function

The date is represented as serial number and times are fractional values in Excel. So if you want to only display the date part, just using the INT function to remove time portion. Or you can also use the TRUNC function to discard the time portion.

Type the below formula in formula box:

=INT(B1)

extract date from date and time7

Or

=TRUNC(B1)

extract date from date and time8

Note: you should make sure the date with time values in one cell is a date format.

Extract date with VBA Code

You can also write an Excel VBA Marco to extract date portion from a date with time values in Excel. Just do the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

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.

extract date vba1

Sub extractDate()
    Set W = Application.Selection
    Set W = Application.InputBox("select cells of Range:", "extract date from a date and time", W.Address, Type:=8)
    For Each R In W
        R.Value = VBA.Int(R.Value)
    Next
    W.NumberFormat = "mm/dd/yyyy"
End Sub

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

extract date from date and time5

6# Select one range from which you want to extract date portion.

extract date from date and time6

#7 Let’s see the result:

extract date from date and time9


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 TRUNC function
    The Excel TRUNC function truncates a number to an integer by removing the fractional part of the number.The syntax of the TRUNC function is as below:=TRUNC (number, number_digits)…

Transpose Multiple Columns into One Column

This post will guide you how to transpose multiple columns into a single columns with multiple rows in Excel. How do I put data from multiple columns into one column with Excel formula; How to transpose columns into single column with VBA macro in excel.

Assuming that you have a data list in range B1:D4 contain 3 columns and you want to transpose them into a single column F. just following the below two ways.

Transpose Multiple Columns into One Column with Formula

You can use the following excel formula to transpose multiple columns that contain a range of data into a single column F:

#1 type the following formula in the formula box of cell F1, then press enter key.

=INDEX($B$1:$D$4,1+INT((ROW(B1)-1)/COLUMNS($B$1:$D$4)),MOD(ROW(B1)-1+COLUMNS($B$1:$D$4),COLUMNS($B$1:$D4))+1)

transpose multiple columns11

#2 select cell F1, then drag the Auto Fill Handler over other cells until all values in range B1:D4 are displayed.

#3 you will see that all the data in range B1:D4 has been transposed into single column F.

transpose multiple columns1

Transpose Multiple Columns into One Column with VBA Macro

You can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly. Just do the following steps:

#1 click on “Visual Basic” command under DEVELOPER Tab.

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.

transpose multiple columns2

Sub transposeColumns()
    Dim R1 As Range
    Dim R2 As Range
    Dim R3 As Range
    Dim RowN As Integer
    wTitle = "transpose multiple Columns"
    Set R1 = Application.Selection
    Set R1 = Application.InputBox("please select the Source data of Ranges:", wTitle, R1.Address, Type:=8)
    Set R2 = Application.InputBox("Select one destination single Cell or column:", wTitle, Type:=8)
    RowN = 0
    Application.ScreenUpdating = False
    For Each R3 In R1.Rows
        R3.Copy
        R2.Offset(RowN, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        RowN = RowN + R3.Columns.Count
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

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

transpose multiple columns3

#6 select the source data of ranges, such as: B1:D4

transpose multiple columns4

#7 select one single cell in the destination Column, such as: F1

transpose multiple columns5

#8 let’s see the last result.

transpose multiple columns6


Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • 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.  And the INT function rounds down, so if you provide a negative number, the returned value will become more negative.The syntax of the INT function is as below:= INT (number)…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel Columns function
    The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….
  • Excel MOD function
    he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

 

Excel TRUNC Function

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

Description

The Excel TRUNC function truncates a number to an integer by removing the fractional part of the number. So this function returns a truncated number based on a given number of digits in Excel.

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

The TRUNC 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 TRUNC function is as below:

=TRUNC (number, number_digits)

Where the TRUNC function arguments are:

  • number -This is a required argument. The number that you want to truncate.
  • number_digits – This is an optional argument. It will specify the number of decimal places to truncate the given number to. If this argument is omitted, and the TRUNC function will set number_digits argument as zero value by default.

Note:

  • If the number_digits argument is a positive value, the function will truncate the number of digits to the right of the decimal point.
  • If the number_digits argument is equal to 0, then the function will truncate the number to the nearest integer.
  • If the number_digits argument is a negative value, the function will truncate the number to the left of the decimal point.
  • The TRUNC and INT functions are very similar, they both can return the integer part of a given number. The TRUNC function will remove the fractional part of the number. And the INT function will round numbers down to the nearest integer based on the value of the fractional part of the number. With negative number, the TRUNC function and the INT function are different, as the returned result are different.

Excel TRUNC Function Examples

The below examples will show you how to use Excel TRUNC Function to truncate a given number to a specified number of decimal places in Excel.

1# to truncate a number 4.8 to return the integer part, enter the following formula in Cell B1.

=TRUNC(4.8)

excel trunc example1

2# to truncate a negative number -4.5 to return the integer part, enter the following formula in Cell B2.

=TRUNC(-4.5)

excel trunc example2

3# to truncate a number between 0 and 1 to return the integer part, enter the following formula in Cell B3.

=TRUNC(0.35)

excel trunc example3


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 INT Function

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

Description

The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.  And the INT function rounds down, so if you provide a negative number, the returned value will become more negative. It means that it will return the first negative number that is less than or equal to the original number. Such as, INT(-5.9), it will return -6.

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

The INT 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 INT function is as below:

= INT (number)

Where the INT function argument is:

  • number –This is a required argument. The real number that you want to round down to an integer.

Excel INT Function Examples

The below examples will show you how to use Excel INT Function to truncate a given number down to the nearest integer.

1# get the integer portion of a given number 7.6, enter the following formula in Cell B1.

=INT(7.6)

excel int example1

2# rounding a given negative -7.6 down to the nearest integer, enter the following formula in Cell B2.

=INT(-7.6)

excel int example2

3# get the decimal part of a positive number 10.4, enter the following formula in Cell B3.

=10.4-INT(10.4)

excel int example3


More Excel INT Formula Examples

  • Transpose Multiple Columns into One Column
    You can use the following excel formula to transpose multiple columns that contain a range of data into a single column, and you can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly…
  • Calculate Days, Weeks, Months and Years 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.How do I count the number of days, weeks, months and years between 2 dates in excel…
  • Extract Date from a Date and Time
    How do I remove time part from a date in Excel. How to get only date part from the date with time values via Format cells feature or Excel function. How to extract date from a date and time with VBA code in Excel…
  • Create a Five Star Rating System
    How do I change the five points system to five star rating in excel. How to use the conditional formatting function to create a five star rating system in excel…..
  • Generate All Possible Combinations of Two Lists
    You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function to get a list of all possible combinations from those two list….

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)…