Yes, I know how to use custom date formats, thanks.
My original problem with the Writing History export still stands though and it’s bugging me still. If you can offer any insight or tips I’d appreciate it. Over a week and many replies and I still haven’t got closer to solving this problem. It’s probably a pebkac thing so I don’t want to raise a support ticket, but the lack of replies here from anyone at L&L suggests I might have to.
Quite frankly, I’m lost.
You’d need to provide screenshots of what you see in Scrivener that produces two formats of dates as per you original post for the same data set, which is not a likely possibility.
L&L will support you on Scrivener, but have no obligation towards users beyond Scrivener.
There’s a lot of emotional frustration in your posts but little facts to work with, which will generally lead to little interest in responding on any user forum.
I apologise for being short. I’m frustrated that other than tips on Excel and alternatives, not one poster seems to have grasped what I’m on about, let alone even used this feature of Scrivener. I will try and better explain how to see what I’m seeing… I cannot produce screen shots from Scrivener because it’s an export from Scrivener causing the problem!
Nope. Don’t see that here.
The output CSV file is clearly in a single format easily readable as dates in Excel (YYYY-MM-DD). I don’t have Excel anymore to demonstrate it works (as I asserted above), but works fine to display the source CSV and works (no screen shot) in Apple Numbers.
The system setting on my Mac is the same format. There will be an equivalent setting in Windows somewhere.
The Scrivener project setting for metadata seem irrelevant (as expected) as
If any of your dates in the CSV file are malformed (unexpected, but maybe), simple to simply change in the CSV file. If there are malformed dates in the CSV file, then post that screen shot (or better yet, the file) and show not the derived Excel file as it may well be something L&L wants to see. As far as using Excel correctly, that is irrelevant to Scrivener as long as the CSV file it creates is correct.
Thank you!
But I’m on Windows and I get this with US-style dates: (Screenshot from notepad)
Which is now even more bizarre as the outliner export Kevitec57 told me about gives me UK dates just fine:
My windows region setting:
Interestingly, this different than first post. Now, knowing the format of the CSV file, tell that to Excel with Excel’s import tools which are powerful enough to deal with this anomaly. Excel works and I can say that with confidence.
We probably have, after 25 (now 26) posts, taken this as far as possible.
Anyone using windows able to duplicate?
I’m so glad I said that having disparate dates is an ‘unlikely possibility’. I get the same effect as you on opening the exported CSV file in Excel, where dates interchange format at random over time up until today. Definitely, something is amiss in Scrivener. See sample below.
Date
2/23/22
2/24/22
2/25/22
2/26/22
2/28/22
03-01-2022
03-02-2022
03-03-2022
03-04-2022
03-07-2022
03-08-2022
03-09-2022
03-10-2022
03-12-2022
3/13/22
3/14/22
3/15/22
3/16/22
3/17/22
3/18/22
3/19/22
3/21/22
3/22/22
3/24/22
3/25/22
3/26/22
04-02-2022
04-08-2022
…
So do the following:
Happy Trails
I do see that the date format in the exported CSV from the Writing History uses a month/day/year format rather than following the system regional setting, which would be a reasonable expectation, and I’ve filed that to take a look at. However, I haven’t come across a case where the raw data out of Scrivener is using multiple different formats for the dates. If you open the exported CSV file in a plain text editor, are you seeing that mix?
What Excel does with the data formatting is up to it, but generally speaking you ought to be able to
You may also need to widen the column a bit—if it’s too narrow, you may see hashmarks instead of the date.
I did a fresh export of data today, as follows.
Opening the CSV in a plain text editor, I get a consistent date format:
Checked on opening in Excel and the result is the mixed format:
| Date | Words (Draft) | Words (Elsewhere) | Words (Total) | Characters (Draft) | Characters (Elsewhere) | Characters (Total) |
|---|---|---|---|---|---|---|
| 2/23/22 | -1202 | 1629 | 427 | -6797 | 7497 | 700 |
| 2/24/22 | 5 | 1617 | 1622 | 28 | 9110 | 9138 |
| 2/25/22 | 2023 | 15563 | 17586 | 11061 | 87714 | 98775 |
| 2/26/22 | 0 | -1259 | -1259 | 0 | -7076 | -7076 |
| 2/28/22 | 0 | 0 | 0 | 1 | 0 | 1 |
| 03-01-2022 | 0 | -64 | -64 | 0 | -558 | -558 |
| 03-02-2022 | 5 | 1161 | 1166 | 25 | 6301 | 6326 |
| 03-03-2022 | 0 | -3 | -3 | -3 | -12 | -15 |
| 03-04-2022 | -4 | -5 | -9 | -26 | -97 | -123 |
| 03-07-2022 | 213 | 3 | 216 | 1343 | 11 | 1354 |
| 03-08-2022 | 112 | 56 | 168 | 665 | 301 | 966 |
| 03-09-2022 | -1 | 0 | -1 | -7 | 0 | -7 |
| 03-10-2022 | 0 | 7 | 7 | 0 | 31 | 31 |
| 03-12-2022 | -4 | 667 | 663 | -22 | 3605 | 3583 |
| 3/13/22 | 455 | 123 | 578 | 2899 | 726 | 3625 |
| 3/14/22 | -161 | 162 | 1 | -1004 | 956 | -48 |
| 3/15/22 | -12 | -439 | -451 | -35 | -2449 | -2484 |
| 3/16/22 | 241 | 2114 | 2355 | 1224 | 11745 | 12969 |
| 3/17/22 | 5 | -1952 | -1947 | 30 | -10986 | -10956 |
| 3/18/22 | 37 | -2433 | -2396 | 209 | -14273 | -14064 |
| 3/19/22 | 2 | 0 | 2 | 7 | 0 | 7 |
| 3/21/22 | 111 | 317 | 428 | 622 | 1671 | 2293 |
| 3/22/22 | 0 | 1 | 1 | 0 | 0 | 0 |
| 3/24/22 | -2 | 2 | 0 | -2 | 2 | 0 |
| 3/25/22 | 0 | 44 | 44 | 49 | 234 | 283 |
| 3/26/22 | 191 | 0 | 191 | 1099 | 0 | 1099 |
| 04-02-2022 | 12 | -27 | -15 | 3 | -180 | -177 |
| 04-08-2022 | 0 | 1268 | 1268 | 0 | 7227 | 7227 |
| 04-09-2022 | 2 | 433 | 435 | 14 | 2549 | 2563 |
| 04-10-2022 | 0 | 344 | 344 | 0 | 1927 | 1927 |
| 04-12-2022 | 2 | 250 | 252 | 49 | 1469 | 1518 |
| 4/13/22 | 0 | 0 | 0 | 21 | 0 | 21 |
| 4/14/22 | -143 | 11 | -132 | -851 | 67 | -784 |
| 4/15/22 | -5 | 0 | -5 | -18 | 0 | -18 |
| 4/16/22 | 1 | 0 | 1 | 2 | 0 | 2 |
| 4/17/22 | 0 | 0 | 0 | -2 | 0 | -2 |
| 4/18/22 | 21 | 1412 | 1433 | 109 | 7830 | 7939 |
| 4/19/22 | 42 | 1244 | 1286 | 302 | 7055 | 7357 |
| 4/20/22 | 837 | -1787 | -950 | 3657 | -9884 | -6227 |
| 4/21/22 | -27 | 487 | 460 | -191 | 2819 | 2628 |
Excel’s Date reformatting ignores the dates in the m/dd/yy format, i.e. it does not respond to a direct choice to reformat it.
Doing a quick calculation, the dates in my default format using a simple calculation [=Today()-Adjacent_Date_Cell] only performs a calculation on the dates in the format dd-mm-yyy (my format of choice). The calculation on the format m/dd/yy renders a #VALUE! result, which confirms the aforementioned. The one fix I explored was by running a Data > Get Data option highlighted in my previous post.
Also, running the Right-lick > Format Cells > Number > Date option on just 4 lines of dates formatted on the n/dd/yy format does not change them to my default date, as there is no underlying “Date Number” to work with.
Have you read that book on how to use Excel yet? Perhaps give the “Text Import Wizard” a try?
Excel can certainly be told how to read CSV files. Up to you, not Scrivener.
For now there’s a problem with the export from Scrivener.
As long as you keep thinking that will be as long as it is unresolved.
The proof is in the pudding. See my last post. I agree with the OP.
As I said above, Scrivener does currently only export in a US date format, and I have already filed this for investigation, as it would be preferable to use the Windows system format. That would make it easier to go straight into Excel, which seems to only be able to work with the system date setting (I was mistaken in my earlier formatting steps, for which I apologise; it did not convert as fully as I had thought at the time). However, Scrivener is consistent in its output, and how that data is interpreted and formatted in another program is up to that program and what adjustments you can make.
In the recent example above, when directly opening the file, Excel interprets and automatically formats dates that would be dates in a European format, i.e. any time the m/d/yyyy numbers are something that would be a valid date if read as d/m/yyyy, Excel treats it as a date and formats it per your settings. Obviously this is not desirable in this case, since it’s mixing up the dates, but as far as I can tell, the date is set internally to the number system Excel uses for that as part of opening the file and making it a new Excel workbook, and from that point I have not yet found a way to convert it. The unrecognised dates can be to the correct date format via Data ▸ Text to Columns and setting the column as the MDY format; after that, they’ll be the proper dates in Excel, and formatted however you choose to format dates. But that won’t fix the ones that converted to dates on open.
I don’t use Excel frequently and am just poking around online and experimenting for the conversion, so there may well be a way to reset incorrect dates that I haven’t found. But it does seem like the easist way for now to do this is as you already noted: importing rather than opening the .csv file, so you can set the MDY format in the process.
Alternatively, you could
^(\d{1,2})/(\d{1,2}) and replace with $1/$2) (or the appropriate replacement scheme for the RegEx type supported by the program, might be \1/\2), resave, and open in Excel.And I don’t have Windows Excel anymore to test this, but does not the finer control available in the Import Wizard in Excel not allow you to specify the data format in the first column as MDY?
More precise than simply opening the file in Excel and hoping for the best.
If not, then my memories as a data munger are clearly flawed.
I don’t use the data export feature but ran tests out of interest in a topic raised by someone else.
Excel is a wonder toy, but why it randomly scrambles date data in this CSV is puzzling. Logically, it should open the spreadsheet with dates in a consistent format and immediately recognise it as dates, irrespective of the region setting. That’s how it’s always worked.
Opening the CSV in Wordpad, all dates are in one format, though data is not in tabular format, but listed as comma delimited data.
The Get Data wizard has no problem shuffling the disparate Excel data into one date standard, without having to change Windows regional date settings. (It’s probably the least fiddly workaround, only with more steps than usual.)
I agree that Excel’s interpretation is not something for L&L to resolve.
Hope that puts things in context of where I’m coming from on this subject.
I hope not beating a dead horse here, but it is not random.
And yes, it’s not for Literature & Latte to sort out (although they acknowledge they might change the export to be in accordance with the regional settings, per @MimeticMouton … so give them time to implement the change—which won’t be immediate nor probably is it a priority).
When the CSV file is simply and directly opened, the Excel app is apparently programmed to use the local regional settings of that computer on date format to translate the text it sees that look like dates into dates that it will represent as not text, but the number of seconds after some defined date in the past. I forget what that date is. Just a long time ago.
A computer with regional settings in UK assumes day, month, year with one or two (or four for years) digits separated by some normal delimiter, e.g. “/“, or “-“, or whatever. If two digit years, Excel will also make assumptions about century—a left over issue from the Year-2000 “bug” fear.
So when it sees any number in the middle part of the date text string greater than 12, it’s going to say “Gosh, but that can’t be right so I’m going to assume that this cell is a text field and I’m not going to convert to an internalised date.”
This is clearly demonstrated in above posts where a month number of 13,14,15,16,17,18,19,20, and 21 were all not accepted as month numbers and so imported as text. Hence some of the cells in the column were text, and some were dates. Hence to convert all to dates was more difficult without a lot messing about with Excel Functions.
By using the more precise import method using the “import wizard”, fully documented by Microsoft and explained in all the books and easily accessible by a very visible menu command, which in my days of doing a lot of number crunching and data analysis I used all the time, one can tell Excel what the data format is. Can do this not only for the dates, but for most if not all the data formats.
What I’m explaining is standard and common use of Excel. Powerful tools need attention and understanding and picking up a book or read “Help” is often useful.
‘Nuff said. 