Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • How to get address of last cell in range in Excel
  • How to use Excel VLOOKUP Function
  • Create hyperlink with VLOOKUP in Excel
  • Multi-criteria lookup and transpose in Excel

Data Analysis Examples

  • How to create running total in an Excel Table
  • Excel Pie Chart
  • How to create Gauge Chart in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • How to sum a total in multiple Excel tables

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation require unique number
  • Excel Data validation must contain specific text
  • Excel Data validation unique values only
  • Excel Data validation don’t exceed total

Tag: TEXT function

TEXT function: Description, Usage, Syntax, Examples and Explanation

by

What is TEXT function in Excel? TEXT function is one of Microsoft Office Excel TEXT functions that lets you change the way a number appears by applying formatting to it with format codes. It’s useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text …

Continue Reading

How to join date and text together in Excel

by

To join a date with text, you can use concatenation with the TEXT function to control the date format.Generic formula =”text”&TEXT(date,format) Note: To control the date format, we use the TEXT function, which is designed to convert a number into text using a specified number format. Explanation  In the example shown, the formula in E4 is: …

Continue Reading

How to show last updated date stamp in Excel

by

To add a date stamp in a workbook to indicate a “date last updated”, you can use the TEXT function. Note:  The TEXT function can apply number formatting to numbers just like Excel’s built-in cell formats for dates, currency, fractions, and so on. However, unlike Excel’s cell formatting, the TEXT function works inside a formula …

Continue Reading

Pad week numbers with zeros in Excel

by

To pad week numbers (or any number) with zeros using a formula, you can use the TEXT function. Formula =TEXT(WEEKNUM(date,type),”00″) Explanation In the example show, D5 contains this formula: =TEXT(WEEKNUM(B5,21),”00″) Which returns the string “07”. How this formula works The TEXT function can apply number formats of any kind, including currency, date, percentage, etc. By …

Continue Reading

How to convert numbers to text using TEXT function in Excel

by

To convert numbers into text values, you can use the TEXT function. Normally, you want to maintain numeric values in Excel, because they can be used in formulas with other numbers. However, there are situations where converting numbers to text makes sense. For example, you might want to perform a lookup on numbers using wildcards, which …

Continue Reading

Convert date to Julian format in Excel

by

This tutorial shows how to Convert date to Julian format in Excel using example below. If you need to convert a date to a Julian date format in Excel, you can do so by building a formula that uses the TEXT, YEAR, and DATE functions. Formula =YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),”000″) Explanation Background “Julian date format” refers to a …

Continue Reading

Convert date to month and year in Excel

by

This tutorial shows how to Convert date to month and year in Excel using example below. To convert a normal Excel date into yyyymm format (e.g. 9/1/2017 > 201709), you can use the TEXT function. Formula =TEXT(date,”yyyymm”) Explanation In the example shown, the formula in C6 is: =TEXT(B6,”yyyymm”) How this formula works The TEXT function …

Continue Reading

Convert date to text in Excel

by

This tutorial shows how to Convert date to text in Excel using example below. If you need to convert dates to text (i.e. date to string conversion) , you can use the TEXT function. The TEXT function can use patterns like “dd/mm/yyyy”, “yyyy-mm-dd”, etc. to convert a valid date to a text value. See table below for …

Continue Reading

Create date range from two dates in Excel

by

This tutorial shows how to Create date range from two dates in Excel using example below. To display a date range in one cell based on dates in different cells, you can use a formula based on the TEXT function. Formula =TEXT(date1,”format”)&” – “&TEXT(date2,”format”) Explanation In the example shown, the formula in cell E5 is: …

Continue Reading

Count dates in current month in Excel

by

This tutorial shows how to Count dates in current month in Excel using example below. To count dates in the current month, you can use a formula based on the COUNTIFS or SUMPRODUCT function as explained below. Formula =COUNTIFS(range,”>=”&EOMONTH(TODAY(),-1)+1, range,”<“&EOMONTH(TODAY(),0)+1) Explanation In the example shown above, the formula in E7 is: =COUNTIFS(dates,”>=”&EOMONTH(TODAY(), -1)+1,dates,”<“&EOMONTH(TODAY(),0)+1) Where “dates” …

Continue Reading

Get days, hours, and minutes between dates in Excel

by

To calculate and display the days, hours, and minutes between two dates, you can use the TEXT function with a little help from the INT function. Alternatively, you can adapt the formula using SUMPRODUCT. Formula =INT(end-start)&” days “&TEXT(end-start,”h”” hrs “”m”” mins “””) Explanation In the example shown, the formula in D5 is: =INT(C5-B5)&” days “&TEXT(C5-B5,”h”” hrs …

Continue Reading

Get day name from date in Excel

by

If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date or to convert the date into a day name, there are several options depending on your needs. Formula TEXT(B4,”dddd”) Explanation Do you just want to display the day name? If you only want to display a day name, you don’t need a …

Continue Reading

Get month name from date in Excel

by

If you need to get the month name (i.e. January, February, March, etc.) from a date, you have several options depending on your needs. Formula =TEXT(date,”mmmm”) Explanation To convert the date into a month name If you want to convert the date value to a text value, you can use the TEXT function with a custom number …

Continue Reading

Convert Numbers to Text in Excel

by

By default, numbers are right-aligned and text is left-aligned in Excel. This example teaches you how to convert numbers to ‘text strings that represent numbers’. 1. Select the range A1:A4 and change the number format  to Text. 2. Precede a number by an apostrophe and it will also be treated as text. 3a. If you add text to a number and …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation
  • How to check if cell contains one of many things in Excel
  • How to calculate Number of Instances in Excel
  • How to count specific characters in a cell in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Convert decimal hours to Excel time
  • Check If Two Dates are same month in Excel
  • Count day of week between dates in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment periods for loan in Excel
  • PRICEDISC function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles