Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Move chart to a separate worksheet in Excel
  • Number and Text Filters Examples in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • How to create running total in an Excel Table
  • How to Use Solver Tool in Excel

References

  • How to get first row number in range in Excel
  • How to retrieve first match between two ranges in Excel
  • How to get relative column numbers in a range in Excel
  • To count total rows in a range in Excel
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation no punctuation
  • Excel Data validation exists in list
  • How To Create Drop-down List in Excel
  • Excel Data validation allow uppercase only

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

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

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

Logical Functions

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function example in Excel

Date Time

  • Convert date to Julian format in Excel
  • Extract date from a date and time in Excel
  • How to show last updated date stamp in Excel
  • Convert Excel time to decimal hours in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers at uneven intervals in Excel

General

  • Currency vs Accounting Format in Excel
  • AutoFit Column Width, AutoFit Row Height in Excel
  • Excel Default Templates
  • How to calculate profit margin percentage in Excel
  • Count cells that contain errors in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning