Which converts the text value in B5 "29/02/16" into a proper Excel date.
The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as follows:
The year value is extracted with with the RIGHT function:
Dates and times in Excel are stored as serial numbers and converted to human readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. In a similar way, the TEXT...
The DATE function creates a valid date using three arguments: year, month, and day: = DATE ( year , month , day ) In cell C6, we use the LEFT, MID, and RIGHT functions to extract each of these components from a text string, and feed the results into...
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.