To join a date with text you can use concatenation with the TEXT function to control the date format.

Text formula in excel date. The point is that other Excel date functions not always can recognize dates entered in the text format. 01Jan1900 is day one 02Jan1900 is day two and so on adding one to. Because the Excel DATEVALUE function converts a text date to a serial number you will have to make that number look like a date by applying the Date format to it as we discussed a moment ago.

Format It specifies which format you want to display your date. DATE Formula in Excel There are multiple numbers of data types in excel and Date is one of them. TEXT112017 -0809 dddd Sunday.

We use the TEXT Function to convert the dates by specifying the language ID in the format argument of the formula. TEXTTODAYMMDDYY Todays date in MMDDYY format like 031412 TEXTTODAYDDDD Todays day of the week like Monday TEXTNOWHMM AMPM Current time like 129 PM. Both workarounds are faster and require less effort.

So the formula to convert a text value to date is as simple as DATEVALUE A1 where A1 is a cell with a date stored as a text string. As noted above I recommend you first try the solutions below adding zero and using text to columns before you use a formula. Lets take an example with the date.

The number or the date value. Excels DATEVALUE function converts a data text into a numeric representation of a date. Date It represents the input dates from your worksheet.

In case you want to convert the current date to the text format you can use the Excel TEXT function in combination with the TODAY function that returns the current date for example. DATE2015 5 20 - returns a serial number corresponding to 20-May-2015. When Excel has evaluated a date value as text one option is to use a formula to parse the text into its components year month day and use these to make a date with the DATE function.