We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Calculate week number offset against a date.

shuboarder
shuboarder asked
on
Medium Priority
828 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?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2007

Commented:
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 !
CERTIFIED EXPERT
Top Expert 2007

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

Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

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

Groupware Consultant
CERTIFIED EXPERT
Commented:
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...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
Striking resemblance... but I've never seen it before, honestly.

Commented:
Great minds think alike! :)  I didn't notice the resemblance, to tell the truth.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.