You are here

Search form

Convert a text value into a date in Excel

Sometimes you'll find yourself working with dates in an Excel spreadsheet that have been pasted or imported into Excel from another datasource. When that happens, Excel can treat those dates as text - in other words, they look like dates but don't behave like dates. For example you can't sort by date properly. This lesson looks at several ways you can convert a date which Excel is treating as text into a proper date value in Excel.

Use the DATEVALUE function to convert text to a date.

The DATEVALUE function takes a text value and tries to convert it into a date. Whether it succeeds will depend on the text value you are trying to convert.

Date problems often occur when you import data from a CSV file (which is a text file format) and Excel doesn't recognise the dates in the file so it imports them as text instead. Another scenario is when you copy and paste date values into Excel from another file format.

Two tell-tale signs that you have a date problem are as follows.

First, some or all of the dates will be left-aligned in the cell. Date values are normally right-aligned.

Second, you'll notice when you click on a cell containing a date that it contains a value such as '2011/05/08. That apostrophe before the date is used to indicates to Excel that the value in the cell is text value, regardless of what it looks like. This feature is sometimes useful, but not in the situation we're looking at here.

The quickest way to solve the problem, is to use the DATEVALUE function as follows:

You have the following scenario:

In a cell next to the cell you want to convert, enter this formula:

Note how the formula returns a number, 39092. This is the correct answer, but it has the wrong format.

Excel treats all dates as serial numbers. It then uses formatting to present the serial number as a date.

Normally, when you enter a date Excel recognises it as a date and formats it automatically. However, Excel actually stores the serial number the date represents.

Clearly, this didn't happen in this example, so you'll have to format it manually.

There are a number of ways to format a number as a date.

The method is to use the Number formatting dropdown list on the Home ribbon bar. You can choose from Short Date or Long Date, or you can choose the More Number Formats option and either choose a different date format or create a custom date format. We'll cover custom date formats in another lesson.

If you're using a Mac, the the only opNumber formatting dropdown only includes Date. To choose another date format, choose Custom...

Once you choose a date format, the date should be presented as expected. This example shows the number in our example formatted as a date:

And that's the result we needed to get to - a text value that looked like a date but didn't behave like one, converted into the correct date format.

Of course, going to all of this effort doesn't make sense when you only have a few dates that haven't converted properly. However, if you have a large spreadsheet (e.g. with hundreds of values) the DATEVALUE function can be a real time saver.

What happens when DATEVALUE doesn't work?

Sometimes, DATEVALUE just can't figure out how to convert the text value into a date. Usually it's because the text entered in the cell doesn't look like a date. Or, it could be that the text in the cell is actually a number that you know to be a date, but which Excel doesn't recognise as a date (or at least, doesn't recognise it for the date we know it to be).

Here's an example of a text value that looks like a date but which just doesn't convert with DATEVALUE:

20120111

It's not obvious to Excel that this is a date, so DATEVALUE doesn't know what to do with it - it will just return a #VALUE error instead.

Option One - Combine DATEVALUE with the TEXT function

However, in this example we know that this is a date. We can break it out into its constituent parts - the year is 2012, the month is January, and the day is 11. Given this, we can use the TEXT function to help us out.

The TEXT function converts a value into text. I know that sounds like the opposite of what what we're trying to do - but this method actually works very well.

The syntax of the TEXT function is this: =TEXT(value, format_text)

value is what we want to convert (or a cell containing that value)

format_text is the format it should be converted to.

In the example below, we're using a number that has been imported as a text value. Note that the TEXT function would also work if this had been imported as a number rather than as a text value:

This is what the TEXT function does to the text value in B7:

We're nearly there! The only problem we have now is that while the date looks right, it is now a text value.

You could solve this by combining the TEXT function and the DATEVALUE functions together:

The final step would then be to format the cell in B12 as a date, as we did earlier.

Option Two - A clever and obscure use of the TEXT function

Assuming the approach shown in Option One works in your particular example, Option Two will give you a better way of getting to the same answer.

As before, we're trying to convert this value to a date:

This time, we'll only use the TEXT function to get the answer we want:

As above, you can now format this number as a date.

You may well be trying to figure out how this formula manages to get the same result. Here's how:

When we used the two minus signs (-) in the formula, we forced Excel to use the output of the the TEXT function in a calculation.

To achieve this, Excel converted the text value into a number so it could complete the calculation without generating an error.

What we actually did was convert the text value that the TEXT function generated into a negative number, and then back into a positive number.

As you can see, this method is a quick and easy way to get the result we wanted, and achieves the same outcome without needing the DATEVALUE function.

Option Three - Splitting the text into its constituent parts and recombining them into a date

The final method you can use when DATEVALUE doesn't deliver what you want is also the most cumbersome - but potentially the most flexible. Once you know this method, there are all sorts of uses you can put it to that have nothing to do with dates. I've already covered it in another lesson, Extract text from a cell in Excel, but it's worth showing you how to apply the method in this specific scenario.

As before, we're trying to convert this text value to a date:

You can then use the LEFT, MID and RIGHT functions to extract the year, the month and the day from the text value. From there, you can use the DATE function to recombine them into something that Excel recognises as a date.

The LEFT function allows you to choose a specified number of characters from the left end (i.e. the beginning) of a text value.

The RIGHT function does the same as LEFT but starts from the right end of the text value.

The MID function allows you to choose a specified number of characters from a specified starting point within the cell. In fact, you can do what we need with just the MID function, but let's look at how we might use all three.

The DATE function takes a Year, a Month and a Day and combines them into a date (which Excel automatically formats as a date).

Here's what this method looks like when applied to our problem:

Let's break it out so you can see how it works:

The LEFT function extracted the first four characters (the Year) from the cell B7 (i.e. the first four characters in the cell)

The MID function extracted two characters (the Month) from B7 starting from the 5th character from the left

The RIGHT function extracted two characthers (the Day) from B7 starting from the right (i.e. the last two characters in the cell)

The DATE function combined the Year, Month and Day into a date, and then Excel formatted it for us.

Some points to consider with this solution:

You could have used the MID function in place of the LEFT and RIGHT functions had you wanted to. I leave that to you to figure out. I used all three functions in this example because the LEFT and RIGHT functions are sometimes very useful on their own, so I wanted you to see them in action.

If you have a value that includes a time as well as a date, you can use the TIME function to convert the time portion of the text into a valid time, and add the results together to get a valid date/time. There is an example of converting text to both a date and time in the comments below.

There are many, many scenarios where a formula that combines LEFT, RIGHT and MID will prove very handy. This has been just one of them.

A final word on working with Dates in Excel

Dates are always problematic, and Excel's treatment of them can often cause confusion. One thing I have not addressed in this lesson until now is the fact that the US uses a date format that is different to most of the rest of the world. Specifically:

The US refers to dates in the format "month, day, year"

The rest of the world (ROW) refers to dates in the format "day, month, year"

That means that when you're working with dates in Excel, you need to know which date format your computer is set up to use (Excel takes its lead from the computer's date settings). Otherwise you can get some strange behaviour - and errors - showing up in Excel.

This example shows how confusion can arise.

The date 04/05/2013 in the US refers to the 5th of April, 2013.

In the ROW, it refers to the 4th of May, 2013.

When sharing a spreadsheet with a colleage that uses a different date format to you, Excel will automatically switch the dates around so they appear in the correct format. But if you're working with a system that, unbeknown to you, is using a different format, you can get into all sorts of trouble.

This example shows how things can get even worse:

The date 04/14/2013 in the US refers to the 14th of April, 2013.

For the ROW, typing this into Excel would generate an error since there are only 12 months in the year, not 14.

On a day to day basis, most Excel power users are used to this problem and normally see it coming. But even the best of us can have a moment of confusion when a formula we tried to write comes back with the wrong answer or an error, before we realise what the cause is, and move on. In practical terms, when using the methods shown in this lesson for converting text values into dates, be aware that how you construct your formulas may depend on the date format being used in your copy of Excel.

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.

The DATEVALUE function is supposed to ignore time values. You'll find that removing the time value from your example doesn't solve the problem.

Question - can you confirm whether the quote marks are part of the value of the cell?

If they are, then they appear to be breaking the DATEVALUE function. I'm not sure why.

One solution you might consider is selecting just the date cells in your spreadsheet and doing a Find and Replace for the " character. By selecting only the cells you want, the Find/Replace will only operate on those cells. Removing the " character will convert the value into a date and you won't need a formula to help you.

However, I also found that this formula would do the trick, assuming that you've got the quote symbol (") at the start and end of the date values you're trying to convert:

=DATEVALUE(MID(A1,2,LEN(A1)-2))

This example assumes that the value I'm trying to convert to a date is in cell A1.

Essentially, the MID function takes the characters out from between the " characters, leaving you with just the raw date. The result is still considered by Excel to be a text value, though, so you need the DATEVALUE function as well.

If you have a value, 13.10.2003, in a cell, then Excel will see it as a text value rather than a numeric value. This is because there are two decimal points in the cell.

There are two ways to solve your problem of converting this text value into a date:

1. Use Excel's Find and Replace feature.

This approach works well for a one-time conversion:

Select all the cells containing the dates you want to convert

Perform a Find and Replace where you search for "." and replace it with "/".

This will instantly convert all of the values into valid dates.

2. Use DATEVALUE, LEFT, MID and RIGHT to convert a text string that looks like a date into an actual date

This option is more involved, and uses a version of Option Three in the lesson above to convert it to a date.

Let's assume that you've typed this value, 13.10.2003, into A1, and then you want to convert it into a date using a formula in B1.

The formula you would enter in B1 is this:

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,4))

Here's how this breaks down:

The LEFT function takes the first two characters in A1

The MID function takes two characters from the middle of A1, starting with the 4th character in A1

The RIGHT function takes the last two characters in A1

We use the & symbol to concatenate those values into a new text string.

The DATEVALUE function then converts that text string into a date.

The result is a value in B1 that contains a date. You can now use Excel's date formatting tools to format it how you wish.

Note that this solution will only work on numbers in the specific format you supplied. It will not work if you want to convert a column of dates with values like this:

12.10.2003 - this will work with the formula supplied

13.10.2003 - this will work too

4.11.2004 - this won't work. The LEFT function will retrieve "4." instead of just 4, because it's looking for two characters.

15.1.2004 - this won't work either. The MID function will retrieve "1." instead of just 1.

Note that it is possible to create a version of the formula I supplied that uses the FIND and LEN functions to figure out where the decimal points are, and how which characters to select in the LEFT, MID and RIGHT functions, although that function will end up being very long and complicated. Check out this comment below and my reply to see an example.

i read the whole topic but that couldn't solve my issue. i have a CSV with the date format like this "30.09.2013 09:36:14.213" I need to change it to "2013.09.30 09:36:14.213"
the custom format for it is gonna be "yyyy.mm.dd hh:mm:ss.ds"

Then, format the cell with a custom date format. Excel offers Date and Time functions as standard, but not a combined Date and Time function so we have to create our own:

The Home tab in the toolbar has a Number section.

Inside that is a drop-down option to choose from different number formats (the default is General).

Choose Custom... to bring up the Number formatting dialog.

Choose Custom from the Category list.

In the Type box, replace what's there with this: yyyy.mm.dd hh:mm:ss.000

The date value in A2 should now be formatted as a date plus time, like this: 2013.09.30 09:36:14.213.

Hopefully you can see how the value in cell A2 matches the custom format we applied in the previous step. In particular, note that the number of zeros at the end controls how many decimal places to show.

Here's a quick explanation of the functions I used:

DATEVALUE takes a text value that looks like a date and turns it into a date.

LEFT takes the first x text characters from the start of a text value (the text in A1, in this case)

MID takes x characters from inside the string, once supplied with a starting point. The first time this formula uses it, it selects 2 characters starting at position 4 inside the string. The second time it's used, FIND is used to determine the starting point.

The RIGHT function takes x characters starting from the end of the text string and working back. It needs to know how many characters to take, which is where the LEN and FIND functions.

The LEN function calculates the number of characters in the string of text.

FIND looks for the first instance of a character and returns its position inside the string. In this case, the formula uses FIND twice:

inside a MID function to locate the first (and only) space and subtract 4 from that number. The MID function then selects 4 characters from that point, which gives us the year value.

inside a RIGHT function, in combination with the LEN function, to determine how many characters to select. By finding the length of the text string with LEN, then subtracting the position of the space inside the text, we then know how many characters the RIGHT function should take.

i did what u told me but it give value error in A2. I copy the text date to A1 and copy paste the formula u provided into A2 when I press enter it gives value error.
is there any chance that I can send u the file itself so u can work on it directly ? it seems it is beyond my experience of excel

If you edit the cell and then remove everything after the last closing bracked, the formula should work OK. I've fixed this problem but it will take some time before the website reflects the fix. If that still doesn't work, try manually typing the formula into the cell.

Try to replace the "," (coma) with ";" in the formulas. This was the issue on my side.

@David,
I am glad I have found this place, as I am strugling with a report taken from CSV. As you rightly pointed out excel didn't manage to recognize the date & tine in my case and actually no wonder as the dates given in the CSV are not perfectly formated.

These are the data I get form the CSV:
Column A: Received Column B: Resolved
A2: 5/22/2013 2:17:46 PM B2: 10/24/2013 11:52:57 AM

What I am trying to do is to convert the above text to date and time (preferably to 24h format if possible). I am stuch on this one now. I have trid to adopt your solutions but no luck so far - I will keep tryiong but meanwhile maybe you could help me out?

The next step will be to could the time tifference between the two cells, ie how many days/hours has passed since A2 to B2.

I tried putting the two dates you supplied into a spreadsheet. The only issue I can see is that the dates are formatted for the US, i.e. month/day rather than day/month, which is the default format in my copy of Excel. Once I converted the dates to day/month format I was able to calculate the time between A2 and B2.

5/22/2013 2:17:46 PM becomes 22/5/2013 2:17:46 PM

10/24/2013 11:52:57 AM becomes 24/10/2013 11:52:57 AM

When subtracting A2 from B2 I get 154.89943287037, i.e. the number of days between them. Note that the value after the decimal point allows you to calculate the number of hours that have passed on the 155th day:

=MOD(B2-A2,1)*24

This formula returns a value of 21.5863888888853. It uses the MOD function to find only the value after the decimal point, and multiplies it to get the number of hours (it should be less than 24, since the value after the decimal point is a fraction of a day).

This brings me back to the original problem - what to do if you are importing a set of dates from a CSV or text file that are in a different format to the date formatting you are using.

The simplest way to do this is simply to change the date system in your version of Excel by changing the regional settings on your computer. You can then import the CSV file, after which you convert your computer back to your normal regional settings. Because Excel has imported the CSV and recognised the values as dates, it will now be storing the values as valid dates, and changing the regional settings back won't affect them.

As usually the simplest ways usually work best! I did as you recomended, changed my regional settings to US, imported, converted from m-d-yyyy to d-m-yyyy and it works just great!!!

I did also notice that while importing data you ge to a point where you can define what a certain column will contain - one of the options gives date and it is possible to apply formatting. I havent checked that yet but I will soon.

well... i noticed that sentence after bracket first time I copy paste the formula and removed it right away but still got value error. i manually typed it as well but no difference.
can u give me ur email address so I can send u the file ?

Tried everything in this chain so far, to no avail. Any ideas?
dont know if this matters, but my computer I bought in Singapore and am now in the U.S. (i think my dates are in a diff format due to geographical difference)

I am glad I have found this place, as I am strugling with a report taken from CSV. As you rightly pointed out excel didn't manage to recognize the date & tine in my case and actually no wonder as the dates given in the CSV are not perfectly formated.

These are the data I get form the CSV:
Column A: Received Column B: Resolved
A2: 5/22/2013 2:17:46 PM B2: 10/24/2013 11:52:57 AM

What I am trying to do is to convert the above text to date and time (preferably to 24h format if possible). I am stuch on this one now. I have trid to adopt your solutions but no luck so far - I will keep tryiong but meanwhile maybe you could help me out?

The next step will be to could the time tifference between the two cells, ie how many days/hours has passed since A2 to B2.

I've a bunch of data that is format "280.605.890" which actually should have been a number "280605.890" . Do you have any idea how can I convert that data number in to the format I want? Please let me know, if you can help! Thanks!

If all numbers are in this format, i.e. xxx.yyy.zzz, then try this formula. Assume the number you want to convert is in cell A1:

=VALUE(LEFT(A1,3)&RIGHT(A1,6))

This will take the first three characters from the text string (the LEFT function does this), and the last 6 characters (the RIGHT function does this), and join them together in a new text string (this is called concatenation: the & symbol joins the result of the LEFT function to the result of the RIGHT function to create a new string). The VALUE function then converts the text value to a string (which it can do since the string is now a valid number).

If the number of digits in each group changes, you'd have to use the FIND function to work out where the first '.' is, and use that result as the second argument in the LEFT function. The function would then look like this:

=VALUE(LEFT(A1,FIND(".",A1)-1)&RIGHT(A1,6))

In this case, the FIND function would determine that the '.' is at position 4. We then have to subtract 1 to so the FIND function gets the characters up to but not including the '.'

From there, the only other complication I can see is the possibility that there are more than two decimal points inside the number. If that happens, you'd need to use the MID and LEN functions as well, to help identify the position of the additional decimal points. You can read more about the LEFT, RIGHT, FIND and MID functions here, and the LEN function here.

If you are trying to change the way a valid date is presented, then you can simply change the format of the date. You need to select the cells and apply a custom date format that looks like this:

This examples shows the date example you gave with a custom format applied:

dd means show the day in 2 digits (so if the date was the 5th of December, it would appear as 05)

mm means show the month in 2 digits

yyyy means show the year in 4 digits.

Note that you could put this combination of dd, mm and yyyy in any order that you want, such as mmddyyyy. Excel will automatically use the underlying date value to show it correctly. You can also use any combination of other characters within the format, as shown in the two options directly below my custom format type in the example above.

Another thing worth noting is that you can use 1 letter, 2 letters, 3 letters or 4 letters. For example:

Using d will return 12, or 5 on the fifth

Using dd returns 12 or 05

Using ddd returns the first three letters of the day (Thu in your example)

Using dddd returns the full name of the day (Thursday)

This also works with the month value (m, mm, mmm, mmmm). It also works with the year value, but only yy (13) and yyyy (2013) are worth using.

This opens the potential for a date format such as this:

dddd, dd mmmm, yyyy

Thursday, 12 December, 2013

The great thing about this is it's only a format, so the date stored in the cell doesn't have to change in any way.

I need to convert a column to short date format. A few of the cells are already in date format, some cells are empty and if I put a 0 in them they convert to the date of 1/0/1900 showing that they are also in date format. But most cells have data looking like 11-15-2012. I've tried highlighting the column and highlighting individual cells and changing the number format, but nothing happens. I've tried the datevalue formula but get #VALUE instead. I've tried option 3 above on cell O9 with 11-15-2012 in it. [=DATE(LEFT(O9,2),MID(O9,4,2),RIGHT(O9,4))] gives me 9/2/1917. I can convert the text in cell O9 to a date if I double click in the cell, but I've got over 14,000 rows and I refuse to double click in all of them. Can you help me?

I can reproduce the scenarios you've described but I can't explain them. The only thought that crossed my mind is that your example is in US date formats, whilst I'm using Excel with British date formats set on my computer, but that doesn't seem to fit with the results we're both getting.

However, I think I can solve the problem for you. Try this:

=DATEVALUE(LEFT(O9,2)&"/"&MID(O9,4,2)&"/"&RIGHT(O9,4))

The DATEVALUE function takes a text value and converts it to a date, provided the text string is presented as a valid date.

The formula I've supplied takes each date component from the value in O9 and concatenates them into a string where each date component is separated by a /

As I noted above, the date in O9 (11-15-2012), is in US date format (mm-dd-yyyy) whereas I'm using dd-mm-yy. So the formula given above doesn't work for me - I have to swap the day and month components around. My version of that formula looks like this:

Hello! I hate to do this, but I read your document and comments section, which is very informative, but cannot seem to come up witha solution to my problem. I think my situation is unique, but not impossible. Who knows.

I have a fairly large doc full of data pulled from an export. The problem is there is a field for dates, and the system I guess put in the dates like this:

Mar 1 2011
Mar 10 2011

And I need to change these into an actual date format usable by Excel. I thought at first I could just use the Format Cells function, but I guess it cannot read this text as a date properly. I noticed, the data is a bit odd. Each entry has the abbreviated month, and then either one or two spaces after depending if the day of the month has one char or two. The other problem seems to be the abbreviated months.

I've read through the article and comments but haven't come across a solution. My CSV file has the dates written like this: April 1, 2010 3:04:12 PM EDT
I need to eliminate the timestamp completely. Trying to format the cells as just a date doesn't work - it doesn't change them to look like a date. It stubbornly refuses to change when I put the file in to XLS format.

I really need this timestamp to go away as it is interfering with my ability to import this data into another program.

That's the format all of the dates are in. Interestingly enough when I delete the text in the cell and type in the date and time by hand it converts it to a number. When I select any cell the exact same format (in text form) shows in the formula bar. When I attempt to format the cell the display never changes. it's like nothing every happened to the formatting.

I have over 2,000 records to import into my other program and it will NOT import the data if the timestamp remains in the cells. I'll upload a sample XLS file so you can see what I mean.

This uses the DATE function as described in the main lesson body above, and then uses the FIND, MID and LEFT functions to pluck out the bits of the date we want.

If you're trying to adapt this for your own use, you may need to tweak the FIND function. I've used the FIND function to identify where a specific pattern in the text appears so I can using that position to start the extraction of different pieces of the date.

The solution is complicated by the fact that Excel doesn't know what to do with "April" inside the DATE function, so we use the MONTH function to convert April into 4, which Excel is then able to use in the DATE function.

This uses the DATE function as described in Option 3 in the lesson above and combines it with the TIME function which works in a very similar way to convert text into a time value. Since dates and times are just numbers in Excel, you are able to add them together to get a valid result.

Be warned that you'll also need to apply a custom date format, otherwise the cell may only display the date and not the time. You can apply this custom date format to see both date and time in the cell at the same time:

Thank you for the detailed post, it was very informative as well as the comments and reply. I didn't find a solution to my problem though. The date is in a APR-04-2014 and I would like it in a 4/4/2014 format. None of the formulas seem to be working.

If the cell is date format change it in what type of date format is convinient for you. If I presume that A1 cell is text format end contents "06-APR-12" then use in B1 cell formula =datevalue(A1) and the result will be 41005. Now format cell B1 date with the format that you wish. If you want to replace the content of A1 with B1 then from B1 copy to C1 paste value, delete B1 content and after that from C1 move to A1 and format A1 date with the format that you wish. Don't forget to format B1 and C1 cell to general.

Step 1: Extracted the month, date and year into individual cells using RIGHT, MID, LEFT formulas.
Step 2: Entered Month in text and their respective numerical values into a separate sheet (for instance, Jan 1, Feb 2, thru Dec 12 - "Jan" and "1" must be in individual cells, so on and so forth)
Step 3: Used VLOOKUP in to the first sheet (referencing the second sheet mentioned in step 2) to convert the months into their respective numerical value.
Step 4: Used CONCATENATE into a final column using the information from Step 1 and Step 3.

This is great, however my problem is more complex. I have thousands of dates and not enough time to clean them! They are in the formats 1/02/2014 and 01/02/2014 and 1/2/2014 and just can't be read in as dates (grrr!)

The RIGHT, MID and LEFT functions are not that helpful as I will then need to look at each one individually. I might as well just retype the date. How do i force it to read the day before the first / and so on?

Well I solved this one. If anyone needs it; I wrote nested ISERROR commands for each variation (2 digits behind the first slash, 1 digit, etc). If a slash goes into a date it returns an error so it checks the next option until it finds the right one.

Then for the random ones with odd variations I used conditional formatting to find them and fixed them manually (beats checking them all!)

Hi there, I have dates which looks like 08-10-12 (dd-mm-yy) which is written in general format. when i convert it to date format it changes day to month, which means instead of showing the month of october it shows august for the above given date, how can i solve this problem to convert thousands of such dates from general to date format???

I am trying to separate the date and time in my excel worksheet. The data was imported from another database and so it is showing in text format.

the date and time shows as follows:
08/25/2014 - 05:23

I tried using the formula =--TEXT(LEFT(A3,5),"000000000") but the value that is being returned is 08/25/2015. It doesn't matter if I change the year in the original data, the returning data is always 2015. I don't understand what step I am missing.

Hello. I am trying to combine a "date column" with a "time column" so i have my data on the format: YYYY-MM-DD hh:mm:ss and it not being a text format. The reason for this is that in my next step i want to subtract 1h from the date/time column i have created, which wont work if it has a text format and unless they aren't combined i cant subtract time to my knowledge.

I have an unknown combination of data that will be entered into a cell that I want to strip out if it is there. For example:

(DE) Smith, John
( DE) Smith, John
( DE )Smith, John
DE Smith, John

I will always want to remove the following values (, DE, and ) if they exist in the cell, but want to keep the Smith, John. I've used the following formula
=RIGHT(C21,LEN(C21)-FIND(")",C21))
This works great when there is a closing ), but in some cases the cell won't have the closing ) - such as my last example above. Is there a way to accommodate for the removal of any combination of the examples above while still leaving the rest of the person's name in this case?

assuming your example value is in cell A1, a way to get the value your system exported into a date and time format for excel is as follows:
=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))
formatting said value into yyyy/mm/dd and hh:mm:ss can be done with the ribbon [number format]/[custom] and entering "yyyy/mm/dd hh:mm:ss" without quotes in the type field.
This of course will make your time format a 24 hour format (13:24:29) unless you add am/pm to the format, then it will display (01:24:29 PM) -- like below...

Or you can display it as a text with:
=TEXT(DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2)),"yyyy/mm/dd hh:mm:ss AM/PM")

I am exporting data from a database which formats a date in the following manner:
Sep 11 2015 1:50PM
but I need to convert it to the following format to import into another database:
09/11/15 or 091115

Is there a formula I can use to automate this converting? As I'm importing hundreds of line items with varying dates I am not versed well enough in Excel to figure out a better way to do this than manually replacing.

This solution is more complex than yours - you can probably simplify it by replacing the FIND functions with LEFT() and RIGHT() functions. You can read more about those functions in our lesson on extracting text from data in Excel.

I am trying to convert a messy table to an excel spreadsheet for importing into a wordpress site.

I have attached a copy of the data as an example

I need to do a few things:
1) Extract the surname into its own cell
2) Extract the first name into its own cell
3) Extract address from second cell
4) Remove date
5) Remove registered
6) Remove 'civil ceremonies'
7) Extract phone number
8) Extract suburb and state to own cell

I don't know if this is remotely possible but would appreciate the help

Sir Elton John is my favourite musician of the world. IвЂ™m very glad to present for you this tour schedule 2019. Check [url=http://eltonjohntour2019.com]Elton John farewell concerts 2019[/url] website to get your tickets for the last Elton John tour in 2019.