Calc week number problem

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

Calc week number problem

I found a LO Calc spreadsheet that provides the year’s day number,
    remaining days and week number. It's attached herein.Depending upon the year it’s tedious to move each week number, which
    begins on Sunday, so that the week number is in the row which
    contains a Sunday.Is there a way to modify the sheet in columns F, J, N, R, V, Z so
    that beside each “Sun” (the row in which “Sun” appears) the correct
    week number will be provided? Thanks,Peter (at least I think that's my name...today)   p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family: "Ubuntu"; }p.cjk { font-size: 10pt; }
--
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: Calc week number problem

Don't know  how the formatting here gets wrecked.  Is there a way to
attach a file?  Looks like the one added didn't "attach"

Peter


On 12/22/18 3:56 PM, . wrote:
> I found a LO Calc spreadsheet that provides the year’s day number,
>     remaining days and week number. It's attached herein.Depending upon the year it’s tedious to move each week number, which
>     begins on Sunday, so that the week number is in the row which
>     contains a Sunday.Is there a way to modify the sheet in columns F, J, N, R, V, Z so
>     that beside each “Sun” (the row in which “Sun” appears) the correct
>     week number will be provided? Thanks,Peter (at least I think that's my name...today)   p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family: "Ubuntu"; }p.cjk { font-size: 10pt; }


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

Re: Calc week number problem

Howdy,

On Sat, Dec 22, 2018 at 5:17 PM . <[hidden email]> wrote:

> Don't know  how the formatting here gets wrecked.  Is there a way to
> attach a file?


Yes - by posting the question using the TDF web site at
https://ask.libreoffice.org/en/questions/ which has full support for
attaching files.

The mailing list however does not support attached files, for the ML  you
would need to supply a url to a networked storage location of your choosing
in the text.

Personally, I can't think of a good answer for you question off the top of
my head and I would like to look as the spreadsheet to see if maybe it
helps to spark an idea of one.

Of course maybe someone else will have a solution just from the description
in your text.

Best wishes,

Drew



> Looks like the one added didn't "attach"
>
> Peter
>
>
> On 12/22/18 3:56 PM, . wrote:
> > I found a LO Calc spreadsheet that provides the year’s day number,
> >     remaining days and week number. It's attached herein.Depending upon
> the year it’s tedious to move each week number, which
> >     begins on Sunday, so that the week number is in the row which
> >     contains a Sunday.Is there a way to modify the sheet in columns F,
> J, N, R, V, Z so
> >     that beside each “Sun” (the row in which “Sun” appears) the correct
> >     week number will be provided? Thanks,Peter (at least I think that's
> my name...today)   p { margin-bottom: 0.1in; line-height: 120%; }p.western
> { font-family: "Ubuntu"; }p.cjk { font-size: 10pt; }
>
>
> --
> 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: Calc week number problem

In reply to this post by pda123
Not clear on what he wanted without seeing the sheet.
Did a test with column a have dates from 1/1/2019 to 12/31/2019
Column B had =Days(a1,"12/31/2018") to get the day of year
Column C had =Weeknum(a1) to get week number
Column D created the Text formula to create the listing.

=TEXT(DAYS(A1,"12/31/2018"),"000")&" day within year, WEEK
"&TEXT(WEEKNUM(A1),"0")&" of year, day of week "&TEXT(A1,"DDD")

001 day within year, WEEK 1 of year, day of week Tue
002 day within year, WEEK 1 of year, day of week Wed
003 day within year, WEEK 1 of year, day of week Thu
004 day within year, WEEK 1 of year, day of week Fri
005 day within year, WEEK 1 of year, day of week Sat
006 day within year, WEEK 2 of year, day of week Sun

Not sure if that would help at all.


On 22 Dec 2018 at 15:56, . wrote:

To:             [hidden email]
From:           "." <[hidden email]>
Subject:         [libreoffice-users] Calc week number problem
Date sent:       Sat, 22 Dec 2018 15:56:46 -0500

> I found a LO Calc spreadsheet that provides the year´s day number,
>     remaining days and week number. It's attached herein.Depending upon the year it´s tedious to move each week number, which
>     begins on Sunday, so that the week number is in the row which
>     contains a Sunday.Is there a way to modify the sheet in columns F, J, N, R, V, Z so
>     that beside each "Sun" (the row in which "Sun" appears) the correct
>     week number will be provided? Thanks,Peter (at least I think that's my name...today)   p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family: "Ubuntu"; }p.cjk { font-size: 10pt; }
> --
> 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 - Computer Science Instructor (Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS

ROSETTA      66308620.404055 | ABC          16613838.513356
SETI        109773059.474214 | EINSTEIN    141794097.999240


--
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: Calc week number problem

In reply to this post by drew-gmail
Thank you, all.

Here's the updated post with attached file on TDF-
https://ask.libreoffice.org/en/question/177121/calc-week-number-problem/

Peter




On 12/22/18 5:57 PM, Drew Jensen wrote:

> Howdy,
>
> On Sat, Dec 22, 2018 at 5:17 PM . <[hidden email]> wrote:
>
>> Don't know  how the formatting here gets wrecked.  Is there a way to
>> attach a file?
>
> Yes - by posting the question using the TDF web site at
> https://ask.libreoffice.org/en/questions/ which has full support for
> attaching files.
>
> The mailing list however does not support attached files, for the ML  you
> would need to supply a url to a networked storage location of your choosing
> in the text.
>
> Personally, I can't think of a good answer for you question off the top of
> my head and I would like to look as the spreadsheet to see if maybe it
> helps to spark an idea of one.
>
> Of course maybe someone else will have a solution just from the description
> in your text.
>
> Best wishes,
>
> Drew
>
>
>
>> Looks like the one added didn't "attach"
>>
>> Peter
>>
>>
>> On 12/22/18 3:56 PM, . wrote:
>>> I found a LO Calc spreadsheet that provides the year’s day number,
>>>      remaining days and week number. It's attached herein.Depending upon
>> the year it’s tedious to move each week number, which
>>>      begins on Sunday, so that the week number is in the row which
>>>      contains a Sunday.Is there a way to modify the sheet in columns F,
>> J, N, R, V, Z so
>>>      that beside each “Sun” (the row in which “Sun” appears) the correct
>>>      week number will be provided? Thanks,Peter (at least I think that's
>> my name...today)   p { margin-bottom: 0.1in; line-height: 120%; }p.western
>> { font-family: "Ubuntu"; }p.cjk { font-size: 10pt; }
>>
>>
>> --
>> 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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Calc week number problem

In reply to this post by pda123
At 15:56 22/12/2018 -0500, Dotty Peter wrote:
>I found a LO Calc spreadsheet that provides the year's day number,
>remaining days and week number.

Found? Most people compose spreadsheets.

>It's attached herein.

Er, it's not: the mailing list processor normally strips attached
files before messages are distributed. In any case, no-one really
want to mend your spreadsheet for you; instead, you should be
prepared to explain the nub of your problem so that others can
address it directly.

>Depending upon the year it's tedious to move each week number, which
>begins on Sunday, so that the week number is in the row which
>contains a Sunday. Is there a way [...] so that beside each "Sun"
>(the row in which "Sun" appears) the correct week number will be provided?

And what would you like in that cell if the row is not a Sunday? I'm
guessing perhaps nothing.

I'm hoping that your "Sun" is actually a date, formatted as "NN". In
that case, you may just need something like
=IF(WEEKDAY(Xn)=1;WEEKNUM(Xn;1);"")
- where Xn is the cell containing the date formatted to appear as
"Sun", "Mon", etc. or any other column in the same row containing that date.

If your "Sun" is text, either use the date cell from which it is
derived as above or else something like
=IF(Xn="Sun";WEEKNUM(Yn;1);"")
- where Xn is the cell containing the text "Sun", "Mon", etc. and Yn
is a cell in the same row containing the actual date.

Here's another possibility:
=IF(WEEKDAY(Xn)=1;TEXT(Xn;"WW");"")

And another:
=IF(WEEKDAY(Xn)=1;Xn;"")
- and format the column as "WW".

>Thanks, Peter (at least I think that's my name...today)

Incidentally, when writing to strangers, please have the courtesy,
maturity, and good sense to put your name (just *a* name?) in the
real name field of your messages. Some people are called Dot, but
no-one's called ".". Thanks.

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: Calc week number problem

In reply to this post by pda123
At 19:09 22/12/2018 -0500, Dotty Peter wrote:
>Here's the updated post with attached file ...

The simplest solution is probably my first suggestion: wrapping
=IF(WEEKDAY(Xn)=1; ... ;"")
around your existing formula.

Note that, unlike the author of this spreadsheet, who has chosen the
tedious job of inserting the week number formula only in those cells
in which a visible value is needed, you can fill this formula (or any
of my suggestions) down the relevant columns. They will show a value
only for Sundays, as you require. In using this technique, you will
have learned how to use spreadsheets more efficiently than the
existing document's author.

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: Calc week number problem

Brian,

The first formula you provided works perfectly.

Thanks,

Peter


On 12/22/18 8:08 PM, Brian Barker wrote:

> At 19:09 22/12/2018 -0500, Dotty Peter wrote:
>> Here's the updated post with attached file ...
>
> The simplest solution is probably my first suggestion: wrapping
> =IF(WEEKDAY(Xn)=1; ... ;"")
> around your existing formula.
>
> Note that, unlike the author of this spreadsheet, who has chosen the
> tedious job of inserting the week number formula only in those cells
> in which a visible value is needed, you can fill this formula (or any
> of my suggestions) down the relevant columns. They will show a value
> only for Sundays, as you require. In using this technique, you will
> have learned how to use spreadsheets more efficiently than the
> existing document's author.
>
> 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
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: Calc week number problem

In reply to this post by pda123

On 22-12-2018 21:56, . wrote:
> I found a LO Calc spreadsheet that provides the year’s day number,
>      remaining days and week number. It's attached herein.Depending upon the year it’s tedious to move each week number, which
>      begins on Sunday, so that the week number is in the row which
>      contains a Sunday.Is there a way to modify the sheet in columns F, J, N, R, V, Z so
>      that beside each “Sun” (the row in which “Sun” appears) the correct
>      week number will be provided? Thanks,Peter (at least I think that's my name...today)   p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family: "Ubuntu"; }p.cjk { font-size: 10pt; }


In F8, the formula:

=IF(WEEKDAY(C8;1)=1;WEEKNUM(C8;1);"")

Copy the formula down until F77 (without F39:F46), and change is for the
other week columns.


--
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
Windows7 / LibreOffice  3.3 330m19(Build:6)