Calc calendar in xlsx file format - day numbers disappeared suddenly

users
#1

I have a calendar file in XLSX format that I've been using and tweaking for
years, now. For the past two days, I've been updating it for 2021. Today, I
thought I had it correct, but of course you always miss one thing or
another.

I copy the base file and update it from year to year.

I reopened it and all of the day numbers were gone. I opened the base file,
and the day numbers were gone from it, as well.

By "gone," I mean that they don't show. If I select a day number cell, I can
see the formula that generates the number, and if I go to edit the cell
format, and turn the font color from "Automatic" to "black," it STILL
doesn't show up.

I'm guessing there is an issue with LibreOffice, but I don't have
LibreOffice running. I can't find it in Task Manager (running Windows 10
Pro) to kill it.

Has anyone seen anything like this, and if so, what did you do to fix it? I
can't reboot, right now, because I'm in the middle of a bunch of other
stuff, too, but I suspect that if I rebooted, it would be fixed.

Thanks!

#2

Hi,It is a bit hard to guess what is going on without actually seeing
the file. Can you e-mail it to me?Two things come to mind:-
Calculations are in manual mode and the cells are not re-calculated.
You can see the status in Data > Calculate (or try F9 to force
recalculation)- Conditional formatting that overrides the cell text
color because an entry is invalid (year greater than 2020?). You can
try to copy a blank cell and paste the value in the cell elsewhere
(using Paste Special). You can then see if the calculation is done and
if it's only a formatting issue.- A calculation or data element is
wrong and the cell formula prevents wrong outputs to be shown
(something like =IFERROR(something,"")). You can use the Detective
(Tools > Detective > Trace Precendents) to find which cells control
which and find the source issueI hope this helps.Rémy.

#3

Hi,It is a bit hard to guess what is going on without actually seeing
the file. Can you e-mail it to me?Two things come to mind:-
Calculations are in manual mode and the cells are not re-calculated.
You can see the status in Data > Calculate (or try F9 to force
recalculation)- Conditional formatting that overrides the cell text
color because an entry is invalid (year greater than 2020?). You can
try to copy a blank cell and paste the value in the cell elsewhere
(using Paste Special). You can then see if the calculation is done and
if it's only a formatting issue.- A calculation or data element is
wrong and the cell formula prevents wrong outputs to be shown
(something like =IFERROR(something,"")). You can use the Detective
(Tools > Detective > Trace Precendents) to find which cells control
which and find the source issueI hope this helps.Rémy.

Thanks.

I tried copy/paste special-numbers and the number showed up.

I tried Data/Calculate/Recalculate hard and that did nothing.

I tried Tools/Detective/Trace Precedents, and nothing obvious showed up.

I think it's a formatting issue, but it's strange that it would show up out
of the blue, all of a sudden, when before, it was working just fine.

I've attached a copy of the file with (I hope) sensitive information
deleted. I am seeing the same problems in this file as in the original. I
would be interested to see whether you find that the numbers show up for
you. I have a suspicion that they will.

I'll be able to reboot my computer shortly, and I will report back as to
whether that fixed the issue. Of course, that won't tell me what the issue
is...

#4

Hi Regina,I found your issue: the indent on your cells (like F3) is
223pt which places the text more or less 3 inches (7.9cm) to the right
of the cell. This seems to be something that was done as formatting to
the cells individually because the Default style calls for a zero
indent. Looking at your setup, the long but easier option will be for
you to select the day cells (using CTRL-drag to select more than one)
and then Format > Cells... and go to the Alignment tab: there, set the
Indent value to 0. This should bring your numbers back and they should
be left justified. Excel is not as flexible as LO when it comes time to
format the cells, so if you want to maintain the compatibility with
Excel, do not use the padding that is available on Format > Cells...
Borders if you want to move the numbers around: just use left, right,
center justification.
Some suggestions to make your life easier:You can replace the drop-down
entry of cell J1 with this formula: =TEXT(DATE(L1,1,1),"dddd"), and the
yes/no for leap year in J2 with =IF(DATE(L1+1,1,1)-
DATE(L1,1,1)>365,"yes","no"). And those should work correctly in Excel
as well.Don't hesitate if you have any other questions.Rémy.

#5

Oh, wow! Good catch! I don't know how that happened, since I didn't
deliberately do that.

I'll also try out your formula suggestion. Thanks!

Any idea how that indent could have slipped in there? I know for sure I
didn't put it there.

#6

Interestingly, the base spreadsheet that I copy and edit, has an indent of
193 pt, 30 pt less than this one. The one for 2020 has an indent of 53 pt,
and rather than being to the right, where I wanted the numbers to be,
they're somewhere to right of center. I don't have 2019 any longer, or I'd
check that, too.

This is really weird.

#7

Ok, so increasingly strange. I looked back at the PDFs for previous years,
and it looks like the numbers have been marching across the cell towards the
right for 3 years, after having started off on the left.

I've reset the base spreadsheet and this year's to zero indent, and made a
note. We'll see if it changes.