Description: Sample Sheet attached. The simple formula =MONTH(TODAY()) with the cell formatted to display the name of the month produces the descriptor "Dec" even though the correct month is currently "Jan" If the descriptor is used as the key for an HLOOKUP on the adjacent table it returns the correct month descriptor If the formula is modified to =MONTH(TODAY())+ B1 [preceding cell value] the effect is unchanged until a value is actually placed in B1 - then the addition of 1 to the formula causes the descriptor to change to the correct descriptor for current month and returns the expected result from the HLOOKUP However, if the value of B1 is changed to any other number then the descriptor remains at that for the current month and the HLOOKUP returns the correct descriptor. Steps to Reproduce: 1 Observe the formula in C1 of the attached sheet 2 It's currently January 2021 so verify the expected result to be "Jan" 3 Observe the result. I'm not convinced it's supposed to be "Dec" 4 Observe the results in C2 & C3 which reflect the correct HLOOKUP return 5 Key a value of 1 into B1 6 Observe the result C1's "Dec" has changed to "Jan" The correct HLOOKUP is reflected in C2 & C3 7 Key values between 2 & 11 into B1 8 Observe the result "Jan" is unchanged in C1 The correct HLOOKUP is reflected in C2 & C3 9 Key 9 Jan 2021 into C1 - which overwrites the formula with 09/01/20211 10 Observe the results in C2 & C3 NOTE: C6 & C7 are simply to confirm the interpretation of TODAY() & MONTH() Actual Results: Unless I'm misinterpreting the anticipated results the displayed month descriptor is not the current month Expected Results: Return the current month's descriptor Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 7.0.4.2 (x64) Build ID: dcf040e67528d9187c66b2379df5ea4407429775 CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 168785 [details] Simple CALC .ods file
The function MONTH() returns a number (as an integer between 1 and 12) of the month in the passed date [1]. It does *not* return a date, which in Calc is a number of days since Epoch [2]. On the other hand, formatting a cell as Date makes Calc treat the value in the cell as a date. So the integer between 1 and 12 is considered number of days since Epoch, which by default is since 1899-12-30. For current date (January 2021), MONTH(TODAY()) returns 1, which, when considered a date, is treated as "1 day after 1899-30-12", which is 1899-12-31. Then that date's month is shown, which is December. If you add some number to the MONTH() result, it produces respectively larger number: e.g., adding 1, you get sum of 2, which, treated as "number of days since 1899-12-30", gives you 1900-01-01, which was in January. Naturally, trying to treat month number as number of days since some point is very unlikely to be what you want. It is user error to try to format random numbers as dates. So this is not a bug. If you need a string showing the month, and also a number for formula processing, you may either use two cells (e.g., B1 for "=TODAY()" formatted as date "MMM", and C1 for "=MONTH(B1)"); or you may use "MONTH($B$1)" directly in cells where you used "C1"; or you may keep the cell content as is now, but instead of applying "date" format to C1, use conditional format to make the cell display needed text for different numbers. An example of the latter suggestion would be four formats, like "if cell value is between 1 and 3, apply number format '[=1]"Jan";[=2]"Feb";"Mar"', else if cell value is between 4 and 6, apply number format '[=4]"Apr";[=5]"May";"Jun"', else if cell value is between 7 and 9, apply number format '[=7]"Jul";[=8]"Aug";"Sep"', else apply number format '[=10]"Oct";[=11]"Nov";"Dec"'". This is bulky, and I would advise to use two cells. [1] https://help.libreoffice.org/7.1/en-US/text/scalc/01/func_month.html [2] https://help.libreoffice.org/7.1/en-US/text/shared/optionen/01060500.html#hd_id3147125
In all honesty, I only needed the month number returned by MONTH(TODAY()) so I could identify a sequential column alpha for an INDIRECT(). In order to verify that it would return the correct month number as time progressed, I simply added the variable to verify my logic. I (mistakenly?) believed I would be incrementing the month number but as you explained, with your explanation of epoch, I was just adding single days to the date. Is this still possibly a bug because the MONTH(TODAY()) correctly returns month number 1 so shouldn't my extra number be incrementing the return of the "outer wrapper" - MONTH() function - as opposed to the "inner" TODAY() function? That is to say; the returned value was =TODAY()+1 when surely the expectation is =MONTH()+1 Or is this really a topic for Ask Libre?
My apologies Mike, I have finally realised that what appeared to be the correct return of the month number was simply because I was "pre-loading" the result I expected into the formula and that it simply performed the HLOOKUP() on a foregone conclusion. Formatting the date cell to simply display the month allowed me to mislead myself into believing the HLOOKUP was on the month descriptor instead of the preloaded month number.