Format cell to delete a portion of a cell text

[gingerbreadgrl]

Hi,

I am using a software program to export my data into excel, then using a macro to organize the data. Unfortunately, the software program only has the option to have both date AND time exported so the cells containing that data look like the following:

8/29/2019 4:00 PM

This occurs in just one column in the entire spreadsheet. Is there a way to format the cells in that column so that the time can be deleted and the date would be the only data left in the cell?

I anticipate having maybe 10 rows of data in that column so if it would be easier to just format the cell instead I could just copy and paste the code and change it for each cell too.

Thanks,
Gingerbreadgrl

 

[Michael M]

Highlight the cells and reformat as ShortDate....unless the dates are imported as text
In which case you use, in an adjacent column

=TRIM(LEFT(A1,FIND("",A1,10)))

 

[gingerbreadgrl]

Hi Michael,

Thanks for your response. The column format comes through as general and is probably text, when I highlight the column and format to short date the time still stays in the cell. I am very new to macros and vba coding, so I tried to put the code you suggested in a blank column to the right of the date column but I received an error. Also, the program that is pulling the data from the excel spreadsheet is very finicky about changes in the format of the workbook (e.g. new columns and changing column headings) so it would be ideal to have the time just delete (via the macro that already runs) and keep the column the same.

Although if I place the code you suggested in the column to the right of the date/time column and apply it to the cell with the column heading, which is "scheduled date of session" then "scheduled" appears in the cell. So it does work for text...

Thanks for your help!

 

[Michael M]

What error do you get ? ?
Did you put this formula on the same line as the date in column "A", then drag the formula down as far as required ?

 

[gingerbreadgrl]

I was getting a Value! error, but I tried again and it does not give me an error. But, it shows up like the following:

Column D
Schedule Date of Session
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]8/23/2019 4:00:00 PM[/FONT]

 

[gingerbreadgrl]

Hi,

Sorry I accidentally just sent that last message. Here is what the spreadsheet looks like with the formula:

Column D	Column E
Scheduled Date of Session	Scheduled
8/23/2019 4:00 PM	43700.6666
8/29/2019 9:00 PM	43706.875

<tbody>
</tbody>

When the data is exported to the spreadsheet the column heading is a general format and the rows say custom. I formatted both column d and column e to be a date but that still doesn't change the numbers in column e back to dates. Also, if it ends up working, is there a way to put it in a macro so that it happens automatically?

Thanks for your help!

 

[DanteAmor]

Try this to fix column D

Sub Format_Date()
  Dim c As Range, y, m, d
  For Each c In Range("D2", Range("D" & Rows.Count).End(xlUp))
    y = Mid(c, InStr(4, c, "/") + 1, 4)
    m = Left(c, InStr(1, c, "/") - 1)
    d = Replace(Mid(c, InStr(1, c, "/") + 1, 2), "/", "")
    c.Value = DateSerial(y, m, d)
  Next
End Sub

 

[Rick Rothstein]

Try this to fix column D

Sub Format_Date()
  Dim c As Range, y, m, d
  For Each c In Range("D2", Range("D" & Rows.Count).End(xlUp))
    y = Mid(c, InStr(4, c, "/") + 1, 4)
    m = Left(c, InStr(1, c, "/") - 1)
    d = Replace(Mid(c, InStr(1, c, "/") + 1, 2), "/", "")
    c.Value = DateSerial(y, m, d)
  Next
End Sub

This should do the same thing as your code does...

Sub Format_Date()
  Columns("D").Replace " *", "", xlPart, , , , False, False
End Sub

 

[DanteAmor]

This should do the same thing as your code does...

Sub Format_Date()
  Columns("D").Replace " *", "", xlPart, , , , False, False
End Sub

Hi Rick, it's very good, I wanted to go step by step to always get a date.

 

[gingerbreadgrl]

Hi,

Thanks so much for both of your responses. I should have mentioned that I have more than one sheet in the workbook. Rick, I put the sheet name in the code as the following:

Sheets("Clt Info").Columns("D").Replace " *", "", xlPart, , , , False, False

When I did that the date and time changed in the column to the following:

8/23/2019 0:00 is visible within the cell, when you click on the cell it shows 8/23/2019 12:00 AM

DanteAmor,

I wanted to try your code but because I was unable to designate the "Clt Info" sheet it produced an error, would you be able designate the "Clt Info" sheet within your code so I could try that out too?

Thanks so much to the both of you for your time and effort!!