Calc week number problem

users
#1

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; }

#2

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

#3

Howdy,

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

#4

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.

#5

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

#6

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

#7

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

#8

Brian,

The first formula you provided works perfectly.

Thanks,

Peter

#9

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.