CellFormat weeknumber-to-date ?

[onidarbe]

Hi,

I wonder if there is a user-defined cell-format that changes a weeknumber to a date of this year?
So if I input w4 then the date 19/01/2009 should be entered. Maybe I can even choose which weekday like always widnesday, then w4 should turn into 21/1/2009

Any idea's ?

Thanks.

[acknak]

You can format a date to display as a week number, but you cannot display a week number as a date. Technically, that's not a formatting operation, that's converting one value into a related value. You need a formula to do that.

[Villeroy]

I wonder if there is a user-defined cell-format that changes a weeknumber to a date of this year?

Are you shure you want a date in "this year's" week number x? I mean, this calculation result will change over time, so the same cells will show different values next year.
What should be considered as first day of a week? Sunday or Monday?
Are you aware that 2007-12-31 was the Monday of the first week in 2008 and that the first few days of 2010 belong to the last week of 2009?

[keme]

If Villeroy's warnings don't scare you, consider the following.
I assume the rules of ISO 8601 for week numbering: Week starts on monday, and belongs to the year where most of its days are.

First we need new years day of this year:
B2 =DATE(YEAR(TODAY());1;1)

Then determine how much of that week is in last year:
B3 =WEEKDAY(B2;3)

Now we can determine the start of week 1:
B4 =B2-B3+7*(B3>4)

With this, the date can be calculated as a number of weeks from the date in B4. Week n will start 7*(n-1) days from that date.
=B4+7*(<week number>-1)

Selecting weekday is easy, just add the number of that day in the formula (0 for monday, 4 for friday, etc.)

You can make it a single formula, but it's easier to spot errors when you split it up this way (and it's more efficient too).

[Villeroy]

Week starts on monday, and belongs to the year where most of its days are.

... and this is what the simple (B3>4) does. Thank you for that. There remains one uncertainty though: 2009-12-30 will be the Monday of the first week in 2010, but starting with DATE(YEAR(TODAY());1;1) calculates the desired week number for the past year, starting from the Monday of first week in 2009, which was 2008-12-29.

[keme]

There remains one uncertainty though: 2009-12-30 will be the Monday of the first week in 2010, but starting with DATE(YEAR(TODAY());1;1) calculates the desired week number for the past year, starting from the Monday of first week in 2009, which was 2008-12-29.

The uncertainty is about whether to use the "real year" or to use the year that the current week belongs to.

I.e., if we're in week fiftysomething in january, we're week-wise still in the previous year. If we're in week 1 in december, we're week-wise already starting the next year.

So to follow the "week-year", we need a bias factor to help us determine when that year starts
B1 =AND(WEEKNUM(TODAY();3)=1;MONTH(TODAY())=12)-AND(WEEKNUM(TODAY();3)>50;MONTH(TODAY())=1)
This is normally 0, but may change to 1 or -1 for up to 3 days before or after new year.

Insert that into the formula used to determine starting date for current year.
B2 =DATE(YEAR(A1)+B1;1;1)

Whether to use "true year" or "week-year" depends on context. The user must decide.

[onidarbe]

Thanks for responding.
I guess there is no way to change weeknumber to a date with cell-formating. Only by using formula's in other cells, but that is not what I'm looking for.
Unless I use the macro-programming, which I spend months trying to make a flowless event-trapping. What is very simple in excel, but doesn't seem to be the case with OO.

Thank though...

[onidarbe]

I did find a bit of a solution: [RED][<55]#;[BLUE]WW

flaws are:
I don't see the exact date comming up in the formula-field when activating the cell.
It's not a real weeknumber unless entering a date, but I can take this in consideration if using it in formula's on other cells.

I wanded to use weeknumbers, because they take less space and thereby see more date-columns on 1 screen-width whitout having to scroll to the right. Also it reads faster when having 20 date's on a row...

Wouldn't it be a nice future of OO-calc to be able to enter formula's which can change there own cell-value? Having a formula-read-only-flag on every cell. This would be a nice future and give OO-calc much more power then Excel
Why didn't they design spreadsheets from the start with a separate formula-field from the value? Just like Note's are a separate field as the cell-value ?! If one enters a formula in a cell, it's very unlikely that one ever wants to override it with a value. So it would be more logic to separate cell-formula's from cell-value's, not?

[Villeroy]

You ask for a new calculated value based on today's date and a week number. Formatting is about different representation of the very same values. Formatting never ever changes one value into another value. That's for shure, otherwise spreadsheets were completely unusable. Intentionally, there are millions of cells on a sheet and spreadsheets work in a certain way since 25 years, so they are more or less compatible with each other as well as with their aging users.

Try keme's formulas. Put them on a sheet and format the end result to your liking. Then insert a new sheet, cut (don't copy) all the result cell's precedents into the clipboard, paste them on the new sheet and hide the new sheet. You may also hide a range on the same sheet rather than using a hidden sheet.

Now you may encounter the common spreadsheet-issue that your hidden formulas always refer to the very same set of input cells. In this particular case you can only change the single week number cell in order to get another result in the result cell, but you want a growing list of week numbers.
Easy solution: Copy down the the calculation to let it perform the same thing for all possible 53 weeks of today's year. Your visible range includes 53 week numbers and 53 results from 53 hidden rows.

What if you want to get the same calculated results from a growing list of arbitrary year numbers and week numbers?
I use to recommend databases to store raw input data for spreadsheet calculations. You can hack all your figures into another medium, which to some extent takes care about what you type into it. You can refresh the imported figures on your spreadsheet while automatically adjusting the size of the adjacent range of calculation cells, which may be hidden since you do not need to adjust them manually.
OK, for some reason this does not work for most spreadsheet users. They'd rather start Basic programming to avoid databases. I have done this already for all vertical spreadsheet lists with calculated (probably hidden) fields of arbitrary width and height: [Calc, Python] expand/shrink list ranges (works as well without any formulas, just inserting new rows into the whole width of the current region of adjacent non-empty cells)

Wouldn't it be a nice future of OO-calc to be able to enter formula's which can change there own cell-value?

Spreadsheet have a very bad reputation among professionals already because input is not strictly separated from processing and output.

[Villeroy]

You can format a date to display as a week number, but you cannot display a week number as a date. Technically, that's not a formatting operation, that's converting one value into a related value. You need a formula to do that.

The week number, year number, month number, day number, day name, month name of a date are unambiguous. But you can not convert a week number to a date as indicated in the subject line. At least you need the year, the week number and the day of week to get a date from.
Demo: You can format the result of =PI() to a date/time value [which is day number 3.14159... then] and number format "WW DDD YYYY-MM-DD HH:MM:SS" shows the curcular constant pi as day #3 plus a fraction of 0.14159 of that day:
1 Tue 1900-01-02 03:23:54 (in other words: Tuesday in week #1, Jan 02 1900 3:24 PM). You can format the same value to show any aspect of "WW DDD YYYY-MM-DD HH:MM:SS" or all together without changing the cell's value.
However it is impossible to get a date from a week number alone.

Hi,

I wonder if there is a user-defined cell-format that changes a weeknumber to a date of this year?
So if I input w4 then the date 19/01/2009 should be entered. Maybe I can even choose which weekday like always widnesday, then w4 should turn into 21/1/2009

Any idea's ?

Thanks.

keme did what you requested as exactly as possible from a given week number assuming the current year number. Even with his formula there remains a ambiguity because 2009-12-31 is within the first week of next year 2010 and you have to decide if you want a day in week #4 of 2009 or 2010.

A date is an unambiguous point in time. It's a day number which can be formatted to show only certain aspects: year, month (1-12), day number(1-31), hours, minutes, seconds, weekday name("Mon"-"Sun"), month name.
All this formatting does not change the numeric value (the day number) by any means. Any number can be formatted as date/time like any date/time shows it's day number when you strip the special formatting.

Demo: You can apply number format "WW DDD YYYY-MM-DD HH:MM:SS" to the result of formula =PI() showing the curcular constant pi (3.14159) as day #3 plus a fraction of 0.14159 of that day:
1 Tue 1900-01-02 03:23:54 (in other words: the Tuesday in week #1, Jan 02 1900 3:24 PM). You can format the same value to show any aspect of "WW DDD YYYY-MM-DD HH:MM:SS" or all together without changing the cell's value. The formatting just drops certain portions of that number. =PI() formatted as "MMMM" shows "January" because we format to show the month name only from the very same value.
However it is impossible to get a date from a week number alone. Keme's formula extracts the missing year number from today's date and adds a certain weekday number (0 to 6) to the calculated Monday in order to get any day of week from that Monday.

[morphingstar]

Villeroy: could you please explain : =$D$97-$F$97+7*(F97>4). seven times cell content larger than 4.
I know to use ><= in IF formula. But in a cell like here [F97] I fail to understand what is supposed to happen.
your date 2009-01-23.
Thanks.

[robleyd]

TRUE = 1; FALSE = 0

F97>4 can only be one of two possible values, TRUE or FALSE. I think you should be able to see where this is going.

[morphingstar]

yes, I do. Lack of practice leads to blindness .... tks.
I would have used the IF formula, had no idea about this neat compression.

[RusselB]

What is very simple in excel, but doesn't seem to be the case with OO.

Sometimes telling us how it's done in Excel can lead us to finding how to do it in Calc.