×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

One way to do it:
In cell I2 enter: =LEFT(G2, 6) & ", " & RIGHT(G2, 4)
In cell J2 Enter: =DATEVALUE(I2)
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: =DATEVALUE(LEFT(G2, 6) & ", " & RIGHT(G2, 4))
Format column as Date

---- Andy

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

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

(OP)
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! ;)

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

Hi, Laurie,

Got a question for you about this statement...

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

(OP)
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

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

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...

Quote (Google)

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close