Windows V 3.1.4
Having a go at automating my use of spreadsheets to record my daily writing efforts but came upon a small snag exporting the Writing History data. Despite the dialogue displaying the dates in UK format (date month year) It seem to export the data in US format (month date year).
For example the current month has the following in the date column:
04 01 2023
04 02 2023
04 03 2023
04 04 2023
04 05 2023
04 06 2023
04 07 2023
04 08 2023
04 09 2023
04 10 2023
4/13/23
4/17/23
4/19/23
This is repeated through previous months, right through the entire project history.
Excel doesnât recognise the ones with '/'s as numbers and the others when I reformat them to say âlong dateâ format are now all wrong. E.g. 04 10 2023 becomes 4th October 2020. So the whole column is useless as exported data!
Is there any way to make this work properly so it can actually be used without retyping all the date information? Have I missed a date format setting somewhere? Iâve looked through the Options but canât find anything.
Thanks for the link Anton but no it doesnât help
Nothing I try produces anything other than #Value or an incorrect date. I shall stick with manually entering the data I think. This feature is clearly far to advanced for my tiny brain.
Annoyed I couldnât get this to work I had another Google and managed to convert all the dates to the correct format using the âText to Columnsâ feature in Excel. Whilst itâs great to have finally got 10th April instead of the 4th October I am not an advanced enough Excel user to figure out how to automate this so this export file is still useless.
Honestly Iâm not quite sure why this export is even a feature if it requires so much work to make it useful.
Yep tried that. The cells with slashes were left justified and didnât change when the number format option was changed from itâs default of âGeneralâ to⊠well anything! They needed to be converted first. The others were already set to âdateâ but if I selected either the long or short options it changed them to the wrong date!
Well, without me on your keyboard to poke around, I canât think what else to suggest. I just do not recall ever having such problem with dates.
Dates in Excel are stored as numbers as the number of seconds after some date in the far past. What you should see is a representation of that number in a human recognisable form. So ⊠try formatting all those cells as ânumberâ and see if you can see numbers or if some of the cells are not actually numbers. (Note: nothing to do with justification).
Good luck. Perhaps re-ask this in an internet forum about Excel.
Like I said earlier the âdatesâ with / marks arenât even numbers. They donât change no matter what number format I apply to them and all formulae result in a #Value result unless they are converted to number format. I posted here because I wanted to fix the data at source, in Scrivener. A simple export of dates and numbers shouldnât produce this much hassle, especially as they display just fine in Scrivener itself!
That is an incorrect assumption. As was advised, learn how to format dates in Excel.
Type 29/04/2023 (UK date style) into Excel without formatting the column as a Date type. All youâll have in the column is General-type data that you canât apply a formula to until you change the column type to Date (which is a calculated number blahty-blahty-blah, but thatâs invisible to you as a novice).
Also, you cannot do what you seem to have done and import two disparate date formats into an Excel Date type column and expect Excel to work its magic automatically. Youâd need to manipulate one of the two formats afterwards, because the Date type formatted column in Excel expects to have it in one format only. (Sure you can type 29/4 and an appropriately formatted Excel column will accept that as 29/04/2023, but try typing 4/29 and youâll get an error based on you date format. And hereâs where itâs complicated because 4/29 is perfectly fine if your operating system is configured for US dates. Excel gets its country formats from the operating system settings, but the Date type cell or column or row still needs to be set in Excel, not the country format type.)
To conclude my point, type the list of dates you have in your original post in Word, then paste them in Excel [or even import them] without formatting an Excel column. Youâll be sitting with the same dilemma youâre asking L&L to resolve in Excel, which wonât happen unless L&L buys out Microsoft, and probably not even then.
Actually this is incorrect. Maybe I have a different version of Excel to you but when I type 29/04/2023 into excel and press enter the cell is marked as a date in the number format dropdown, automatically and applying a formula to it works just fine, without any manual setting needed.
Well yes. But it wasnât me who chose to use these odd formats. Excel did when it tried to import the data and got confused by the cells being labelled as dates when some of them arenât based on my systemâs date settings and did itâs best. The problem is they are in US format in the first place. Which is bizarre for a UK-based company. But I guess theyâve gone for a greater market. An option in Scrivener to choose which format to export these dates in would be ideal? Which is why I posted here in the first place, thinking Iâd missed that setting. From all these replies Iâm clearly not and so this feature is essentially useless to me. Which is frustrating especially as the month export from Scrivener works just fine. Nice list of correct dates automatically formatted in the custom number format mmm yy.
What is your system date format set to?
Yes, you will note in a few places that Scrivener uses US spelling in its menus, so your assumption about the greater market customer base is probably correct.
What format does Scrivener export the data? Comma delimited CSV? You could import that into Excel and search out an IF statement on Google for Excel to adjust the dates.
These days in Excel you type something in an adjoining cell based on the content of the original and Excel adjusts the subsequent cells in a column accordingly. But then you need to familiarise yourself with Excel tables. So in essence the number value of the imported date is irrelevant as the display value of each character in a cell is subjected to a test and adjusted accordingly.
With all these permeations at play, do you still wonder why there is no one solution fits all?
Iâm not expecting a one solution fits all. Just one that means I can get the correct dates without too much hassle. So far manually typing them takes less time than using the exported data. Which makes me wonder why Scrivener has this feature at all? Does anyone actually use it?
Setup a Custom Metadata field in Scrivener, using the Type: Date, Format: Custom and Custom Format: dd/MM/yyyy (UK date format). Note that the month pattern must be in capitals, else youâre referring to seconds, which will render an illogical number.
Type in the date for subsequent scenes in the UK format of typing dates, since the field has been set as such. Since the Time attribute has not been set, itâs ignored.
The Export Outliner menu pops up. Type a name into Save As, Format: Comma Separated Values (CSV), Text Encoding: Unicode (UTF-8) â I assume youâre on Windows; if on Mac, it may differ but not by much.
After pressing OK, find the file you saved using File Explorer or Finder on Mac. Youâll see it has an Excel-like icon. Open it and your data is reflected in columns and rows, which can be manipulated in Excel. Note column H in my sample. The dates look different because my system date format is set different to the UK format. Mine is still readable as DATE MONTH YEAR, your should be too. Youâll also note I have an error in cell H7, which tells me I need to fix my source data in Scrivener.
Thanks, I acknowledge your correction that Excel recognises a Date out of the box, if in the correct format. My bad.
Hope thatâs clear enough for you to proceed and takes away any expectation for L&L to provide anything beyond the proven working solution that is available.
Well, now you have a working solution, which you can adjust for your daily target setting. The same type of data would serve those that use AEON Timeline, for example. Different strokes for different okes, as we say over here.
Thanks @Kevitec57 I had no idea you could export the ouliner to CSV! Gamechanger!
Itâs a pity the modified date doesnât export properly, would save me having to change the metadata for each file I update every day. But I shall have play. Thank you!
I donât agree. See extract with Modified Date and Time listing as Iâve set it up in the operating system, reflecting DATE-MONTH-YEAR HOURS:MINUTES ⊠see last column.
As I mentioned, set your date display default in your operating systemâs settings, be it UK or US and use the format consistently as it applies in your country. My experience is all computers are set to US defaults, even if Windows is licensed in Europe. Techies are just too lazy to do otherwise, and most users are none the wiser.
What I further suggest you do in Scrivener is setup a Layout whereby you just display the Title and Modified Date only in Outliner mode, name it and save it. (But before doing this save your current Outliner display as a Layout, naming and saving it.)
In future, when you want to do a periodic export of your progress then select Use or double click on the saved Layout. Itâs easy once youâve tried and grasped the concept.
Changing my system short date to dd/mm/yyyy has done the trick. I canât remember why/how I changed it to include the day name but that was certainly not helping! The modified date now works perfectly in Excel. The writing history export âdaysâ date still doesnât work but canât win 'em all! I can use the Outliner export to try and automate my spreadsheet.
You can format the column in Excelâafter the importâto include the day.
In Excel, highlight the Modified Date column > Right-click > Format Cells > Number > Click on Custom > Type in the cell below Type: ddd, dd/mm/yyy for Sat, 28/04/2023, or whatever date it is.
It strips out the time, as well. If you want to continue seeing the time, add a space and hh:mm to the format string.
And Bobâs your uncle.
An option in Scrivener to choose which format to export these dates in would be ideal? Which is why I posted here in the first place, thinking Iâd missed that setting. From all these replies Iâm clearly not and so this feature is essentially useless to me. Which is frustrating especially as the month export from Scrivener works just fine. Nice list of correct dates automatically formatted in the custom number format mmm yy.
