Excel 365: [SOLVED ]Data Dump Date: "Jul 12 20:27:14 CDT 2022" convert to 07/12/2022 2

[ladyck3]

Hello,
Its me again. I have a checkbook app that will export transactions into a *.cvs file but the date column is so contrived and the app (Android App: "Checkbook" by digitallifesoftware@gmail.com is no longer on Google Play, or supported. It is the most simple checkbook app I found back circa 1998. I've looked for a replacement to no avail as all of the apps I've found are not simple and cannot be configured as easily as this app can be done. There is an export option with no configurations it just dumps a *.csv file with the date being the issue for me).

Data Dump: Jul 12 20:27:14 CDT 2022
I am striving to convert this date format to 07/12/2022 straight up mm/dd/yyyy.

If you could please review the attached example that has been converted from *.csv to *.xlsx file the date/dump is highlighted in yellow. All of the columns show the manual configurations I go through to "fix" the date, like:

Example: Manually using REPLACE to remove the "day" names using "Sun " relace (and leave the replacement blank. This removes the day name.

From there I use =left( and =right( formulas to seperate the date and then =(concatenate to put it altogether.
Using (=mid confuses me but maybe there is just a way to use =mid to delete the "time" info) just a thought.
After the left/right/concatenate formulas, what has worked every time is using ASAP Utilities (ok I cheat) to convert the date to a number value and format using Date > Custom to configure it as 07/10/2023 format. Each column header on the attached sheet explains what I've done.

The exports/dumps that I have only go back so far, I've lost a lot when changing to newer phones. However I have CD backups (Remember those, pre-cloud and TB storage devices) that I can comb through for the older exports.

I have preformed these steps SO MANY times manually, I'm wondering if there is some sort of formula that can be used to split the default date dump so I can just lop off the "day" abbreviation from the mess, then split the date into separate cells so eventually I'm left with the stupid timestamp that I can just delete .. then work my magic with Concatenate.

Make sense? I know, I go the long way round and you are probably laughing now, I don't blame you Its just that I try to do for myself rather than having to jump on here and bother y'all. I'm screaming UNCLE after 8+ hours of merging many exports into one file and I'm sure I'll find MANY MORE. Is it possible this can be done easier?!

NOTE: This is not in TABLE format. I have issues with tables and formulas (not understanding). I usually will sort date alphabetically (to remove duplicates) then (for instance, use Column B to weed out Duplicates in Column A, as follows) use the formula =if(a2=a1,1,0) and filter showing only the "1" lines and delete but with tables I can't seem to make this work. It adds the table names and cloumn names and it turns out to all show as zeros and I KNOW that after adding 4000+ records multiple times to make sure all of the dates are reflected and I catch all of the data from these different dumps) the end result should have multiple dulicates to delete but they all show as zeros. That's when I said, ok, enough, saved it and went to bed.... sigh.

I LOVE EXCEL except when it bites me LOL... [Consider me bitten]

As always, thanks in advance....
Laurie

 

[Andrzejek]

One way to do it:
In cell I2 enter: [tt]=LEFT(G2, 6) & ", " & RIGHT(G2, 4)[/tt]
In cell J2 Enter: [tt]=DATEVALUE(I2)[/tt]
Select column J, Right-Click - Format Cells... Category: Date, Type: select how your date should be displayed.

Or, in one step:
In cell I2 enter: [tt]=DATEVALUE(LEFT(G2, 6) & ", " & RIGHT(G2, 4))[/tt]
Format column as Date

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[ladyck3]

ANDY, ANDY, ANDY!!!!! YOU ARE A GENIUS!

Then again, me, "notsomuch"

I tried to enter the formula but the spaces confused me, I was getting errors so I said, OK... Andy Knows! I copied the formula you posted into I2, VOILA, Entered the =datevalue in J2, formatted to mm/dd/yyyy. BINGO!!!! Double-clicked to copy down the columns and POOF!!!

HEARTS & STARS, HEARTS & STARS!! ​

Thanks you! <sigh> what a relief!

Laurie

UPDATE NOTE: I just saw the "one stepper" and PER-FECTION!

 

[SkipVought]

Hi, Laurie,

Got a question for you about this statement...

This is not in TABLE format. I have issues with tables and formulas (not understanding). I usually will sort date alphabetically (to remove duplicates)...

What duplicates are you removing and how are you removing them?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[ladyck3]

Skip,

I know for a fact there are duplicate records in this check register spreadsheet (part of what I'm doing with this inventory) as I've pasted several check register exports into one file, thinking I'd just use the =if( formula like always. =if(c3=c2,1,0)

But when I try to do this on a TABLE, it shows the following formula (I don't know the proper syntax in the tables) but it turns out all zeros. Here is what I came up with or how it looks when I tried to select the Cells for the formula. I insert an empty Column B and lable it "Dupes" then enter the following formula (The description column is "C" and sorted alphabetically:

=IF([@description]=C3=C2,1,0)​

Without a doubt this is wrong because I KNOW there are duplicates and rather than going through thousands of records one at a time to delete records which are duplicates..manually, how does one write it accurately since I'm not used to Title being or Table name (sometimes it happens to me, don't remember when) appear in a simple formula. Well simple in a regular workbook w/o tables.

I just played with it again and removed the [@description] part and did the formula as I'm used to doing and it worked. ARGH Sorry Skip...

Thanks Skip for asking..
Laurie

 

[SkipVought]

Assuming that row 1 is your Table Headings and column C heading is description, then

B2: =IF([@description]=C3,1,0)

Then filter to display 1s in column B and Delete the visible data rows.

You'll end up with a #REF! in your formula for each deleted row.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[ladyck3]

Thank you Skip,

I'm shutting down Excel for tonight but I've saved this formula and will use it... much appreciated. I'm learning and I appreciate the education

Have a great rest of your night...
Laurie

 

[Andrzejek]

Removing duplicate records with the formula is fine, but Excel can do that with the build-in functionality. Here is How to find and remove duplicates in Excel

Pretty much...

Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[ladyck3]

Skip,
It worked great, Duplicates are removed.

Andy,
I just saw your post about an internal option do remove duplicates. Thanks for the link, I'll grab the info and use it next time I run into this as a test.

Guys, thanks for coming through for me once again... you have no clue how much this means! (and I'll keep telling you this until I learn it all LOL {expect to hear it more} LOL...

Laurie