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.

on
Medium Priority
828 Views
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

## View Solution Only

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.

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

Groupware 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.
##### Thanks for using Experts Exchange.

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