Cannot work out correct format for entering date data.

[marrs]

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.

# This data file was generated by the Spreadsheet Calculator Improvised (SC-IM)
# You almost certainly shouldn't edit it.

format B 14 2 0
rightstring A0 = "2022/12/31"
let B0 = -2207347200
fmt B0 "d%d/%m/%y"
rightstring A1 = "12/31/2022"
let B1 = -2202940800
fmt B1 "d%d/%m/%y"
rightstring A2 = "31/12/2022"
let B2 = 1609372800
fmt B2 "d%d/%m/%y"
rightstring A3 = "31/12/22"
let B3 = 1672444800
fmt B3 "d%d/%m/%y"
goto A81

[marrs]

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.

[andmarti1424]

@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"

[marrs]

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.

1. Actual bug: %b is incorrectly interpreted by the date formatter.

1. Download datefmt-bug.txt
2. Rename the file to .sc and open with sc-im
3. Navigate to cell A2 (containing value: Nov 2022)
4. Edit to >Dec 2022

Date remains as Nov 2022 when it should update to show Dec 2022.

You can further reproduce the bug with the following:

5. Edit to >Jan 2022.

Date is updated to Dec 2022 when it should become Jan 2022.

2. Possible bug: Pf deletes value of cell when working with dates.

1. Open a blank spreadsheet with sc-im
2. Input a date in to A0 with >30/11/22 C-d
3. Format that date to show Nov 2022 with :format "d%b %Y"
4. Enter a different date into A1 with >31/12/22 then C-d
5. Use 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.

3. Likely bug: Cannot modify the date of a cell that only shows partial date info

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.

1. Open blank spreadsheet
2. Enter >13/11/22 C-d
3. Reformat the date with :format "d%B %Y". Cell shows new formatting.
4. Copy cell to cell below with yyjp
5. Update value to be 15 Dec 2022 with >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.

4. Usability issue:C-d can't handle unambiguous dates

• C-d requires me to enter the date so that it matches the current value of :datefmt.
• If :datefmt = %d/%m/%y then I must type >1/3/20 for C-d to correctly interpret the date as 1st March 2020.
• If I type >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.

5. Usability issue: Nuanced differences between :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.

6. Usability issue: The status line

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?

1. A1 is obviously the cell index. No complaints there.
2. (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.
3. I'm a professional programmer and I can't tell you what [-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, or
• A1 (d%m/%d/%y) [01 Mar 2012] for a cell that reads 3/1/12, or
• A1 (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.

7. The overall user experience as it applies to working with dates

Some general background

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.

How I use dates

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:

• Do I use :datefmt or :format?
• Do I copy the formatting to a cell before I've populated it with a value or after?
• Does d%d/%m/%y mean 1/1/22, 01/01/22, 01/01/2022, or any of those, or only some?

This bug report as an example

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. Start with a blank spreadsheet
2. Enter new date representing the source date of Nov 2022 by typing >1/11/2022. C-d converts it to a timestamp [1604188800] that means nothing to me and displays 01/11/20 in the cell.
3. Go read some docs, search for some help online. Read somewhere that sc-im likes formats of `%Y/%m/%d
4. Try entering >2022/11/1 instead. C-d converts this to a negative timestamp and shows 20/01/00 in the cell.
5. Eventually I work out that I need to match my input to the date format %d/%m/%y and Replace the date with >1/11/22.
6. That seems to have worked, but is that really 1 Nov 2022 or is it 11 Jan 2022? The timestamp's not telling me, but the format string indicates that it is, so I'll go with it.

Now I want to apply formatting to make it appear as November 2022.

7. A quick look in the docs suggests that %B %Y will format the date correctly.
8. With the target cell selected, type :datefmt %B %Y. Nothing happens.
9. Try C-d again? Still nothing happens.
10. Eventually work out I need to use :format
11. Try :format "%B %Y". The cell changes to literally show "%B %Y".
12. Quickly hit undo and go back to the docs. Oh, we needed to prefix with a d.
13. `:format "d%b %Y" does the trick.

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:

14. Create a new date representing the date I want to reformat with >1/12/22 C-d.
15. Go back to the template cell November 2022
16. Paste its formatting to the new cell with yyjPf
17. Target cell disappears. Hit undo.
18. Copy template cell instead using yyjp
19. Try converting the date with >1/12/22 C-d yields December 1899 and timestamp shows a negative value.
20. try converting with >December 2022. November 2022 shows instead.

[HelionSmoker]

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

[marrs]

@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

[andmarti1424]

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?

[andmarti1424]

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

[marrs]

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

[andmarti1424]

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

[andmarti1424]

@marrs Regarding 2 please update to latest commit on dev branch and retry.
Thanks.

[andmarti1424]

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

[andmarti1424]

@marrs please take a look at 93f68f2
I have also updated DATES INPUT section in doc

[andmarti1424]

@marrs Could you take a look at the change?

[marrs]

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.

[marrs]

@andmarti1424 dev branch fixes items 2 & 3.

[marrs]

@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

[marrs]

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

[andmarti1424]

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