Open marrs opened 1 year ago
I have a further problem in the spreadsheet I'm actually working with: I cannot apply the formatting of the date in the previous cell to the new one that I've created. I'm using yyjPf
to do this. The formatting for the src cell is (d%b %Y)
. Copying it to the cell below seems to copy the formatting ok, but deletes the cell data.
Alternatively, if I copy the formatting first, then enter the date. This doesn't work either:
Entering >31/12/22
produces Dec 1899
.
Entering >Dec 2022
produces Nov 2022
.
@marrs Im not sure if I understand your problem.
Lets say you have this text on A0: 2022/12/31
If you issue :datefmt "%Y/%m/%d" sc-im converts the text to a the numeric value that the date represents..
You can then also reformat the numeric value, for instance to 31/12/2022 with: :format "d%d/%m/%Y"
Thanks for that explanation. It's helped me understand some of the things I've been seeing.
In hindsight I think I've encountered multiple separate and overlapping issues that make this a far more complex bug report than I initially realised! If I was starting again, I might break this into multiple tickets, but then again, there's an overall user experience issue here that I want to get across and keeping these issues together helps me do that.
That said, this is a large ticket now, and I apologise for that. It's not my intention to overwhelm you and I hope that I've done my part to be as clear and concise as is necessary.
%b
is incorrectly interpreted by the date formatter..sc
and open with sc-im
>Dec 2022
Date remains as Nov 2022
when it should update to show Dec 2022
.
You can further reproduce the bug with the following:
>Jan 2022
.Date is updated to Dec 2022
when it should become Jan 2022
.
Pf
deletes value of cell when working with dates.A0
with >30/11/22
C-d
Nov 2022
with :format "d%b %Y"
A1
with >31/12/22
then C-d
A0
to reformat A1
by navigating to A0
and typing yyjPf
This deletes the contents of A1
when I would expect it to convert the value to Dec 2022
.
Scenario: I have an existing date of 13 Nov 2022 that is formatted to display as November 2022
. I want to use this as the basis of a new date representing 15 Dec 2022 that displays as December 2022
.
>13/11/22
C-d
:format "d%B %Y"
. Cell shows new formatting.yyjp
>15/12/22
.I hope to see December 2022
but instead I see December 1899
and the timestamp shows a negative value.
It looks like I have to convert the format back to something that includes the date (e.g. d%d/%m/%y
), enter the date against that format, then convert back again to d%B %Y
.
Obviously I'd much rather just update the value without having to mess with the formatting.
C-d
can't handle unambiguous datesC-d
requires me to enter the date so that it matches the current value of :datefmt
.:datefmt
= %d/%m/%y
then I must type >1/3/20
for C-d
to correctly interpret the date as 1st March 2020.>01 Mar 2020
then C-d
won't understand it and will convert it to 20/01/00
by default.In my opinion, this is bad behaviour because there is absolutely no doubt as to what 01 Mar 2020
means and therefore C-d
should be able to handle it and convert it to the correct date.
:datefmt
and :format
If I understand this correctly, :datefmt
is for converting a string representing a date to a timestamp integer and :format
is for displaying a timestamp integer as a formatted string.
Internally I get why those things should be different, and maybe their distinction is important for a user writing formulae, but I don't see why :datefmt
can't be applied to both string and integer.
For me, either :datefmt
should be able to handle both types, or C-d
should apply either :datefmt
or :format
depending on the cell type.
Edit: After thinking about this further, I don't think this is quite right. I think what's confusing me is the name of the function :datefmt
. It's not a formatter at all. It's a type converter. I might think differently about this if :datefmt
was in fact called :datetype
or :timestamp
.
Following on from above, if I have a cell containing data 20/01/00
and a corresponding status line of A1 (d%d/%m/%y) [-2207347200]
, what does that mean to me?
A1
is obviously the cell index. No complaints there.(d%d/%m/%y)
is pretty opaque if you don't know what it means, but sc-im is a text-driven tool and it's for power users, so I think showing the format in this way is actually important. However, I think the UI can better help an unfamiliar user derive its meaning. More on that below.[-2207347200]
means. I'm going to guess there's a line of source code that says something like, if (date < 0) { invalid_date = true; }
. I can't imagine any other reason for a negative timestamp. In any case, even I have no use for that in the context of using sc-im, though it might be helpful when submitting bug reports.I'd prefer to see a status line that reads the following: A1 (d%d/%m/%y) [INVALID DATE]
.
Alternatively, if the date is not invalid, then I'd like to see an unambiguous representation, such as:
A1 (d%d/%m/%y) [01 Mar 2012]
for a cell that reads 1/3/12
, orA1 (d%m/%d/%y) [01 Mar 2012]
for a cell that reads 3/1/12
, orA1 (d%y/%m/%d) [01 Mar 2012]
for a cell that reads 12/3/1
.This way, the user always know what the date actually is, and can work out what the formatting string must mean by inspecting the contents of the cell.
sc-im is what I use for all my spreadsheets, including really important ones, but I don't use sc-im that often. This means that I must rely on the docs to remind myself how to do things. That's inevitable for this kind of interface, and I'm ok with that, but nevertheless it can be frustrating, especially if I want to get something done in a hurry. So the less I have to refer to the docs, the happier I am.
Real world example: I use sc-im to track and generate invoices. A single row will contain an invoice date, such as 31/12/22
and also a description for the PDF such as December 2022
. Elsewhere I might have the date formatted as Dec 2022
.
Realistically, I don't remember how I formatted those dates. I did it once a long time ago. Usually, when working with the spreadsheet, I just want to take a date that is already formatted correctly and copy its formatting to the next cell with, but this is where things get awkward. As we've already explored, there are all these little things that you have to remember to get the desired behaviour:
:datefmt
or :format
? d%d/%m/%y
mean 1/1/22
, 01/01/22
, 01/01/2022
, or any of those, or only some?The following journey represents a condensed version of the experience I had that motivated me to write this in-depth report.
The following scenario is essentially showing me trying to reproduce the conditions of my experiences for the purposes of submitting this bug report. The scenario is a little contrived but I'm trying to capture a sense of how I experienced these problems for the first time, before I understood what I know now:
>1/11/2022
. C-d
converts it to a timestamp [1604188800]
that means nothing to me and displays 01/11/20
in the cell.>2022/11/1
instead. C-d
converts this to a negative timestamp and shows 20/01/00
in the cell.%d/%m/%y
and Replace the date with >1/11/22
.Now I want to apply formatting to make it appear as November 2022
.
%B %Y
will format the date correctly.:datefmt %B %Y
. Nothing happens.C-d
again? Still nothing happens.:format
:format "%B %Y"
. The cell changes to literally show "%B %Y".d
.Now I have my example of a cell that that represents the date that I added last month that I want to use a template for my new invoice date. Let me continue to try to create the new invoice date from the old:
>1/12/22
C-d
.November 2022
yyjPf
yyjp
>1/12/22
C-d
yields December 1899
and timestamp shows a negative value.>December 2022
. November 2022
shows instead.:datefmt "%Y/%m/%d"
:format "d%d/%m/%Y"
I think the confusion seems to be coming from the assumption that these 2 steps are the same. In Excel (afaik), this action is carried out in just one step, declaring what date format you want, and then Excel is left with converting the date into a timestamp.
Here, you declare what the format is so that it can be converted to a numeric value and then what format you want to display.
This also confused me at the beginning.
@andmarti1424 did you manage to digest this ticket at all? It would be good to know at least which of these issues can be confirmed as bugs and which need further discussion
Hello. Some, I still have to check all of the cases you reported. Regarding issue 1, perhaps you need to change 'tm_gmtoff' configuration variable? Have you checked that?
@marrs And yes, issue 2 seems something I have to fix. EDIT: Regarding 7, do you happen to use date functions? or you just enter dates..?
- tm_gmtoff
I'll try setting it to a positive value and see if that fixes the issue. If it does then I'll submit a new bug report with the additional info.
Regarding 7, do you happen to use date functions? or you just enter dates..?
I'm not sure what you mean by using date functions. I enter the date as a string and then try to convert it to a date value using C-d
. I use :datefmt
and :format
in the way I described above.
@marrs I meant if you use any of the functions describes on "Built-in Date and Time Functions" section of doc. I use dates on my spreadsheets but since I don't any of the above functions I just enter them as text..
@marrs Regarding 2
please update to latest commit on dev branch and retry.
Thanks.
@marrs Regarding 3, its true. :datefmt
removes text content as it converts the date to the corresponding numeric value.
One option is to avoid removing it so it can be easy modified later. For this we also have to prevent showing the text value of the cell if a date format is applied.. so it wont show the text content AND the date formatted value both at the same time..
@marrs please take a look at https://github.com/andmarti1424/sc-im/commit/93f68f2bc009ec7a245a0adf7baeceae17dd3f1e I have also updated DATES INPUT section in doc
@marrs Could you take a look at the change?
Sorry, @andmarti1424, I didn't see your initial reply. I'm busy for the next week but I'll try and look at this over the weekend. Otherwise I'll get to it towards the end of the month.
@andmarti1424 dev branch fixes items 2 & 3.
@andmarti1424 Setting tm_gmtoff had no effect on item 1. I ran the spreadsheet with commands sc-im --tm_gmtoff=3600
and sc-im --tm gmtoff=1
. Neither did anything
Regarding 7, do you happen to use date functions? or you just enter dates..?
Just entering dates. I've not tried any of those functions before
Regarding number 1, you have to do like this for instance:
enter june 1st on B1 with \06/01/2022
, press control d, then :format "d%b %Y"
enter november 1st on B2 with \11/01/2022
, press control d, then :format "d%b %Y"
then you edit B2 text content modifing it to 12/02/2022 -> that should update the cell and show "Dec 2022"
NOTE: hope you use the same locale as I do..
The treatment of dates in sc-im is causing me confusion. I don't know if this is a bug or intended behaviour, but it's definitely impeding my ability to enter data.
I had to mess around in a small spreadsheet to understand what was going on. I've copied the file data for that spreadsheet below. It contains 2 columns. The column on the left contains a list of unformatted date values entered as right-aligned text, while the column on the right shows the corresponding results of formatting those values with
C-d
.You can see that the interpretation of the values in the 1st two rows is completely nonsensical. The third row seems to have truncated the year and the fourth finally gets it right (I think).
It looks like the date has to be entered in the format of
%d/%m/%y
in order to be interpreted correctly. So if I add the date with>2022/12/31
, which is the preferred format according to the docs, this doesn't work at all. If I add the date as>31/12/2022
, this also fails. Likewise for the US style,>12/31/2022
.This is very confusing behaviour and there's no feedback from the UI as to what is going on.
None of the formats used to enter the date is ambiguous. It's quite clear for each of them which part must be date, which must be month, and which must be year. I would suggest therefore that the date interpreter should be able to work out that a number greater than 12 is not a month and that a 4 digit number must be a year and reconstruct the date appropriately.
Obviously a value such as
>12/12/12
is ambiguous, but a message from the programme saying how that date has been interpreted would be fine by me.Also, I found the documentation to be misleading. It gives the impression that a UK format should not be used but it seems from my experimenting that in fact it should be, at least on my machine.