[Solved] Incrementing dates in NON-Adjacent cells in calc

Discuss the spreadsheet application
Post Reply
dmb1319
Posts: 7
Joined: Mon Apr 12, 2021 5:51 pm

[Solved] Incrementing dates in NON-Adjacent cells in calc

Post by dmb1319 »

This was SO easy in Excel. I have a sheet where I HAD a date picker in excel. I clicked the linked cell, a calendar popped up and I double clicked a date to enter it into the linked cell. Here's the question for this post. That date was for the first Monday of any week for a weekly schedule. Cells just to the right of the other days of the week populated with their correct dates. Cell B1 is where the Monday date was entered by selecting the Monday on the calendar date picker. Then cell A9 which had the simple formula B1+1 showed the date for the Tuesday of that week - Monday plus 1 day
Cell A16 had the formula =B1+2 showed the date for the Wednesday of that week - Monday plus 2 days
Cell D2 had the formula =B1+3 showed the date for the Thursday of that week - Monday plus 3 days (two columns, hence the shift over to column D
This obviously does not work in Calc. I've tried formatting the cells as dates Format > Number > Date and selecting a date format. That did not work
I tried selecting the destination cells for each day and using Edit > Fill > Series but Series was greyed out. I suppose because the cells are not adjacent.
I tried selecting a range of cells in a column that WERE adjacent and using the Edit > Fill > Series menu option. The selected cells populated with the dates and they incremented. I thought I was getting close!
But! when I changed the Monday date the cells in the series did not increment again 8-(

So, this is probably simple stuff. I just need some help breaking the excel paradigm - please!
Last edited by dmb1319 on Mon Apr 12, 2021 9:19 pm, edited 1 time in total.
MikeB
Open Office 4./1.9 on WinX
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Incrementing dates in NON-Adjacent cells in calc

Post by Villeroy »

It works exactly as in older versions of MS Excel. If your data entry evaluates to text, there is no way to do arithmetics with these values. Recent versions of Excel switch to a "best guess" mode and interprete numeric strings as numbers which may give wrong/unexpected results. OpenOffice and LibreOffice don't do this stunt because "7/4/21" mean two different days on both sides of the Atlantic.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dmb1319
Posts: 7
Joined: Mon Apr 12, 2021 5:51 pm

Re: Incrementing dates in NON-Adjacent cells in calc

Post by dmb1319 »

Thank you for your speedy reply.
I do understand the cell contents have to be numeric in order for Calc to perform math functions. I first converted all the cells to default format to be sure they were as plain as could be. Then I used Format Cells > Numbers > Date to format them. I tried entering dates as dates, ie 19 Jun but that's all that appeared in the cell and Calc did not increment a cell with cell reference = 1. So I tried formatting cells as numbers using Formt Cells > Numbers > Number. That didn't help. Interestingly, I have some calendar sheets from Vertex.com and they've cracked the code - OR their excel formulae work in Calc because their calendars work fine by just opening them with Calc. I haven't been able to figure out where they are copying FROM and the format for that cell though. The quest continues.
It would be helpful to have samples, something like
A1 - date (and format - numbers? Date?)
A3 - =A1+1 - or whatever that works and the format for cell A3

Thanks!
MikeB
Open Office 4./1.9 on WinX
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Incrementing dates in NON-Adjacent cells in calc

Post by Villeroy »

t105002.ods
(11.89 KiB) Downloaded 106 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dmb1319
Posts: 7
Joined: Mon Apr 12, 2021 5:51 pm

Re: Incrementing dates in NON-Adjacent cells in calc

Post by dmb1319 »

Excellent! When I tried it in col B it worked as well. So, since the formula wasn't the 'trick' it must be the cell formatting. I pressed CTRL-F8 and the cell entries turned green. I am sure that should tell me something but I don't know enough yet to know what. I tried changing the cell format for another cell - away from col A - D2 - to different formats, but doing so did not change the color of the entry there. So now I can see that the formula DOES work, I just don't know WHY it works.
THANKS!
MikeB
Open Office 4./1.9 on WinX
dmb1319
Posts: 7
Joined: Mon Apr 12, 2021 5:51 pm

Re: Incrementing dates in NON-Adjacent cells in calc

Post by dmb1319 »

Ah - Ha! The light came on and the elevator ran clear up to the top floor!
I was entering a DATE (which is a NUMBER) as text - 19 Apr
When I entered it in a date format - 04/19/2021 - Calc recognized it right away and did the math correctly. I thought that since I had formatted the cell in the date format dd MON yyyy that Calc would recognize a date enered as 19 apr 2021. I guess that's where I went wrong.
MikeB
Open Office 4./1.9 on WinX
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Incrementing dates in NON-Adjacent cells in calc

Post by Villeroy »

Well, Ctrl+F8 shows numbers in blue and formulas in green. =A1+1 returns a number if A1 is a number or #VALUE! if A1 is a string.

There is one exception: If a numeric string is just a sequence of digits or an ISO date (yyyy-mm-dd) or an ISO time (hh:mm:ss), Calc does the same dirty trick as Excel does. ISO values and sequences of digits are unambiguous. They don't mean different things on both sides of the Atlantic.
A1: ="2021-04-12" (unambiguous ISO date)
=A1+1 --> 2021-04-13
---------------------
A1: ="12/04/2021"
=A1+1 --> #VALUE! (not clear if 12th of April or 4th of December)

Likewise the string ="1.234" may mean one-thousand-two-hundred-thirty-four (e.g. in German or French context) or one point two three four in English context.

Excel files are full of bullshit simply because Excel handles bullshit so well.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dmb1319
Posts: 7
Joined: Mon Apr 12, 2021 5:51 pm

Re: [Solved] Incrementing dates in NON-Adjacent cells in cal

Post by dmb1319 »

Those are some good notes for this Calc newbie. THANKS!
MikeB
Open Office 4./1.9 on WinX
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Incrementing dates in NON-Adjacent cells in cal

Post by robleyd »

You might find [Tutorial] Ten concepts that very Calc user should know to be a useful resource.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply