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

classic Classic list List threaded Threaded
19 messages Options
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

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.
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

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

On Wed, Jul 15, 2015 at 1:06 PM, Gilles <[hidden email]> wrote:

> 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.
>

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


--
*Joel Madero*
LibreOffice QA Volunteer
[hidden email]

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

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.
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

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!
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

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

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

On Wed, Jul 15, 2015 at 1:24 PM, Gilles <[hidden email]> wrote:

> 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!
>
>
>
> --
> View this message in context:
> http://nabble.documentfoundation.org/Calc-Automate-creation-of-series-of-full-date-ie-Day-name-Date-tp4154586p4154589.html
> Sent from the Users mailing list archive at Nabble.com.
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>


--
*Joel Madero*
LibreOffice QA Volunteer
[hidden email]

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

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 ;-)
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

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

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

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


Best,
Joel

On Wed, Jul 15, 2015 at 1:45 PM, Gilles <[hidden email]> wrote:

> 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 ;-)
>
>
>
> --
> View this message in context:
> http://nabble.documentfoundation.org/Calc-Automate-creation-of-series-of-full-date-ie-Day-name-Date-tp4154586p4154591.html
> Sent from the Users mailing list archive at Nabble.com.
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>


--
*Joel Madero*
LibreOffice QA Volunteer
[hidden email]

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

To make matters worse - or more interesting, progammatically speaking -, I could be running the French version of LO and type "Mon" instead of "Lun" ;-)
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by jmadero
Funny enough if you remove Monday from the entry and just put Aug 1 and
then Aug 2, things work ;)

On Wed, Jul 15, 2015 at 2:06 PM, Joel Madero <[hidden email]> wrote:

> Ah yes I do see that (Excel has same behavior). I would have quoted
> "smart" instead of "that" ;)
>
> But I think that detecting Mon 1 Aug as a date makes sense. I'll file an
> enhancement request.
>
>
> Best,
> Joel
>
> On Wed, Jul 15, 2015 at 1:45 PM, Gilles <[hidden email]> wrote:
>
>> 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 ;-)
>>
>>
>>
>> --
>> View this message in context:
>> http://nabble.documentfoundation.org/Calc-Automate-creation-of-series-of-full-date-ie-Day-name-Date-tp4154586p4154591.html
>> Sent from the Users mailing list archive at Nabble.com.
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems?
>> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
>> List archive: http://listarchives.libreoffice.org/global/users/
>> All messages sent to this list will be publicly archived and cannot be
>> deleted
>>
>>
>
>
> --
> *Joel Madero*
> LibreOffice QA Volunteer
> [hidden email]
>
>


--
*Joel Madero*
LibreOffice QA Volunteer
[hidden email]

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

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.
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

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

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

On Wed, Jul 15, 2015 at 2:14 PM, Gilles <[hidden email]> wrote:

> 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.
>
>
>
> --
> View this message in context:
> http://nabble.documentfoundation.org/Calc-Automate-creation-of-series-of-full-date-ie-Day-name-Date-tp4154586p4154595.html
> Sent from the Users mailing list archive at Nabble.com.
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>


--
*Joel Madero*
LibreOffice QA Volunteer
[hidden email]

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
John & Ruth Sparrow John & Ruth Sparrow
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
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



On 16/07/2015 6:24 AM, Gilles wrote:

> 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!
>
>
>
> --
> View this message in context: http://nabble.documentfoundation.org/Calc-Automate-creation-of-series-of-full-date-ie-Day-name-Date-tp4154586p4154589.html
> Sent from the Users mailing list archive at Nabble.com.
>


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
Am 15.07.2015 um 22:06 schrieb Gilles:

> 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.
>


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.

------------------------------
Now for the series of dates:
------------------------------
Enter one valid date so it appears in blue font and/or right aligned.
Drag down the cell handle (little square in the bottom-right corner).
Easy, isn't it?


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

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

P.S.

Am 16.07.2015 um 16:05 schrieb Andreas Säger:

> 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.



--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

tonybsa tonybsa
Reply | Threaded
Open this post in threaded view
|

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

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
[hidden email]




--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

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

Am 16.07.2015 um 18:49 schrieb tonybsa:
>
> 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.
>

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.


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Piet van Oostrum-2 Piet van Oostrum-2
Reply | Threaded
Open this post in threaded view
|

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

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.
--
Piet van Oostrum <[hidden email]>
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

TomD TomD
Reply | Threaded
Open this post in threaded view
|

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

Hi :)
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! :)
Regards from
Tom :)



On 16 July 2015 at 20:44, Piet van Oostrum <[hidden email]>
wrote:

> 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.
> --
> Piet van Oostrum <[hidden email]>
> WWW: http://pietvanoostrum.com/
> PGP key: [8DAE142BE17999C4]
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

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

Am 16.07.2015 um 23:08 schrieb Tom Davies:
> Hi :)
> 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).
>

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.


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted