Solved

Calculate week number offset against a date.

Posted on 2006-11-08
7
779 Views
Last Modified: 2013-12-18
Hi,

I currently use the following formula to calculate the current week number:

Datum:= @If(@IsTime(Date); Date; @Now);
y:=@Year(Datum);
jan1:=@Date(y;1;1);
day1:= @Adjust(jan1; 0; 0; 3-@Modulo(@Weekday(jan1)+1; 7); 0; 0; 0);
jan1l:=@Date(y-1;1;1);
day1l:=@Adjust(jan1l; 0; 0; 3-@Modulo(@Weekday(jan1l)+1; 7); 0; 0; 0);
jan1n:=@Date(y+1;1;1);
day1n:=@Adjust(jan1n; 0; 0; 3-@Modulo(@Weekday(jan1n)+1; 7); 0; 0; 0);
offset:=@If(Datum<day1; day1l; Datum<day1n; day1; day1n);
@Integer((Datum-offset)/86400/7)+1

What I would like to do is calculate the week number from the first Saturday after the 6th April of each year.

So for example if the first Saturday in April is the 7th then this would be week 1
if the first Saturday is the 5th then this would be week 53

Is there any way the above formula can be modified to achieve this?
0
Comment
Question by:shuboarder
  • 3
  • 2
  • 2
7 Comments
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
Sure, but I am not a formula expert.

I would think that all you need to do is change the beginning date to work from April 6 instead of Jan 1.

I hope this helps !
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
So first would be to calculate the first Sat after April 6, and then plug it in whenever Jan1 is used.

SOmeone here can figure it out.

0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
You might try to set jan1 (which is used as reference date) to your preferred reference date, but there are some weird snags in the standard weeknumber computation. No, won't work. If someone hasn't solved this puzzle by tomorrow, I'll certainly give it a try...

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 250 total points
Comment Utility
Some info missing: a week starts on what day: Sat/Sun/Mon?

Try with this code, it assumes Monday as the first day of the week:

Datum:= @If(@IsTime(Date); Date; @Now);
y:=@Year(Datum);
jan1:=@Date(y;4;6);
day1:= @Adjust(jan1; 0; 0; 5-@Modulo(@Weekday(jan1)+1; 7); 0; 0; 0);
jan1l:=@Date(y-1;4;6);
day1l:=@Adjust(jan1l; 0; 0; 5-@Modulo(@Weekday(jan1l)+1; 7); 0; 0; 0);
jan1n:=@Date(y+1;4;6);
day1n:=@Adjust(jan1n; 0; 0; 5-@Modulo(@Weekday(jan1n)+1; 7); 0; 0; 0);
offset:=@If(Datum<day1; day1l; Datum<day1n; day1; day1n);
@Integer((Datum-offset)/86400/7)+1

Not tested, just applied logic...
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Striking resemblance... but I've never seen it before, honestly.
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
Great minds think alike! :)  I didn't notice the resemblance, to tell the truth.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now