CellFormat weeknumber-to-date ?

Discuss the spreadsheet application

CellFormat weeknumber-to-date ?

Postby onidarbe » Fri Jan 23, 2009 3:26 am

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.
OOo 3.1.X on Ms Windows XP
User avatar
onidarbe
 
Posts: 84
Joined: Thu Nov 29, 2007 8:03 pm
Location: Belgium, Mechelen

Re: CellFormat weeknumber-to-date ?

Postby acknak » Fri Jan 23, 2009 5:17 am

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: CellFormat weeknumber-to-date ?

Postby Villeroy » Fri Jan 23, 2009 10:38 am

onidarbe wrote: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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CellFormat weeknumber-to-date ?

Postby keme » Fri Jan 23, 2009 4:09 pm

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).
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3208
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: CellFormat weeknumber-to-date ?

Postby Villeroy » Fri Jan 23, 2009 5:06 pm

keme wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CellFormat weeknumber-to-date ?

Postby keme » Sat Jan 24, 2009 1:40 am

Villeroy wrote: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.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3208
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: CellFormat weeknumber-to-date ?

Postby onidarbe » Sun Jan 25, 2009 11:53 am

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...
OOo 3.1.X on Ms Windows XP
User avatar
onidarbe
 
Posts: 84
Joined: Thu Nov 29, 2007 8:03 pm
Location: Belgium, Mechelen

Re: CellFormat weeknumber-to-date ?

Postby onidarbe » Sun Jan 25, 2009 1:01 pm

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

:idea: 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?
OOo 3.1.X on Ms Windows XP
User avatar
onidarbe
 
Posts: 84
Joined: Thu Nov 29, 2007 8:03 pm
Location: Belgium, Mechelen

Re: CellFormat weeknumber-to-date ?

Postby Villeroy » Sun Jan 25, 2009 1:22 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CellFormat weeknumber-to-date ?

Postby Villeroy » Sun Jan 25, 2009 7:09 pm

acknak wrote: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.

onidarbe wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CellFormat weeknumber-to-date ?

Postby morphingstar » Fri Jul 12, 2019 4:53 am

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.
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 65
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: CellFormat weeknumber-to-date ?

Postby robleyd » Fri Jul 12, 2019 4:57 am

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.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2858
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: CellFormat weeknumber-to-date ?

Postby morphingstar » Fri Jul 12, 2019 5:10 am

yes, I do. Lack of practice leads to blindness .... tks.
I would have used the IF formula, had no idea about this neat compression.
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 65
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: CellFormat weeknumber-to-date ?

Postby RusselB » Fri Jul 12, 2019 5:29 am

onidarbe wrote: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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5282
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests