[SOLVED] Day() doesn't return the correct value. {Oops! User Error}

[HavaneissDei]

I’m currently working in Calc with dates in the 1300s and 1400s. The “Day()” function doesn’t work on dates prior to 1582-10-15. For what I’m doing, all I need (RE this issue) is for Day() to return the “day of the month” part of the date; else, I need a reliable way to extract the “day of the month” component from the displayed date.

For my entertainment, I’m using a cell format that shows the date as “YYYY-MM-DD NN”; using the standard ISO8601 format doesn’t help. I can change the format to show only the DD component; however, this is useless for my calculations, as the underlying information is unaffected.

An example of the problem involves calculating the years, months and days between two dates; in this case, I end up with a text value automatically displayed in either a “Y-MM-DD”, a “YY-MM-DD” or a “YYY-MM-DD” format (with right alignment and a fixed-pitch font, the data presents nicely).

Simply subtracting the smaller date from the larger, and applying a date format, doesn’t return the correct answer (a problem with even modern dates, but I’m not dealing with that right now).

I’m using LibreOffice Version: 5.1.6.2 and my OS is Linux Mint 18 “Sarah” with the “Cinnamon” GUI (I hope I expressed that properly).

[Lupp]

Quoting @HavaneissDei: “Simply subtracting the smaller date from the larger, and applying a date format, doesn’t return the correct answer (a problem with even modern dates, but I’m not dealing with that right now).”
Differences of dates aren’t dates!
Don’t try to apply the format codes to them. Communicating date differences in “years, months, and days” is doubtable. Years only (with fractional part probably) or days only should be preferred.
You may study and try the DATEDIF() function. I never use it anyway, and I wouldn’t expect it to perform correctly with pre-Gregorian dates.
Have a nice day!

[HavaneissDei]

Hi, Lupp!

Oops! I didn’t intend to indicate that I thought of differences in dates, as dates; however, I can now see that my subtraction and formatting “idea” was attempting to create exactly that confusion. As the writers for Seth MacFarlane’s comedy might observe, “Fatigue no es bueno!” LOL

I tried DATEDIF() before I read your reply; you may want now to question your self-image (because we’re thinking alike – ha ha).

Joking aside, DATEDIF() takes a few seconds of experimentation to figure out what’s going on, but it let me configure strings the way I wanted, with minimal reliance on manual intervention (I haven’t figured out an economical way to force a “00” output when the day of the month is the same).

Thanks for the recommendation: I’m no expert, but I believe it is helpful.

Warmest regards.

[HavaneissDei]

Thanks for the bug submission; I found the recommended way of solving the problem to be better and got distracted with my sheet, and de-prioritized the report because I had been attempting to use the software improperly. I would have posted back here sooner, but I forgot where I had bookmarked this page.

[Lupp]

It seems the formatting routines for dates know about the Gregorian reform and the Julian status quo ante, and regard all that correctly while the DAY() function doesn’t.
Try the replacement of =DAY(A2) with =VALUE(TEXT(A2;"DD")).
If your sheet also should work in locales using different code letters for formatting dates, you have another funny little problem. Software loves to create unnecessary silly problems.

[erAck]

Since some releases English format codes are possible in the TEXT() function’s format string argument, regardless of locale. Unless a single English letter code (here “DD”) is also available in the current locale’s format codes but meaning something different… which is not the case for “D”.

Btw, someone might want to file a bug for the DAY(), MONTH() and YEAR() pre-Gregorian behavior (yes they all don’t cut the gap). Please report the resulting bug number back here if done. Thanks.

[HavaneissDei]

Greetings, erAck!

The recommended replacement seems to work well, but incorporating it into the sort of string that I’m using will take more effort than I presently want to expend, although it may actually produce better long-term results.

I have a little “work area” in one of my sheets; I’ve pasted the recommended replacement there, so I can come back to it, and I’m bookmarking this page in my browser, so I can update my findings.

I’m perpetually amazed by the resource that is shared information and experience, and daily humbled for the giants on whose shoulders I stand. I doubt I have the competence to describe to the developers a bug report. I’ll try to collect some additional information, attempt [to file a bug for the DAY(), MONTH() and YEAR() pre-Gregorian behavior], then report back probably in a week or two with news of my progress.

Thanks, and warmest regards.

[erAck]

Submitted bug is tdf#122158

[HavaneissDei]

Thanks for the bug submission; I found the recommended way of solving the problem to be better and got distracted with my sheet, and de-prioritized the report because I had been attempting to use the software improperly. I would have posted back here sooner, but I forgot where I had bookmarked this page.