Changing incorrect Dates to all look the same

[aweir2]

I have recieved a large amount of data to scrub, half of the dates were inputted incorrectly. When I go to format cell and change the date format no change is made as excel does not view it as a date.

The way the date is displayed is below. (The zero before the month is what I believe is throwing everything off).

Looking for a quick way to get the date to go from looking like this
20/02/2014 7:00:00

to a date that looks like this
20/2/2014 7:00:00 AM

 

[JRS1010]

The cells showing FALSE with ISNUMBER don't have a numeric value behind the date. They are Text not a "real" date.

The other thing you could try is but I suspect you will get a #VALUE error.

I converted the text to a numeric value by stripping out the different parts of the date, and then multiplying by one. Try the formula, it worked for me.

 

[Rick Rothstein]

I just gave that a try and still nothing some of the dates change, while others wont do anything at all. Below is an example of what I am working with. The dates without the 0 in front of month change just fine, while the other dates stay as they were.

Also if I manually change them, take out the 0 they still do not respond if I were to change them to a different format.

5/2/14 7:00

6/2/14 7:00

7/2/14 7:00

10/2/14 7:00

11/2/14 7:00

12/2/14 7:00

13/2/2014 7:00

14/2/2014 7:00

17/2/2014 7:00

18/02/2014 7:00:00

19/02/2014 7:00:00

20/02/2014 7:00:00

21/02/2014 7:00:00

24/02/2014 7:00:00

25/02/2014 7:00:00

<tbody>
</tbody>

I don't think the dates you say are working are really working correctly. Your dates appear to have been saved out, as Text, from a system using non-US (probably UK) settings where the day is listed first, then the month, then the year. The dates that are failing are the ones whose first number is larger than 12 because in the US system (where the month is assumed first), there is no such month as 13, 14, etc. and so they do not get converted. The problem with the ones that do get converted is the first number (lower than 13) is where a US system assumes the month should be, so it gets assumed to be the month. HOWEVER, given the date order of the dates that fail to convert, I would bet that first number should be the day... so if your US system is assuming what should be a day as a month, the converted dates will all be wrong (unless the month number and day number are identical). You are going to have to reverse those first two numbers if you want the dates to get converted correctly. Since it appears that you want to preserve the time value, I think the most effect way to proceed will be to use a macro to flip the first two numbers and then convert the resulting value to a date. Give me a couple of minutes to come up with that code for you.

 

[MARK858]

The cells showing FALSE with ISNUMBER don't have a numeric value behind the date. They are Text not a "real" date..

Having said that my data changed when I tried the formula with a cell as text in US format so I will be interested to see what happens (got a #VALUE error on a cell as text in UK format so hopefully the reverse is true).

Edit: I do agree with Rick as they look like UK format to me.

 

[JRS1010]

My original formula assumed all times from your data set were 7:00AM. The updated formula below does not make that assumption and will preserve the time.
=IF(ISNUMBER(A1),A1,CONCATENATE(LEFT(MID(A1,FIND("/",A1,1)+1,9999),FIND("/",MID(A1,FIND("/",A1,1)+1,9999))-1),"/",LEFT(A1,FIND("/",A1)-1),"/",MID(A1,FIND(" ",A1)-4,4)," ",RIGHT(A1,LEN(A1)-FIND(" ",A1))))*1

 

[Rick Rothstein]

I don't think the dates you say are working are really working correctly. Your dates appear to have been saved out, as Text, from a system using non-US (probably UK) settings where the day is listed first, then the month, then the year. The dates that are failing are the ones whose first number is larger than 12 because in the US system (where the month is assumed first), there is no such month as 13, 14, etc. and so they do not get converted. The problem with the ones that do get converted is the first number (lower than 13) is where a US system assumes the month should be, so it gets assumed to be the month. HOWEVER, given the date order of the dates that fail to convert, I would bet that first number should be the day... so if your US system is assuming what should be a day as a month, the converted dates will all be wrong (unless the month number and day number are identical). You are going to have to reverse those first two numbers if you want the dates to get converted correctly. Since it appears that you want to preserve the time value, I think the most effect way to proceed will be to use a macro to flip the first two numbers and then convert the resulting value to a date. Give me a couple of minutes to come up with that code for you.

Okay, assuming my analysis above is correct and you really are not getting the correct dates when a conversion takes place, then this macro should straighten all the dates out. Select all the cells containing your values and then run this macro...

Sub FixDates()
  Dim Cell As Range, Test As Variant, Parts() As String, Slashes() As String
  On Error Resume Next
  For Each Cell In Selection
    If IsEmpty(CLng(Cell.Value)) Then
      Parts = Split(Cell.Value, " ")
      Slashes = Split(Parts(0), "/")
      Parts(0) = Slashes(1) & "/" & Slashes(0) & "/" & Slashes(2)
      Cell.Offset(, 5).Value = CDate(Join(Parts, " "))
    Else
      Cell.Offset(, 5).Value = DateSerial(Year(Cell.Value), Day(Cell.Value), Month(Cell.Value)) + TimeValue(Cell.Value)
    End If
  Next
  On Error GoTo 0
End Sub

Once the dates have been fixed, delete the macro as you cannot run it against the cells that have already been fixed (doing so will reverse the correction and screw everything up).

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (FixDates) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

 

[aweir2]

The formula below worked for me (Excel 2007). It gets the numeric value behind the date (e.g., 41761.29167) and then you can format it however you want from there. Formula assumes your data starts in cell A1.

=IF(ISNUMBER(A1),A1,CONCATENATE(LEFT(MID(A1,FIND("/",A1,1)+1,9999),FIND("/",MID(A1,FIND("/",A1,1)+1,9999))-1),"/",LEFT(A1,FIND("/",A1)-1),"/",MID(A1,FIND(" ",A1)-4,4)," 7:00"))*1

Thanks for your help! The Formula works great, the only thing is there are different times depending when someone clocks-in, is there a way I can get it to pull the exact time the date/time is displaying versus all of them showing 7:00?

i.e

14/04/201 6:36:00

<tbody>
</tbody>

 

[JRS1010]

You're welcome, glad it worked for you. And yes, the following formula preserves the time:

=IF(ISNUMBER(A1),A1,CONCATENATE(LEFT(MID(A1,FIND("/",A1,1)+1,9999),FIND("/",MID(A1,FIND("/",A1,1)+1,9999))-1),"/",LEFT(A1,FIND("/",A1)-1),"/",MID(A1,FIND(" ",A1)-4,4)," ",RIGHT(A1,LEN(A1)-FIND(" ",A1))))*1

 

[aweir2]

The formula works great, except that it switches the day and month on the dates that do have a numeric value. The dates with and without numeric values are all mixed in with eachother, so the dates that I was able to format that read 6/4/2014 April 6th, reads as 4/6/2014 June 4th once the formula is applied. I applied the formula to the entire column. Is there a way to fix this without going through 1000s of rows determining whether or not to apply the formula

 

[aweir2]

Sorry, I have a correction

This is how the data that is non-numeric value look

16/01/2014 14:10:00

17/01/2014 15:14:00 when the formula is applied this how how they look after

<tbody>
</tbody>

1/16/14 14:10

1/17/14 15:14

<tbody>
</tbody>

The month is now the first number in the sequence
This is how the data that does have a numeric value looks before the formula (Month is represented in the middle, just like original non numeric value dates)

3/1/14 15:00

6/1/14 15:00

<tbody>
</tbody>

The dates after the formula applied appears the same way

3/1/14 15:00

6/1/14 15:00

<tbody>
</tbody>

however, when you format them to actually read the month they read as March and June vesus January (which is the correct month). So it reads the same, however excel is now taking the first number in the sequence as the date, as the formula adjusts the non numeric values this way.

 

[JRS1010]

Hmm...Can you paste a few rows of your data where this is happening. The formula is supposed to account for this.