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
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
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my OLD subtlety...![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")