Category: Excel Functions

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 Count Numbers with Leading Zeros

This post will guide you on how to count numbers with leading zeros in Excel 2013/2016/2019/365. When working with numbers that have leading zeros, it can be challenging to count the number of zeros that appear before the actual value. Fortunately, there are two main methods for counting leading zeros in Excel: using a formula or using VBA code.

In this post, we’ll walk through the steps for each method, so you can choose the one that works best for your needs.

1. Count Numbers with Leading Zeros using Formulas

Suppost you have a list of product ID in your worksheet, and the product ID contains the different number of leading zeros in the front of string. And if you use the COUNTIF function to count numbers with leading zeros, but it will remove those leading zeros, it means that 1289 and 001289 are the same strings.

So how to preserve the leading zeros while counting numbers. And you can use another Excel function named as SUMPRODUCT.

For example, you want to count strings with leading zeros in the range A2:A6, you can write down the following formula based on the SUMPRODUCT function.

=SUMPRODUCT(--($A$2:$A$6=A2))

You can enter this formula into Cell B2, then press Enter key. Then you can drag AutoFill Handle down to other cells to apply this formula.

count string with leading zero1

You can also use another excel array formula to count strings with leading zeros as follows:

=SUM(IF($A$2:$A$10=A2,1,0))
count string with leading zero2

2. Count Numbers with Leading Zeros using User Defined Function with VBA Code

Step1: press ALT+F11 to open the VBA editor.

Step2: Click on Insert -> Module to create a new module.

Step3: Copy and paste the following code into the module:

Function CountLeadingZeros_ExcelHow(cell As Range) As Long
    Dim count As Long
    Dim value As String
    value = cell.value
    count = 0
    While Left(value, 1) = "0" And Len(value) > 1
        count = count + 1
        value = Mid(value, 2)
    Wend
    If Left(value, 1) = "0" Then count = count + 1
    CountLeadingZeros_ExcelHow = count
End Function

The VBA code uses a While loop to count the number of leading zero characters in the specified cell. And it checks whether the leftmost character of the cell’s value is “0” and whether the length of the cell’s value is greater than 1.

If both conditions are true, it increments a count variable and removes the leftmost character from the cell’s value using the Mid function.

Step4: Save the module and go back to your Excel worksheet.

Step5: Type the following formula in a blank cell to count the number of leading zero characters in cell A2.

=CountLeadingZeros_ExcelHow(A2)

Step6: Press Enter to see the result.

How to Count Numbers with Leading Zeros vba 2.png

3. Video: Count Numbers with Leading Zeros

This video explains how to count numbers with leading zeros in Excel using a formula or VBA code. And it provides step-by-step instructions for both methods and demonstrates how to use them in practice.

4. Related Functions

  • 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],…)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • 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 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],…)…

5. Related Posts

  • How to insert leading zeros to number or text
    Assuming that you want to insert leading zeros  to number in Cell A2, then you can try to use the TEXT function to create an excel formula. You can also use the CONCATENATE function to add the specific digit of leading zeros into each number…
  • How to keep or remove leading zeros
    If you want to keep the leading zeros as you type in a cell, you need to change the cell format to Text before you type the zip code number.You can remove or delete leading zeros in front of the cells with excel VBA macro…
  • Count the number of words in a cell
    If you want to count the number of words in a single cell, you can create an excel formula based on the IF function, the LEN function, the TRIM function and the SUBSTITUTE function. ..

Excel XLOOKUP Function

Excel XLOOKUP Function was added into Excel as a beta feature in August 2019 and is now accessible exclusively in Microsoft 365. (as of July 2021). However, if you fall into this category and often deal with big sets of data in Excel, understanding this method is worthwhile. In this lesson, we will demonstrate how to use the XLOOKUP function to significantly simplify your data search.

What exactly is Excel XLOOKUP formula?

The Excel XLOOKUP function is a member of the family of lookup and reference functions. It is one of the most helpful features of Microsoft’s famous spreadsheet program. XLOOKUP is the simplest method for finding particular data items inside a cell range. The items included inside a previously set cell range are formatted. Does this sound familiar? The VLOOKUP function likewise operates on this concept. However, the more versatile Excel-XLOOKUP function allows you to look up not just one, but several items. Additionally, you may search for values vertically and horizontally inside your sheet.

In practice, what does this mean? Consider the following scenario: you have a digital client database in the form of an Excel file and are seeking for the address and phone number of a certain individual. With XLOOKUP, you can now search for related items by name and instantly see the needed information. It makes no difference if the sought data are included in a column, row, or table on another page. This indicates that the XLOOKUP function supersedes not only the VLOOKUP but also the HLOOKUP functions.

XLOOKUP syntax Function

The syntax of the XLOOKUP function is identical to those of VLOOKUP and HLOOKUP. If you’ve ever used them, you’ll appreciate how much more convenient XLOOKUP is. The following is the Excel syntax for the XLOOKUP function:

=XLOOKUP(lookup value, lookup array, return array, [if not found], [match mode], [search mode])

The XLOOKUP function accepts up to six parameters, the values of which are shown below.

  • lookup value (required): the value you’re looking for.
  • lookup array (required): the array in which the lookup value should be located.
  • return array (required): the array from which you want to retrieve and return the values when the lookup value is discovered.
  • [if not found] (optional): If no match is discovered, this value is returned.
  • [match mode] (optional): This option specifies the sort of match that you are searching for. There are several ways to specify it:
  • 0 – It searches for an exact match, and the result must precisely match the lookup array value. When not specified, it is also set as the default.
  • -1 – It searches for an exact match and then returns to the next lower value.
  • 1 – It searches for an exact match and then advances to the next bigger number.
  • 2 – It performs partial matches using wildcards, where the characters *,?, and have particular significance.

[search mode] (optional): Used to specify the lookup array’s XLOOKUP search mode. There are several ways to specify the same thing:

  • 1– Begin the search with the first item. When nothing is supplied, it is defaulted to default.
  • -1 – Conducts a reverse search, beginning with the last item.
  • 2 – Conducts a binary search in the lookup array in which the data must be sorted ascending. If the data is not properly organized, it may result in mistakes or incorrect outcomes.
  • –2 – Conducts a binary search in the lookup array, sorting the data in ascending order. If the data is not properly organized, it may result in mistakes or incorrect outcomes.

XLOOKUP’s Advantages and Disadvantages In Excel, the XLOOKUP function retains some of its benefits over VLOOKUP and INDEX/MATCH. However, it does have certain drawbacks.

The XLOOKUP Function’s Advantages

  1. It operates in both vertical and horizontal directions.
  2. Three inputs are required, rather than the four required by the VLOOKUP and INDEX MATCH methods.
  3. By default, an exact match is used.
  4. Utilize wildcards to conduct partial match lookups.
  5. Can execute descending order lookups.

INDEX MATCH now uses a single function rather than two.

The XLOOKUP Function’s Drawbacks

  1. Optional arguments may seem complicated to novices.
  2. Can take longer when two ranges are selected and the spreadsheet has an excessive number of cells.
  3. When the lookup and array results are not the same length, an error is returned.
  4. Both lookup and return ranges must be remembered.

How to Use Excel’s XLOOKUP Function

The XLOOKUP function is comparable to Excel’s LOOKUP function. XLOOKUP may be used by simply specifying the cell references for the function to act on.

Alternatively, you may utilize the top-level “Formula bar” box to input the XLOOKUP function syntax.

What makes XLOOKUP superior?

XLOOKUP simplifies and reduces the likelihood of errors in Excel’s most frequently used formulas. Simply type =XLOOKUP(what you're looking for, the list, the result list) and you’ll receive the result (or a #N/A error if the value is not found).

By default, this function looks for an exact match: One of the drawbacks of VLOOKUP is that you must provide FALSE as the last argument in order to get the right result. XLOOKUP corrects this by defaulting to precise matches. If desired, you may alter the lookup behavior using the match mode option.

The fourth parameter is used to accommodate the value not found situation. To suppress errors in the majority of business cases, we are compelled to encapsulate our lookup formulae with IFERROR or IFNA formulas. XLOOKUP has a fourth argument (described in further detail below) that allows you to choose the default output to use if your value is not found.

XLOOKUP includes extra parameters for searching for unusual circumstances. You may search from the top or bottom of a list, use wildcards, or use quicker alternatives for searching sorted lists.

It outputs a reference, not the value. While this may seem insignificant to casual Excel users, professional Excel users’ eyes light up when they find a formula that returns references. That is, you may mix XLOOKUP results with other formulae in novel ways.

It’s so much cooler to type; all you have to do is type =XL. I’m not sure whether this is a lucky coincidence, but saying =XL generates this formula.

Related Functions

  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • 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 MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

Excel UNIQUE Function

The guide demonstrates how to use the UNIQUE function and dynamic arrays in Excel to create unique values. You’ll learn how to use a basic formula to locate unique values inside a column or row, across many columns, and depending on criteria, among other things.

Excel 365’s introduction of the Excel UNIQUE formula altered everything! What was once considered rocket science becomes as simple as ABC. Now, you don’t need to be an expert in formulas to extract unique values from a range based on one or more criteria and organize the results alphabetically. All of this is accomplished via the use of straightforward formulae that anybody can understand and adapt to their own requirements.

Notes on usage

The Excel UNIQUE function returns a list of values that are unique inside a range or array. As a consequence, a dynamic array of unique values is created. If this array is the end output (i.e. it is not passed to another function), array contents “spill” onto the worksheet into a range that updates automatically when new unique values are added or deleted from the source range.

The UNIQUE function accepts three arguments: an array, a by col parameter, and an exactly once argument. The first parameter, array, specifies the array or range from which unique values should be extracted. This is the only argument that is necessary. The second input, by col, specifies whether unique data are extracted by rows or columns. UNIQUE extracts unique values from rows by default. Set by col to TRUE or 1 to compel UNIQUE to extract unique data by column. The last option, exactly once, specifies the behavior for values that occur many times. By default, UNIQUE extracts all unique values, regardless of their number of appearances in the array. Set exactly once to TRUE or 1 to retrieve unique values that exist just once in the array.

 

UNIQUE function in Excel

In Excel, the UNIQUE function produces a list of distinct values from a range or array. It works with any form of data, including text, numbers, dates, and timings.

The function falls within the category of Dynamic Arrays functions. As a consequence, a dynamic array is created that naturally overflows onto adjacent cells, either vertically or horizontally.

excel unique function1

The Excel UNIQUE function has the following syntax:

=UNIQUE(array, [by col], [once exactly])

Where:

  • Array (needed) – the range or array from which unique values are to be returned.
  • By col (optional) – a logical value specifying how data should be compared:
  • TRUE – does cross-column comparisons.
  • FALSE or omitted (default) – does cross-row comparisons.
  • Exactly once (optional) – a logical value defining which values are regarded as unique:
  • TRUE – yields values that only occur once, which corresponds to the database concept of uniqueness.
  • FALSE or omitted (default) – returns the range or array’s distinct (different) values.

 

Important note:

At the moment, the UNIQUE feature is only accessible in Microsoft Excel 365 and Excel 2021. Excel 2019, 2016, and older versions do not allow dynamic array formulae, and so do not have the UNIQUE function.

Related Functions

  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Excel SORT Function

The excel sort function is one of the most useful new features in Excel. It is one of many functions that make advantage of Excel’s new dynamic array calculation engine, which enables Excel to spill results from a single formula across numerous cells.

SORT function in Excel

The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.

SORT is a member of the family of dynamic array functions. As a consequence, a dynamic array is created that overflows vertically or horizontally onto nearby cells, depending on the form of the source array.

The SORT function has the following syntax:

=SORT(array, [sort index], [sort order], [by col])

Where:

  • Array (needed) – represents a collection of values or a range of cells to sort. These may be any kind of value, such as text, numbers, dates, or timings.
  • Sort index (optional) – an integer indicating the column or row from which to sort. If this parameter is missing, the default index 1 is used.
  • Sort order (optional) – specifies the order in which items are sorted:
  • 1 or omitted (default) – ascending order, that is, from smallest to greatest -1 – descending order, that is, from greatest to smallest
  • By col (optional) – a logical value indicating the sort direction:
  • FALSE or omitted (by default) – row-based sorting. This is the most often used option.
  • TRUE – column-based sorting. This option is appropriate if your data is structured horizontally in columns.

Notes on use

The excel sort formula uses a formula to organize the contents of a range or array in ascending or descending order. The outcome of SORT is a dynamic array of data that “spill” into a range on the worksheet. If the values in the source data change, the SORT result will immediately update.

The SORT function accepts four parameters: an array, a sort index, a sort order, and a by col parameter. The first option, array, specifies the array or range to sort. This is the only argument that is necessary. By default, the SORT method sorts data upward using the array’s first column. Utilize the optional sort index and sort order parameters to specify which column to sort by and in what order to sort (ascending or descending). Sort index should be an integer that corresponds to the sorting column (or row). For instance, to sort by the third column in a set of data, use the sort index 3 parameter. The optional sort order parameter specifies the direction of the sort. Ascending order is 1 and descending order is -1. The SORT function by default sorts data vertically by row. Set the fourth option, by col, to TRUE to sort a range horizontally by columns.

Excel sort formula – Tips and Tricks

Excel sort function is a novel dynamic array function, and as such, it has a few quirks worth noting:

Currently, only Microsoft 365 and Excel 2021 support the excel sort function. Because Excel 2019 and Excel 2016 do not support dynamic array formula, they do not have the SORT function.

If the array generated by a SORT formula is not provided to another function, Excel dynamically builds and populates a suitably sized range with the sorted data. As a result, always ensure that there are sufficient empty cells below or to the right of the cell where the formula is entered; otherwise, a #SPILL error occurs.

The findings are continuously updated in real time as the underlying data changes. The array given to the formula, on the other hand, does not immediately expand to accept additional elements inserted outside of the referred array. To include such things, you must either change the array reference in your formula, convert the source range to a table, or build a dynamic named range.

Excel’s SORT Function Constraints

The SORT function cannot be used to sort columns or rows that are not contiguous.

For instance, you cannot sort the employee names and wages separately since they are not adjacent.

However, you may sort by employee name and date of hire, or by employee name and date of hire and salary.

If you ever need to sort non-adjacent columns, you may use the approach outlined in this article.

If you wish to sort any range of cells according to a row or column outside that range, you cannot use the SORT function.

For instance, you cannot use the SORT function to sort just the employee names and joining dates by salary.

To resolve these sorts of issues, you may utilize Excel‘s SORTBY function. Alternatively, follow the process outlined in this article.

Arrange and Rank

In this article, we’ll examine one Dynamic Array Functions: SORT. It is excellent for showing a list in a ranked or chronological sequence (date, time).

In our example, we’re going to classify workers according to the amount of time they spend in the office (duration) over the course of a week. This will rank the workers from those who spent the greatest time at work to those who spent the least.

However, before we begin sorting, allow me to demonstrate quickly how I combined all of the duration periods for each employee using the SUMIF function.

The SORT Feature

Along with ordering the personnel from most to least hours worked, it would be beneficial to provide the matching hours next to each name. There are other methods to achieve this, however we will utilize the SORT function. This is another Dynamic Array Function that returns a wide range of values.

To use SORT, you only need to provide the range to sort and the order in which it should be sorted. (The SORT function’s second parameter, sort index, is not required in this case.)

Our straightforward formula would be as follows:

=SORT(B2:B8,,-1)

excel sort function1

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Excel TRANSPOSE Function

Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel. Its goal, as a component of the Excel lookup and reference functions, is to arrange data in the appropriate manner. To run the formula, pick the precise size of the transposed range and hit the CSE key (“Control+Shift+Enter“).

Description

The TRANSPOSE function in Microsoft Excel returns a transposed range of cells. For instance, if a vertical range is specified as a parameter, a horizontal range of cells is returned. Alternatively, if a horizontal range of cells is specified as a parameter, a vertical range of cells is returned.

Excel TRANSPOSE formula is an Excel built-in function that is classified as a Lookup/Reference Function. It may be used in Excel as a worksheet function (WS). The TRANSPOSE function is a worksheet function that may be used as part of a formula in a worksheet cell.

Syntax

The Excel TRANSPOSE Function in Microsoft Excel has the following syntax:

=TRANSLATE (range)

Arguments or Parameters:

range – The range of cells to transpose.

Returns

TRANSPOSE returns a range of cells that have been transposed.

Note:

The TRANSPOSE function requires an array as the range value. Enter a value and then press Ctrl+Shift+Enter to create an array. This will enclose the formula in brackets, indicating that it is an array.

Applicable To

 Office 365

Notes on use:

  • The TRANSPOSE function transforms a vertical range of cells to a horizontal range of cells, or vice versa. TRANSPOSE, in other terms, “flips” the orientation of a specified range or array:
  • TRANSPOSE translates a vertical range to a horizontal range when provided one.
  • TRANSPOSE transforms a horizontal range to a vertical range when provided one.
  • When an array is transposed, the first row becomes the new array’s first column, the second row becomes the new array’s second column, the third row becomes the new array’s third column, and so on.
  • TRANSPOSE is a function that works with both ranges and arrays. Ranges that have been transposed are dynamic. If the data in the source range changes, TRANSPOSE updates the data in the target range instantly.

Excel TRANSPOSE Function

The TRANSPOSE Excel function, as the name implies, may be used to transpose data in Excel.

The syntax is as follows:

=TRANSPOSE(array)

The array denotes a range of cells to be transposed in this case.

Take the following basic steps:

Step1: Choose the range of cells in which you wish the output data to be transposed.

Step2: TRANSPOSE is an array formula; hence, the precise amount of cells must be specified.

Step3: If the range of your table is 6×2, i.e., 6 rows and 2 columns, you must pick a 2×6 range for the transposed data, i.e., 2rows and 6 columns.

excel transpose function1

Step4: Insert the formula =TRANSPOSE (A1:F5)

excel transpose function1

Step5: Avoid pressing Enter! Ctrl+Shift+Enter is required to perform array formula.

Step6: It’s worth noting that transpose is an array formula. An array formula must be input by hitting the Ctrl+Shift+Enter key combination, which causes the formula bar to insert a set of braces around the formula.

Step7: This instructs Excel to print an array of data rather than a single cell.

Step8: Transposition of the data is now complete.

excel transpose function1

Converting Week Number to Date

This post will guide you how to convert a week number to a date with a formula in Excel. How do I calculate a date from a week number and a year with formula in Excel. Or how to get week number from a given date with formula in Excel.

Convert Week Number to Date

Assuming that you have a week number and a year number in your worksheet, and you want to get the start date and end date in that give week in that year. How to achieve it.

You need to create a formula based on the MAX function, the DATE function and the WEEKDAY function to convert week number to a date in Excel. Just do the following steps:

#1 Type the year number in Cell C1, and type the week number in Cell C2.

#2 Type this formula into the formula box of the Cell C3, then press Enter key in your keyboard. The Serial number of the start date is calculated in Cell C3.

=MAX(DATE(C1,1,1),DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),2)+(C2-1)*7+1)

convert week to date1

#3 Type the following formula into the formula box of the Cell C4, and then press Enter key in your keyboard. And the serial number of the end date is calculated in Cell C4.

=MIN(DATE(C1+1,1,0),DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),2)+C2*7)

convert week to date2

#4 select the cells C3:C4 and then go to HOME tab, click Number format list box, and select Short Date item. The serial number are changed to a standard date format.  Or you can also select Long Date format from the drop-down list of the Number format.

convert week to date3

Get Week Number From a Date

If you want to get the week number from a given date, then you can use the WEEKNUM function to quickly achieve it. Just type the formula into the Cell D3:

=WEEKNUM(C3,1)

convert week to date4

Related Functions

  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • 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 WEEKNUM function
    The Excel WEEKNUM function returns the week number of a specific date, and the returned value is ranging from 1 to 53.The syntax of the WEEKNUM function is as below:=WEEKNUM (serial_number,[return_type])…

 

Excel Vlookup From Another Workbook

This post will guide you how to lookup value from another workbook in Excel. How do I use VLOOKUP function across multiple workbooks in Excel. How to vlookup a value from another workbook with a formula in Excel.

Excel Vlookup From Another Workbook

Before we use the VLOOKUP function to look for a value in current workbook. Assuming that you have request that look for value across multiple workbook. How to achieve it. You can use the VLOOKUP function with a full reference to the other workbook. Like this formula:

=IF(ISNA(VLOOKUP(A1,[Book2.xlsx]Sheet1!$C$1:$C$2,1,FALSE)), "FALSE", "TRUE")

This formula will search the range of cells in Book1.xlsx workbook to look for values in range of cell C1:C2 in workbook Book2.xlsx. if found, return TRUE, otherwise, return FALSE.

The syntax for external references is:

[Book2.xlsx]Sheet1!$C$1:$C$2

The Book2.xlsx is the name of the external workbook.

Sheet1 is the name of the sheet of the external workbook.

!$C$1:$C$2 is the range of cells that you need to use in the external sheet.

Type this formula in a cell B1 in current worksheet and press Enter key.

vlookup from another worksheet

You will see that the VLOOKUP function will call the data from external workbook(book2.xlsx) and get the expected result in the column B.

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 VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • Excel ISNA function
    The Excel ISNA function used to check if a cell contains the #N/A error, if so, returns TRUE; otherwise, the ISNA function returns FALSE.The syntax of the ISNA function is as below:=ISNA(value)….

Excel DVARP Function

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

Description

The Excel DVARP Function will get the variance of a population based on the entire population of numbers in a column in a list or database based on a given criteria. And the DVARP function can be used to evaluate text values and logical values in references in Excel.

The DVARP function is a build-in function in Microsoft Excel and it is categorized as a Database Function.
The DVARP 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 DVARP function is as below:

= DVARP(database, field, criteria)

Where the DVARP function arguments are:

  • Database -This is a required argument. The range of cells that containing the database.
  • Field – This is a required argument. The column with database that you want the minimum of.
  • Criteria – The range of cells that contains the conditions that you specify.

Excel DVARP Function Examples

The below examples will show you how to use Excel DVARP Function to get the variance of a population based on a sample by using the numbers in a column in a list or database that match a given criteria.

#1 to get the variance in the cost of excel project in a range A1:C11, using the following formula:

=DVARP (A1:C11,C1,E1:E2)

 excel dvarp examples1

Related Functions

  • Excel DVAR Function
    The Excel DVAR Function will get the variance of a population based on a sample of numbers in a column in a list or database based on a given criteria.The syntax of the DVAR function is as below:= DVAR (database, field, criteria)…

Excel DVAR Function

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

Description

The Excel DVAR Function will get the variance of a population based on a sample of numbers in a column in a list or database based on a given criteria.

The DVAR function is a build-in function in Microsoft Excel and it is categorized as a Database Function.

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

= DVAR (database, field, criteria)

Where the DVAR function arguments are:

  • Database -This is a required argument. The range of cells that containing the database.
  • Field – This is a required argument. The column with database that you want the minimum of.
  • Criteria – The range of cells that contains the conditions that you specify.

Excel DVAR Function Examples

The below examples will show you how to use Excel DVAR Function to get the variance of a population based on a sample by using the numbers in a column in a list or database that match a given criteria.

#1 to get the sample variance of cost for the numbers in a range A1:C11,  using the following formula:

=DVAR(A1:C11,C1,E1:E2)

excel dvar examples1

Related Functions

  • Excel DVARP Function
    The Excel DVARP Function will get the variance of a population based on the entire population of numbers in a column in a list or database based on a given criteria. The syntax of the DVARP function is as below:= DVARP(database, field, criteria)…

Excel DSUM Function

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

Description

The Excel DSUM Function will add the numbers in a column or database that meets a given criteria. And so you can use the DSUM function to add the numbers in a field of records in a list or database that match a given conditions that you specify in Excel.

The DSUM function is a build-in function in Microsoft Excel and it is categorized as a Database Function.

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

= DSUM (database, field, criteria)

Where the DSUM function arguments are:

  • Database -This is a required argument. The range of cells that containing the database.
  • Field – This is a required argument. The column with database that you want the minimum of.
  • Criteria – The range of cells that contains the conditions that you specify.

Excel DSUM Function Examples

The below examples will show you how to use Excel DSUM Function to get the sum of values from a set of records that match a given criteria.

#1 to get the total cost from excel project and its cost is greater than 200, using the following formula:

=DSUM(A1:C10,C1,E1:F2)

 excel dsum examples1

Excel DSTDEVP Function

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

Description

The Excel DSTDEVP returns the standard deviation of a population based on the entire population of numbers in a column or database that match the given criteria.

The DSTDEVP function is a build-in function in Microsoft Excel and it is categorized as a Database Function.

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

=DSTDEVP(database, field, criteria)

Where the DSTDEVP function arguments are:

  • Database -This is a required argument. The range of cells that containing the database.
  • Field – This is a required argument. The column with database that you want the minimum of.
  • Criteria – The range of cells that contains the conditions that you specify.

Excel DSTDEVP Function Examples

The below examples will show you how to use Excel DSTDEVP Function to calculate the standard deviation of population that matching the given criteria you specify.

#1 =DSTDEVP(A1:C10,C1,E1:F2)

excel dstdevp examples1

Related Functions

  • Excel DSTDEV Function
    The Excel DSTDEV returns the standard deviation of a population based on a sample of numbers in a column or database that match the given criteria.The syntax of the DSTDEV function is as below:= DSTDEV (database, field, criteria)…

Excel DSTDEV Function

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

Description

The Excel DSTDEV returns the standard deviation of a population based on a sample of numbers in a column or database that match the given criteria.

The DSTDEV function is a build-in function in Microsoft Excel and it is categorized as a Database Function.
The DSTDEV 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 DSTDEV function is as below:

= DSTDEV (database, field, criteria)

Where the DSTDEV function arguments are:

  • Database -This is a required argument. The range of cells that containing the database.
  • Field – This is a required argument. The column with database that you want the minimum of.
  • Criteria – The range of cells that contains the conditions that you specify.

Excel DSTDEV Function Examples

The below examples will show you how to use Excel DSTDEV Function to calculate the sample standard deviation of a column in a database or list that matching the given criteria you specify.

#1 =DSTDEV(A1:C10,C1,E1:F2)

excel dstdev examples1

Related Functions

  • Excel DSTDEVP Function
    The Excel DSTDEVP returns the standard deviation of a population based on the entire population of numbers in a column or database that match the given criteria.The syntax of the DSTDEVP function is as below:=DSTDEVP(database, field, criteria)…

Excel DMIN Function

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

Description

The Excel DMIN returns the minimum value from a database or a column of a list that matches the specified conditions.

The DMIN function is a build-in function in Microsoft Excel and it is categorized as a Database Function.

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

=DMIN(database, field, criteria)

Where the DMIN function arguments are:

  • Database -This is a required argument.  The range of cells that containing the database.
  • Field – This is a required argument.  The column with database that you want the minimum of.
  • Criteria – The range of cells that contains the conditions that you specify.

Excel DMIN Function Examples

The below examples will show you how to use Excel DMIN Function to find the minimum value from a column of a list or database that matches a given criteria.

#1 = DMIN (A1:C10,C1,E1:F2)

excel dmin function example1

Note: The above excel formula will find excel project and its cost is greater than 200, then return the minimum one(300).

Related Functions

  • Excel Dmax Function
    The Excel DMAX returns the maximum value from a database or a column of a list that matches the specified conditions.The syntax of the DMAX function is as below:= DMAX (database, field, criteria)…

Excel DPRODUCT Function

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

Description

The Excel DPRODUCT returns the product of values from a set of records that match criteria that you specify.

The DPRODUCT function is a build-in function in Microsoft Excel and it is categorized as a Database Function.

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

=DPRODUCT(database, field, criteria)

Where the DPRODUCT function arguments are:

  • Database -This is a required argument. The range of cells that containing the database.
  • Field – This is a required argument. The column with database that you want the minimum of.
  • Criteria – The range of cells that contains the conditions that you specify.

Excel DPRODUCT Function Examples

The below examples will show you how to use Excel DPRODUCT Function to calculate the product of value from a column of a list or database that matches a given criteria.

#1 to get the product of the Cost from excel project with a cost is greater than 200, using the following formula:

= DPRODUCT(A1:C10,C1,E1:F2)

excel dproduct examples1

 

Excel XIRR Function

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

Description

The Excel XIRR function returns the internal rate of return for a series of cash flows that is not necessarily periodic. And if you want to get the internal rate of return for a series of periodic cash flows, you can use the IRR function in Excel.

The XIRR function is a build-in function in Microsoft Excel and it is categorized as a Financial Function.

The XIRR function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2011 for Mac.

Syntax

The syntax of the XIRR function is as below:

= XIRR (values, dates, [guess])

Where the XIRR function arguments are:

  • Values -This is a required argument. An array or cell reference that contain numbers for which you want to calculate the internal rate of return. And the values must include at least one positive value and one negative value.
  • Dates -This is a required argument. A series of dates that corresponds to the cash flow payments.
  • Guess – This is an optional argument. An initial guess at the internal rate of return. If it is omitted, and it will be set as 0.1 or 10%.

Note:

  • The values arguments must contain at least one positive value and one negative cash flow value, or the XIRR function will return the #NUM! Error.
  • If you provide an invalid date in the date argument, the XIRR function will return the #NUM! Error.

Excel XIRR Function Examples

The below examples will show you how to use Excel XIRR Function to calculate the internal rate of return for a series of cash flows.

#1 to get the internal rate of return, using the following formula:

=XIRR(A2:A5,B2:B5)

excel xirr examples1

Related Functions

  • Excel IRR Function
    The Excel IRR function returns the internal rate of return for a series of cash flows and the cash flows must be occurred at regular intervals (monthly or annually).The syntax of the IRR function is as below:=IRR(values, [guess])…

Related Examples

  • Calculate Stock Rate of Return
    You can try to calculate the rate of return by manually, or you use an Excel formula to achieve the result. The best way to calculate your rate of return is to use the EXCEL XIRR function,…

Excel VDB Function

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

Description

The Excel VDB function calculates the depreciation of an asset for a specified period based on the double declining balance method. And it will return a numeric value.

The VDB function is a build-in function in Microsoft Excel and it is categorized as a Financial Function.

The VDB function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2011 for Mac.

Syntax

The syntax of the VDB function is as below:

= VDB (cost, salvage, life, start_period, end_period, [factor], [no_switch])

Where the VDB function arguments are:

  • Cost -This is a required argument. The initial cost of the asset.
  • Salvage -This is a required argument. The value of the asset at the end of the depreciation.
  • Life -This is a required argument. The number of periods over which the asset is to be depreciated.
  • Start_period -This is a required argument. The starting period for which you want to calculate the depreciation.
  • End_period -This is a required argument. The ending period for which you want to calculate the depreication.
  • Factor -This is an optional argument. It is specified the rate of depreciation.
  • No_switch -This is a required argument. A logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation. And it can be either a value of TRUE or FALSE.
TRUE Using declining balance method of depreciation.
FALSE Using the straight-line depreciation method when the straight-line depreciation is greater than the declining balance depreciation amount.

Excel VDB Function Examples

The below examples will show you how to use Excel VDB Function to calculate the straight line depreciation of an asset for one period.

#1 to get the depreciation between the 7th and 12th month for an asset that cost 3000, with a salvage value of 500, and the useful life of the asset is 5, using the following formula:

=VDB(B1,B2,B3*12,7,12)

excel vdb examples1

Related Functions

  • Excel SLN Function
    The Excel SLN function calculates the depreciation of an asset for one period based on the straight line depreciation. And it will return a numeric value.The syntax of the SLN function is as below:= SLN (cost, salvage, life)…
  • Excel SYD Function
    The Excel SYD function calculates the sum-of-years’digits depreciation of an asset for a specified period. And it will return a numeric value.The syntax of the SYD function is as below:=SYD(cost, salvage, life, per)…

 

Excel SYD Function

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

Description

The Excel SYD function calculates the sum-of-years’digits depreciation of an asset for a specified period. And it will return a numeric value.

The SYD function is a build-in function in Microsoft Excel and it is categorized as a Financial Function.

The SYD function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2011 for Mac.

Syntax

The syntax of the SYD function is as below:

=SYD(cost, salvage, life, per)

Where the SYD function arguments are:

  • Cost -This is a required argument. The initial cost of the asset.
  • Salvage -This is a required argument. The value of the asset at the end of the depreciation.
  • Life -This is a required argument. The number of periods over which the asset is to be depreciated.
  • Per -This is a required argument. The period that you want to calculate the depreciation for.

Excel SYD Function Examples

The below examples will show you how to use Excel SYD Function to calculate the sum-of-years’digits depreciation for a period in the lifetime of an asset.

#1 to get the depreciation for the first year for an asset that cost 3000, with a salvage value of 500, and the useful life of the asset is 5, using the following formula:

=SYD(B1,B2,B3,1)

excel syd examples1

Related Functions

  • Excel SLN Function
    The Excel SLN function calculates the depreciation of an asset for one period based on the straight line depreciation. And it will return a numeric value.The syntax of the SLN function is as below:= SLN (cost, salvage, life)…
  • Excel VDB Function
    The Excel VDB function calculates the depreciation of an asset for a specified period based on the double declining balance method. And it will return a numeric value.The syntax of the VDB function is as below:= VDB (cost, salvage, life, start_period, end_period, [factor], [no_switch])…

Excel SLN Function

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

Description

The Excel SLN function calculates the depreciation of an asset for one period based on the straight line depreciation. And it will return a numeric value.

The SLN function is a build-in function in Microsoft Excel and it is categorized as a Financial Function.

The SLN function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2011 for Mac.

Syntax

The syntax of the SLN function is as below:

= SLN (cost, salvage, life)

Where the SLN function arguments are:

  • Cost -This is a required argument. The initial cost of the asset.
  • Salvage -This is a required argument. The value of the asset at the end of the depreciation.
  • Life -This is a required argument. The number of periods over which the asset is to be depreciated.

Excel SLN Function Examples

The below examples will show you how to use Excel SLN Function to calculate the straight line depreciation of an asset for one period.

#1 to get the depreciation for an asset that cost 3000, with a salvage value of 500, and the useful life of the asset is 5, using the following formula:

= SLN (B1,B2,B3,B4)

excel sln examples1

Related Functions

  • Excel SYD Function
    The Excel SYD function calculates the sum-of-years’digits depreciation of an asset for a specified period. And it will return a numeric value.The syntax of the SYD function is as below:=SYD(cost, salvage, life, per)…
  • Excel VDB Function
    The Excel VDB function calculates the depreciation of an asset for a specified period based on the double declining balance method. And it will return a numeric value.The syntax of the VDB function is as below:= VDB (cost, salvage, life, start_period, end_period, [factor], [no_switch])…

Excel PV Function

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

Description

The Excel PV function returns the present value of a loan or investment based on constant payments and a constant interest rate. So you can use the PV function to get the present value based on a series of future payments.

The PV function is a build-in function in Microsoft Excel and it is categorized as a Financial Function.

The PV function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2011 for Mac.

Syntax

The syntax of the PV function is as below:

= PV(rate,nper,pmt,[fv],[type])

Where the PV function arguments are:

  • Rate -This is a required argument. The interest rate per period.
  • nPer -This is a required argument. The total number of payments periods in an annuity.
  • Pmt – This is a required argument. The amount of the payment made each period.
  • Fv – This is an optional argument. The future value or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
  • Type – This is an optional argument. The number 0 (zero) or 1 and indicates when payments are due.
Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

 Note:

  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

Excel PV Function Examples

The below examples will show you how to use Excel PV Function to calculate the present value of an investment.

#1 to get the present value of an annuity with the terms in A2:A4, using the following formula:

=PV(B1,B2,B3,B4)

excel pv examples1

Related Functions

  • Excel FV Function
    The Excel FV function used to calculate the future value of an investment based on a constant interest rate. The syntax of the FV function is as below:=FV(rate,nper,pmt,[pv],[type])…
  • Excel IPMT Function
    The Excel IPMT function used to calculate the interest payment for an investment based on a constant payment schedule and a constant interest rage.The syntax of the IPMT function is as below:= IPMT (rate, per, nper, pv, [fv], [type])…
  • Excel IRR Function
    The Excel IRR function returns the internal rate of return for a series of cash flows and the cash flows must be occurred at regular intervals (monthly or annually).The syntax of the IRR function is as below:=IRR(values, [guess])…
  • Excel ISPMT Function
    The Excel ISPMT function used to calculate the interest paid during a specific period of an investment.The syntax of the ISPMT function is as below:= ISPMT (rate, per, nper, pv)…
  • Excel MIRR Function
    The Excel MIRR function returns the modified internal rate of return for a series of cash flows and the cash flows must be occurred at regular intervals (monthly or annually). The syntax of the MIRR function is as below:=MIRR(values, finance_rate,reinvest_rate)…
    Excel NPER Function
    The Excel NPER function returns the number of periods for an investment or loan based on periodic payment amount and a constant interest rate.The syntax of the NPER function is as below:= NPER (rate, pmt, pv, [fv], [type])…
  • Excel NPV Function
    The Excel NPV function returns the net present value of an investment by using a discount rate and a series of future cash flows (future payments and income).The syntax of the NPV function is as below:= NPV (rate, Value1,[value2],…)…
  • Excel PMT Function
    The Excel PMT function returns the payment amount for a loan or investment based on constant payments and a constant interest rate.The syntax of the PMT function is as below:= PMT(rate, nper, pv,[fv],[type])…
  • Excel PPMT Function
    The Excel PPMT function returns the payment amount on the principal for a given period for a loan or investment based on constant payments and a constant interest rate. The syntax of the PPMT function is as below:=PPMT(rate, per,nper, pv,[fv],[type])…
  • Excel RATE Function
    The Excel RATE function returns the interest rate per payment period of an annuity.The syntax of the RATE function is as below:=RATE(nper, pmt,pv,[fv],[type],[guess])…