date, day number, week number in one cell

classic Classic list List threaded Threaded
9 messages Options
pda123 pda123
Reply | Threaded
Open this post in threaded view
|

date, day number, week number in one cell

p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family: "Ubuntu"; }p.cjk { font-size: 10pt; }a:link {  }
It’s
      back to the same old question;


In
      a
      calendar, how can I put the day’s date (number only) and week
      number and day-number-of-the-year in one cell?  Particularly in
      cell A5, B5...etc which are all cells that contain only the day
      number of the month.


Here’s
      a link to the Calc calendar template I’m trying to use-


https://extensions.libreoffice.org/templates/calendar-creator


The
      template is very useful and I don’t want to interfere or mess up
      the calendar creation- which it does perfectly.


Thank
      you,




Carl


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
pda123 pda123
Reply | Threaded
Open this post in threaded view
|

Re: date, day number, week number in one cell

My apologies for the messed up format.  Here's a better version;

It’s back to the same old question;

In a calendar, how can I put the day’s date (number only) and week
number and day-number-of-the-year in one cell?  Particularly in
cell A5, B5...etc which are all cells that contain only the day
number of the month.


Here’s a link to the Calc calendar template I’m trying to use-


https://extensions.libreoffice.org/templates/calendar-creator


The template is very useful and I don’t want to interfere or mess up
the calendar creation- which it does perfectly.


Thank
      you,

Carl


On 07/11/2018 07:30 PM, Carl Winerich wrote:

> p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family: "Ubuntu"; }p.cjk { font-size: 10pt; }a:link {  }
> It’s
>       back to the same old question;
>
>
> In
>       a
>       calendar, how can I put the day’s date (number only) and week
>       number and day-number-of-the-year in one cell?  Particularly in
>       cell A5, B5...etc which are all cells that contain only the day
>       number of the month.
>
>
> Here’s
>       a link to the Calc calendar template I’m trying to use-
>
>
> https://extensions.libreoffice.org/templates/calendar-creator
>
>
> The
>       template is very useful and I don’t want to interfere or mess up
>       the calendar creation- which it does perfectly.
>
>
> Thank
>       you,
>
>
>
>
> Carl
>
>


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

[SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell

In reply to this post by pda123
At 19:30 11/07/2018 -0400, Carl Winerich wrote:
>It's back to the same old question; ...

Indeed so - and the answer is very much the same as on the previous
two occasions you have asked this.

>In a calendar, how can I put the day's date (number only) and week
>number and day-number-of-the-year in one cell?

To put multiple items in one cell, you must concatenate the
individual values. You can do this using the CONCATENATE() function:
=CONCATENATE(<one>;<two>;<three>)
or probably more easily using the & operator:
=<one>&<two>&<three>
- where <one>, <two>, and <three> represent your items - references
or formulae.

Note that you will have to take care of spacing, so you may need something like
=<one>&" "&<two>&" "&<three>
instead.

Note that concatenation - expressed either way - both requires and
produces text items. If you pass it numeric values, these are
converted to text automatically on the fly. If you want more control
over exactly how a value is represented, you may want to do the
conversion yourself, using the TEXT() function, which allows you to
specify the format of the converted value. So if you wanted, say, all
your dates to appear as two-digit numbers, so the first of the month
was "01" instead of just "1", your formula might start
=TEXT(<one>;"00")&" "& ...

>Particularly in cell A5, B5...etc which are all cells that contain
>only the day number of the month. Here's a link to the Calc calendar
>template I'm trying to use-
>https://extensions.libreoffice.org/templates/calendar-creator The
>template is very useful and I don't want to interfere or mess up the
>calendar creation - which it does perfectly.

You'll have to puzzle out how to derive formulae for the required two
new values for yourself, unless the answers are buried somewhere in
the existing template. The functions WEEKNUM(), WEEKNUMADD(), and
DAYS() may help. You can read the existing formulae in your template
by selecting relevant cells and looking in the Input Line for each, of course.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

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

Re: [SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell

On 07/12/2018 04:12 AM, Brian Barker
      wrote:
At
      19:30 11/07/2018 -0400, Carl Winerich wrote:It's back to the same old question; ...Indeed so - and the answer is very much the same as on the
      previous two occasions you have asked this.In a calendar, how can I put the day's
        date (number only) and week number and day-number-of-the-year in
        one cell?To put multiple items in one cell, you must concatenate the
      individual values. You can do this using the CONCATENATE()
      function:=CONCATENATE(one;two;three)or probably more easily using theoperator:=onetwothree- whereone,two, andthreerepresent your
      items - references or formulae.Note that you will have to take care of spacing, so you may need
      something like=one" "two" "threeinstead.Note that concatenation - expressed either way - both requires and
      produces text items. If you pass it numeric values, these are
      converted to text automatically on the fly. If you want more
      control over exactly how a value is represented, you may want to
      do the conversion yourself, using the TEXT() function, which
      allows you to specify the format of the converted value. So if you
      wanted, say, all your dates to appear as two-digit numbers, so the
      first of the month was "01" instead of just "1", your formula
      might start=TEXT(one;"00")" "...Particularly in cell A5, B5...etc which
        are all cells that contain only the day number of the month.
        Here's a link to the Calc calendar template I'm trying to use-https://extensions.libreoffice.org/templates/calendar-creatorThe template is very useful and I don't want to interfere or
        mess up the calendar creation - which it does perfectly.You'll have to puzzle out how to derive formulae for the required
      two new values for yourself, unless the answers are buried
      somewhere in the existing template. The functions WEEKNUM(),
      WEEKNUMADD(), and DAYS() may help. You can read the existing
      formulae in your template by selecting relevant cells and looking
      in the Input Line for each, of course.I trust this helps.Brian BarkerBrian,I understand the WEEKNUM, and DAYS functions. Maybe the real problem is within the cell A5?Existing cell (for the month of February) has the following
        formula;=$G30-$H30+$D32+$E32+COLUMN(A5)That stuff points to various parts of the spreadsheet and
        returns the correct numeric day number of the month.When I try to concat it with, say, =DAYS then Err:510 appears in
        the cell.  Any variation of the data produces a similar error.=$G30-$H30+$D32+$E32+COLUMN(A5)"   
        "=DAYS(A5,$January.A5)+1I've successfully used concat or =text...etc as you suggested
        and placed the formula in other blank cells in an unused portion
        of the spreadsheet and have obtained the desired result.  But
        for simplicity it would be best to have the formula and result
        in the correct cell.Any ideas?Thank you,Carl
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: date, day number, week number in one cell

In reply to this post by pda123
=Not fully clear on what you want, but this is what I did in a little test.


Did this withmono font, but list might change things so they don't line up,
but sould be easy to figure out.


DateMonthDay60;WeekdayJulianweek of year01/01/1811=Monday1;1
01/02/1812=Tuesday20;101/03/1813=Wednesday360;1


The first date was in A15, and A16 was just A15+1Column B just had =month(A15) to get Month
Column C just had =day(A15) to get day
Column D to get day of Week
=INDEX($J$1:$J$7,WEEKDAY(A15))
$J$1:$J$7 have Sunday thru Saturday
Column E to calculate the Julian Day of Year
=A15-DATE(YEAR(A15),1,1)+1
Column F to get week of year
=INT(E15/7)+1


One could just use the text command andto make whatever combination
one might want??


Did the dates all the way to 12/31,




On 11 Jul 2018 at 19:30, Carl Winerich wrote:


To:;[hidden email]
From:60;Carl [hidden email]
Subject:;[libreoffice-users] date, day number, week
number in one cell
Date sent:60;Wed, 11 Jul 2018 19:30:41 -0400


p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family:Ubuntu; }p.cjk { font-size: 10pt; }a:link {}
Its
0; back to the same old question;




In
0; a
0; calendar, how can I put the days date (number only) and week
0; number and day-number-of-the-year in one cell?Particularly in
0; cell A5, B5...etc which are all cells that contain only the day</font>
0; number of the month.




Heres
0; a link to the Calc calendar template Im trying to use-




https://extensions.libreoffice.org/templates/calendar-creator




The
0; template is very useful and I dont want to interfere or mess up</span>
0; the calendar creation- which it does perfectly.




Thank
0; you,




Carl




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: date, day number, week number in one cell

Not sure why this mailing list screws up the text. Did it in a text editor, so
assumed it could handle it without screwing up the formating to much, but it
did?


DateMonthDayWeekdayJulianweek of year
01/01/1811Monday11
01/02/1812Tuesday21
01/03/1813Wednesday31


Seems it doesn't handle tabs, so changed them to blanks.
Some other stuff got thrown in there that didn't exist in the text??




On 13 Jul 2018 at 1:20, Michael D. Setzer II wrote:


=Not fully clear on what you want, but this is what I did in a little test.




Did this with mono font, but list might change things so they don't line up,
but sould be easy to figure out.




DateMonthDay60;WeekdayJulianweek of year01/01/1811=Monday1;1
01/02/1812=Tuesday20;101/03/1813=Wednesday360;1




The first date was in A15, and A16 was just A15+1Column B just had =month(A15) to get Month
Column C just had =day(A15) to get day
Column D to get day of Week
=INDEX($J$1:$J$7,WEEKDAY(A15))
$J$1:$J$7 have Sunday thru Saturday
Column E to calculate the Julian Day of Year
=A15-DATE(YEAR(A15),1,1)+1
Column F to get week of year
=INT(E15/7)+1




One could just use the text command andto make whatever combination
one might want??




Did the dates all the way to 12/31,




On 11 Jul 2018 at 19:30, Carl Winerich wrote:




To:;[hidden email]
From:60;Carl [hidden email]
Subject:;[libreoffice-users] date, day number, week
number in one cell
Date sent:60;Wed, 11 Jul 2018 19:30:41 -0400




p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family:Ubuntu; }p.cjk { font-size: 10pt; }a:link {}
Its
0; back to the same old question;




In
0; a
0; calendar, how can I put the days date (number only) and week
0; number and day-number-of-the-year in one cell?Particularly in
0; cell A5, B5...etc which are all cells that contain only the day/font
0; number of the month.




Heres
0; a link to the Calc calendar template Im trying to use-




https://extensions.libreoffice.org/templates/calendar-creator




The
0; template is very useful and I dont want to interfere or mess up/span
0; the calendar creation- which it does perfectly.




Thank
0; you,




Carl




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: date, day number, week number in one cell

Again, it stripped all the spaced?? So, there it is with spaces changed to .


Date.......Month.....Day.....Weekday.....Julian.....week.of.year
01/01/18...1.........1.......Monday.......1...........1
01/02/18...1.........2.......Tuesday......2...........1
01/03/18...1.........3.......Wednesday....3...........1




On 13 Jul 2018 at 1:37, Michael D. Setzer II wrote:


From:Michael D. Setzer [hidden email]
To:Carl [hidden email],
[hidden email]
Date sent:Fri, 13 Jul 2018 01:37:48 +1000
Subject:Re: [libreoffice-users] date, day number, week
number in one cell
Priority:normal


Not sure why this mailing list screws up the text. Did it in a text editor, so
assumed it could handle it without screwing up the formating to much, but it
did?




DateMonthDayWeekdayJulianweek of year
01/01/1811Monday11
01/02/1812Tuesday21
01/03/1813Wednesday31




Seems it doesn't handle tabs, so changed them to blanks.
Some other stuff got thrown in there that didn't exist in the text??




On 13 Jul 2018 at 1:20, Michael D. Setzer II wrote:




=Not fully clear on what you want, but this is what I did in a little test.




Did this with mono font, but list might change things so they don't line up,
but sould be easy to figure out.




DateMonthDay60;WeekdayJulianweek of year01/01/1811=Monday1;1
01/02/1812=Tuesday20;101/03/1813=Wednesday360;1




The first date was in A15, and A16 was just A15+1Column B just had =month(A15) to get Month
Column C just had =day(A15) to get day
Column D to get day of Week
=INDEX($J$1:$J$7,WEEKDAY(A15))
$J$1:$J$7 have Sunday thru Saturday
Column E to calculate the Julian Day of Year
=A15-DATE(YEAR(A15),1,1)+1
Column F to get week of year
=INT(E15/7)+1




One could just use the text command andto make whatever combination
one might want??




Did the dates all the way to 12/31,




On 11 Jul 2018 at 19:30, Carl Winerich wrote:




To:;[hidden email]
From:60;Carl [hidden email]
Subject:;[libreoffice-users] date, day number, week
number in one cell
Date sent:60;Wed, 11 Jul 2018 19:30:41 -0400




p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family:Ubuntu; }p.cjk { font-size: 10pt; }a:link {}
Its
0; back to the same old question;




In
0; a
0; calendar, how can I put the days date (number only) and week
0; number and day-number-of-the-year in one cell?Particularly in
0; cell A5, B5...etc which are all cells that contain only the day/font
0; number of the month.




Heres
0; a link to the Calc calendar template Im trying to use-




https://extensions.libreoffice.org/templates/calendar-creator




The
0; template is very useful and I dont want to interfere or mess up/span
0; the calendar creation- which it does perfectly.




Thank
0; you,




Carl




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

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

Re: [SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell

In reply to this post by Brian Barker
Solved the problem.

All I had to do was the following;

Format the selected calendar cell as follows;

DD     (then a bunch of spaces)  WW

The result returned was the correct calendar date number and the current
week of the year.

Now all I have to figure out is how the get the day-number-of-the-year
in there.

Thank you, all, for your efforts.

Carl

 





On 07/12/2018 04:12 AM, Brian Barker wrote:

> At 19:30 11/07/2018 -0400, Carl Winerich wrote:
>> It's back to the same old question; ...
>
> Indeed so - and the answer is very much the same as on the previous
> two occasions you have asked this.
>
>> In a calendar, how can I put the day's date (number only) and week
>> number and day-number-of-the-year in one cell?
>
> To put multiple items in one cell, you must concatenate the individual
> values. You can do this using the CONCATENATE() function:
> =CONCATENATE(<one>;<two>;<three>)
> or probably more easily using the & operator:
> =<one>&<two>&<three>
> - where <one>, <two>, and <three> represent your items - references or
> formulae.
>
> Note that you will have to take care of spacing, so you may need
> something like
> =<one>&" "&<two>&" "&<three>
> instead.
>
> Note that concatenation - expressed either way - both requires and
> produces text items. If you pass it numeric values, these are
> converted to text automatically on the fly. If you want more control
> over exactly how a value is represented, you may want to do the
> conversion yourself, using the TEXT() function, which allows you to
> specify the format of the converted value. So if you wanted, say, all
> your dates to appear as two-digit numbers, so the first of the month
> was "01" instead of just "1", your formula might start
> =TEXT(<one>;"00")&" "& ...
>
>> Particularly in cell A5, B5...etc which are all cells that contain
>> only the day number of the month. Here's a link to the Calc calendar
>> template I'm trying to use-
>> https://extensions.libreoffice.org/templates/calendar-creator The
>> template is very useful and I don't want to interfere or mess up the
>> calendar creation - which it does perfectly.
>
> You'll have to puzzle out how to derive formulae for the required two
> new values for yourself, unless the answers are buried somewhere in
> the existing template. The functions WEEKNUM(), WEEKNUMADD(), and
> DAYS() may help. You can read the existing formulae in your template
> by selecting relevant cells and looking in the Input Line for each, of
> course.
>
> I trust this helps.
>
> Brian Barker
>


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: date, day number, week number in one cell

In reply to this post by pda123
At 09:04 12/07/2018 -0400, Carl Winerich wrote:
>Existing cell (for the month of February) has the following formula;
>=$G30-$H30+$D32+$E32+COLUMN(A5)
>
>That stuff points to various parts of the spreadsheet and returns
>the correct numeric day number of the month. When I try to concat it
>with, say, =DAYS then Err:510 appears in the cell.

I'm not surprised: you concatenate references or expressions in a
formula, not multiple formulae. What's that extra equals sign doing
there? That's like writing =A1+=B2 instead of =A1+B2.

>Any ideas?

Yes: excise that superfluous equals sign.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy