LO Calc refusing to format dates

Questions about applications and software
Forum rules
Before you post read how to get help. Topics in this forum are automatically closed 6 months after creation.
Post Reply
User avatar
AZgl1800
Level 20
Level 20
Posts: 11223
Joined: Thu Dec 31, 2015 3:20 am
Location: Oklahoma where the wind comes Sweeping down the Plains
Contact:

LO Calc refusing to format dates

Post by AZgl1800 »

I am having trouble with Importing spreadsheets from Discover Card,
and merging their data into my spreadsheet

I usually just enter 4/8 and Calc displays 4/8/24
I do a Copy form Discover card and Paste into my Spreadsheet

Discover Card formats as 4/8/2024
no big deal, as long as, you don't try to sort the column.

I am trying to Reconcile expenses from 3 different sources, and the date formats are will nilly.

when sorted, it ignores the Actual Dates.

typical:

dates.jpg




very aggravating, as the Sequence of Events is Critical for what I am doing.

I tried to reverse the order to " fix it "

2024/4/07

but Calc ignores that too.
LM21.3 Cinnamon ASUS FX705GM | Donate to Mint https://www.patreon.com/linux_mint
Image
User avatar
Dan-cer
Level 6
Level 6
Posts: 1235
Joined: Fri Jul 29, 2022 4:56 pm
Location: Germany, Lower Saxony

Re: LO Calc refusing to format dates

Post by Dan-cer »

Interesting. I can't confirm that at the moment. Whatever format I insert data in, LO standardizes it. I have written data in completely different formats in a text editor for this purpose.

Could you upload an example file?
Which version of LO are you using?
How you get better results when searching for yourself.
Having problems with translate into English? Use Deepl Translator 1-click-button in editor - available for Firefox and chromium-based browsers.
Shiva
Level 3
Level 3
Posts: 144
Joined: Thu Jul 07, 2022 11:25 am

Re: LO Calc refusing to format dates

Post by Shiva »

AZgl1800 wrote: Mon Apr 08, 2024 1:13 am I am having trouble with Importing spreadsheets from Discover Card,
and merging their data into my spreadsheet
Hi,

You may check what format LO Calc is setting to your Discover Card dates and to its own and see if they match.

To do so, right-click on an LO date and choose last option (english translation should be Format cells or something alike). It will give you the current cell format (and possibly change it).
Do the same with your DC dates and see if they are considered as dates (not text). If so, adjust the cell format to the same as the LO Calc. Then try sorting again.

(Using LO 7.4.7.2 & LMDE6)
User avatar
AZgl1800
Level 20
Level 20
Posts: 11223
Joined: Thu Dec 31, 2015 3:20 am
Location: Oklahoma where the wind comes Sweeping down the Plains
Contact:

Re: LO Calc refusing to format dates

Post by AZgl1800 »

I stripped the file of personal info,
just left the dates and description, and sanitized that also.

what is interesting, even though I scroll to each Date Cell individually, and force it to the desired Date Format, it does not change the order in how it gets sorted, or will it accept the new date format.

e.g. from 2/10 to 2024/02/10 the reformat is ignored.

forMint.ods
(23.31 KiB) Downloaded 20 times
LOversion.jpg
LM21.3 Cinnamon ASUS FX705GM | Donate to Mint https://www.patreon.com/linux_mint
Image
User avatar
AZgl1800
Level 20
Level 20
Posts: 11223
Joined: Thu Dec 31, 2015 3:20 am
Location: Oklahoma where the wind comes Sweeping down the Plains
Contact:

Re: LO Calc refusing to format dates

Post by AZgl1800 »

it appears to me,
that the only way to fix this

is to create a brand new date Column, and Enter each Date MANUALLY.

that will then force the Date Order as I have specified.

that will burn up a lot of time... the file is large, I just uploaded a small segment of it.
LM21.3 Cinnamon ASUS FX705GM | Donate to Mint https://www.patreon.com/linux_mint
Image
User avatar
shedyed
Level 4
Level 4
Posts: 321
Joined: Wed Feb 03, 2021 5:12 pm

Re: LO Calc refusing to format dates

Post by shedyed »

You may not be aware of another format which comes in handy for date operations, but it can get tricky, or unwieldy,or both. It's called julian, if memory serves, where the first of the year would be 001, the last day would be 365 (366 for leap years).

I remember setting up a hidden column that s paired with the dates, and each date would have an invisible julian counterpart in this column. Sort it any which way that you prefer, and this is as close to bullet proof as it can get (at least, arithmetically). The other way, as mentioned above, is to use text.
Shiva
Level 3
Level 3
Posts: 144
Joined: Thu Jul 07, 2022 11:25 am

Re: LO Calc refusing to format dates

Post by Shiva »

AZgl1800 wrote: Mon Apr 08, 2024 7:38 am it appears to me,
that the only way to fix this

is to create a brand new date Column, and Enter each Date MANUALLY.

that will then force the Date Order as I have specified.

that will burn up a lot of time... the file is large, I just uploaded a small segment of it.
As I suspected, some dates are not considered as dates but text.

The solving process of your forMint.ods file is :
- unify the date format (DD/MM/YY seems the most used in your sheet) of the B06:B64 cell zone
- sort the A06:D64 cell zone with the B column as main criteria, and C column as second criteria (if several ops match the same day they will be sorted alphabetically too)
- the dates considered as strings will be found at the end, beginning with 02/10/2024 Dinosaur Tire Service In Price Ut
(you'll get forMint01.ods)

- copy the dates not considered as so (B32:B64) somewhere else on your sheet (in forMint02.ods I pasted them in the G column)
- convert them into dates : Select G32:G64 zone, open Data tab, then Text in Columns. You will find your data below with Standard as title. Click on it to make it appear in the column type and change it to Date(MJA).
(see forMint03.ods)
Attachments
forMint03.ods
(20.83 KiB) Downloaded 13 times
forMint02.ods
(20.75 KiB) Downloaded 13 times
forMint01.ods
(20.57 KiB) Downloaded 14 times
Last edited by Shiva on Mon Apr 08, 2024 10:04 am, edited 1 time in total.
Shiva
Level 3
Level 3
Posts: 144
Joined: Thu Jul 07, 2022 11:25 am

Re: LO Calc refusing to format dates

Post by Shiva »

(next)
- select the G32:G64 zone and paste it over the B32:B64 zone ignoring the alert message.
(forMint04.ods)

- sort the A06:D64 cell zone again with the same criterias as the first time.
(forMint05.ods)
Attachments
forMint05.ods
(21.91 KiB) Downloaded 12 times
forMint04.ods
(20.83 KiB) Downloaded 15 times
User avatar
AZgl1800
Level 20
Level 20
Posts: 11223
Joined: Thu Dec 31, 2015 3:20 am
Location: Oklahoma where the wind comes Sweeping down the Plains
Contact:

Re: LO Calc refusing to format dates

Post by AZgl1800 »

Well,
bass ackerds, but the Fix is to always save the Discover Card as a *.csv file, then it imports correctly
.
csv file.jpg
LM21.3 Cinnamon ASUS FX705GM | Donate to Mint https://www.patreon.com/linux_mint
Image
Post Reply

Return to “Software & Applications”