Question about importing dates

[Rellwood]

Library Thing has an export Excell sheet. I am trying to import it into Calibre to match books and dates,etc. The problem I am facing is that Library Thing formats their dates as yyyy-mm-dd even if I have it showing differently on the site. When I uploaded my Goodreads Library.csv they had their dates formatted as d/m/yyyy so a lot of my LT read dates are wrong.

Should I create a text column to hold the import date perfectly as it is, then create a date column with dd MMM yyyy and do some formatting of the date to copy it over? How will I retain wrong dates? LT has no problem showing dates that are wildly incorrect and impossible, so it won't be possible to copy those over, but being able to spot them will be difficult. I want to compare the column side by side with the correct dates in Calibre.


The goal is to import a date column formatted yyyy-mm-dd and is unable to be formatted differently in excel through their date format function(due to the impossible dates I think). I want to maintain dates if they are wrong or impossible so I can locate them and change them. However, I want to be able to read that column in the dd MMM yyyy format that Calibre uses so I can compare correct dates with imported dates. I don't want Calibre to misinterpret the dates being imported and change them to what they "should" be, or not import them at all because they might be impossible.

Suggestions?

[ownedbycats]

Originally Posted by Rellwood

Should I create a text column to hold the import date perfectly as it is, then create a date column with dd MMM yyyy and do some formatting of the date to copy it over? How will I retain wrong dates? LT has no problem showing dates that are wildly incorrect and impossible, so it won't be possible to copy those over, but being able to spot them will be difficult. I want to compare the column side by side with the correct dates in Calibre.

If you import into a text column, you can then use a template to convert it to a a date that can be inserted into a date column using Action Chains' single-field edit or Search&Replace.

As a simple example:

program:

somedate = 'Sep 19 2023';
## somedate = '23/09/2023';

	format_date(somedate, 'yyyy-MM-dd')

If the date doesn't exist (e.g. September 32), the template returns BAD DATE.