Manipulating date values

Can you post a sample workbook?

There's an old trick that may work for you.

1) Highlight the column of dates (1 column only for this to work)

2) Select Data > Text To Columns > Next > Next

3) On the 3rd page of the wizard in the upper right corner is the Column Data Format options. Select Date: and choose the correct DMY, YMD, MDY, whatever from the drop down

4) Click Finish and this usually allows Excel to convert the text values to proper dates.

Thanks for the feedback. Here is a sample workbook.

The first tab shows an example of one log I get from a company, the column headers highlighted are those needed to populate the second sheet.

The second tab shows the format it has to be placed in to upload. These highlighted cells are also the ones that need to be populated from the sheet the station gives me.

Also, some are in 24 hour format, some are in AM/PM. I need it to be in 24 hour format.

Thanks!

Drop the data into sheet1 then run this macro:

Please Login or Register  to view this content.

You will have a sheet you can save a CSV file and it will have the data in the correct spots/format.

Ah, I couldn't for the life of me figure out where the values in the Air Time column came from....duh!

I've updated the macro above to fix that and include it.

The macro recorder is a great place to start.

You can use the recorder to create a macro that:

1) Does a global search and replace, search for ANPL and replace that with APL, in column H, yes?

2) Inserts a LEFT() formula in J2 taking the 13 characters from K2. Then copy that down, then copy the new values and PASTESPECIAL > VALUES over the original K values, then delete the formulas in column J.

I started learning macros by recording things, then posting the macros for "optimization", then learning by comparing what the pros did to my recorded macros to make them faster/better.