[Calc] Automate creation of series of full date (ie. Day name, Date)?

users
#1

Hello

I often use Calc to prepare multi-day trips, and have to write series that
look like this:

Mon 1 Aug
Tue 2 Aug
Wed 3 Aug
etc.

Does Calc come with a way to automate this tedious task, or is there some
macro that I could copy/paste from somewhere?

Thank you.

#2

You can just enter the first two values (A1 and A2), format them however
you want (highlight them, right click -> format cell -> tab to "Number" or
"Format" - forget exact wording, set to date and the way you want it to
look, exit the dialog, then select A1 and A2 (highlight), select (left
click and hold) on the bottom right corner, then just drag it down. It will
fill down for you.

Best,
Joel

#3

Thanks but no go: Even after telling LO that it's a Date, it simply copies
the contents instead of incrementing it:

Right-click > Format Cells > Numbers >
Category = Date
OK

Drag:

Mon 1 Aug
Mon 1 Aug
Mon 1 Aug
Mon 1 Aug
etc.

#4

Found it: What you type in the cell must match exactly the format chosen in
the Format Cells dialog, in the Format section.

For instance, if you choose "Fri. 31 Dec. 99", that is exactly what you must
type for LO to figure things out.

Thanks a lot!

#5

I'm interested in that observation - might be worth reporting a bug or
enhancement request. You shouldn't have to type exactly the same format (if
you apply the format after you type it in the cells). Can you shoot me an
email privately with a simple attachment with a sample of what did not work
for you? I'll take a look and report it if I see an issue.

Best,
Joel

#6

I simply typed "Mon 1 Aug" and expected LO to figure it out.

But I guess the doco should say that user must first start with a simple
DD/MM/YYYY, before telling LO that it's a date, and clicking to get a
series. Computers are not *that* smart :wink:

#7

Ah yes I do see that (Excel has same behavior). I would have quoted "smart"
instead of "that" :wink:

But I think that detecting Mon 1 Aug as a date makes sense. I'll file an
enhancement request.

Best,
Joel

#8

To make matters worse - or more interesting, progammatically speaking -, I
could be running the French version of LO and type "Mon" instead of "Lun"
:wink:

#9

Funny enough if you remove Monday from the entry and just put Aug 1 and
then Aug 2, things work :wink:

#10

Right.

Makes sense: If a column or set of cells was formated as Date, LO knows what
to do with it. But users might start with a full "Mon 1 Aug" and try to get
LO to complete the series automagically.

#11

Yup yup, thus why it's a good request. Although - what do you think should
happen if they put "M 1 Aug" or even worse if they put "T 1 Aug" when 1-Aug
is actually a Monday....could become a bit tricky (as any automagic stuff
tends to become).

Best,
Joel

#12

I use this a lot.
Format the column the way you want it.
Put a date in the first cell
A formula goes in the cell below =A1+1
Fill from that formula down
Copy the the entries
Paste Special with values into the same area.

Works for me

John

#13

Generally speaking, you can enter dates any way you want and get the
resulting date displayed in the way how you format the cells. Formatting
the cells does not affect the input method.
"Normal" spreadsheet programs (not LO Calc) accept date input like this:
5/ --> 5th of current month
5/6 --> this year's 5th of June or 6th of May with US English locale
5/6/14 --> 5th of June 2014 according to the global settings for 2-digit
years. You can enter digits together with localized month names
according to the current locale setting. It recognizes short and long
month names of 180 languages.

In any case you should _never_ enter any weekday names.

Entering dates into LibreOffice is particularly difficult since version
3.6. To make above mentioned input patterns working in LibreOffice, you
need to adjust the "date recognition patterns" in the language settings.

The output with or without weekday names, month names 2 or 4 digit years
looks like you formatted the cells. The formatting of a cell never makes
any difference. The value remains the same.

PLEASE turn on menu:View>HighlightValues [Ctrl+F8]. This highlights all
numeric values in blue font. If your dates are displayed in black font,
they are no dates at all.
If you do not set the alignment of the cell, all numbers are right
aligned to the cell border whereas all text values are left aligned.
For any valid date in A1, =ISNUMBER(A1) should return TRUE.

#14

P.S.

In any case you should _never_ enter any weekday names.

Entering dates into LibreOffice is particularly difficult since version
3.6. To make above mentioned input patterns working in LibreOffice, you
need to adjust the "date recognition patterns" in the language settings.

something like
1 apr
13 april
13 April 2015
works independently from the "recognition patterns" if the month names
match your current locale.
The locale is the language setting in the cell format dialog and it
defaults to the locale setting in the global language settings.

#15

Use Edit > Fill > Series

Click in cell A2
Right click Format Cells > date > Fri, 31Dec 99 > OK
With A2 still selected shift click A6
Click Edit>Fill>Series
When the dialog box opens
Select Down > Date > Day
Enter start date: 3/08/2015
Enter finish date: 7/08/2015
Increment: 1
Click OK

Result:
Mon, 3 Aug 15
Tue, 4 Aug 15
Wed, 5 Aug 15
Thu, 6 Aug 15
Fri, 7 Aug 15

The secret i using the Edit > Fill > Series menu options

So now you have the dates, with days in the A column you could put time along the the row 1.
Starting at B1 and use the Edit > Fill > Series >Right > Date . Start 08:00, End 12:00 Increment 1:00.
Just ensure that the Cell Format for the Row is set to Time rather than Date.

Hope this helps.

"I came into this world with nothing.
Fortunately I have got most of it left."
Tony Bray
tonybsa@me.com

#16

This topic as started by Gilles is about dates. Dragging down a single
number (currency, date, time, whatever) increases the value by one which
is one day.
Problem is that too many spreadsheet users do not know what a
spreadsheet date/time is and how it differs from a text such as
"16/07/2015" which is not a date by any means.

For intervals other than one day you can use the fill-series dialog _or_
enter 2 start values (e.g. 8:00 and 9:00), select the two values and
drag down the interval.

#17

Andreas Säger wrote:

> Problem is that too many spreadsheet users do not know what a
> spreadsheet date/time is and how it differs from a text such as
> "16/07/2015" which is not a date by any means.

Actually it is if you add the pattern to Options > Language Settings > Languages > Date Acceptance Patterns.
I had previously added D/M/Y and "16/07/2015" (without the quotes) was perfectly recognized as a date. To keep the formatting you must also adapt the cell formatting.

#18

Hi :slight_smile:
If it's spread out into 2 columns (or rows) such that the day-of-the-week
(eg Mon) is in the first cell and then the date part is in the next cell
then both drag down fine. (I had to set the format of the cell before it
let me show the date in "MMM d" format but that's a side-issue imo).

So there are quite a few different ways of getting variants on the various
pre-programmed auto-complete series. It's been a good thread imo with a
good variety of answers so i learned quite a bit there. Thanks folks! :slight_smile:
Regards from
Tom :slight_smile:

#19

Like
Sunday 13/7/2015
Monday 14/7/2015
Tuesday 15/7/2015
...

Works perfectly well even though 13/7/15 was a Monday.

You _never_ need to enter any weekday names into a spreadsheet when you
have the dates. This is redundant information which is likely to get out
of sync. A valid date contains the information about the correct weekday
already.

1) Format the dates to show their correct weekdays together with the date.
2) Use a reference to the date cell and format as DDD or DDDD to show
the weekday name in another cell.
3) Use =TEXT(A1;"DDD") to show the weekday name of A1 as a text value.
But do not enter redundant information.