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
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.
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.
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.
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.
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.