Fiscal Week 1 starting 1st Saturday of July

Hello to all!!!

I've searched many options on how start a Fiscal year. Some ones are very good ones but focused in that a date will always be the "Start" if the Fiscal Year. In my case is a little different, my fiscal will always start the first Saturday of July.

Can anyone help me to set a formula that give me the week number starting the 1st Saturday of July??

I had found this formula and is very close, but returns the week number starting on Sundays.

Formula:

Please Login or Register  to view this content.

For example: Jul-05-2014 to Jul-11-2014 must be in Week 1. With the formula above only Saturday Jul-05 is in Week 1, the remaining are in week 2.

The goal is to have all of them in Week 1 and to consider that the 1st Saturday of July will not always be Jul-05. In 2015, the 1st Saturday of July will be July-04.

Your help will be very much appreciate it.

Best regards to all and please have a great day.

Hi there.

Try this. =DATE(A1,7,1+((1-(7>=WEEKDAY(DATE(A1,7,1))))*7)+(7-WEEKDAY(DATE(A1,7,1))))

With A1 and coming down is date
From B1 and drag down:

Please Login or Register  to view this content.

Thank you Glenn and Bebo!!!

Your work was awesome!!! Thank you very much!!! I applied both formulas to test them and finally the one I choose was bebo's formula. I added reputation to both of you for your prompt help.

Best regards to both and please have the best of weekends.
(Saludos cordiales a ambos y por favor tengan lo mejor del fin de semana).

Thanks for the \reputation. It's appreciated.

bebo's formula works for me but you can do this more simply by adapting my ISO week number formula.

This formula gives you ISO week numbers (where week 1 starts on the first Monday on or after 29th December)

=INT((A1-WEEKDAY(A1-1)-DATE(YEAR(A1+4-WEEKDAY(A1-1)),1,4))/7)+2

To modify that for week 1 starting on the first Saturday in July you get this version:

=INT((A1-WEEKDAY(A1+1)-DATE(YEAR(A1+185-WEEKDAY(A1+1))-1,7,7))/7)+2

If you are using Excel 2010 or later you can also use this formula to get the same result for "current" years

=WEEKNUM(A1-7124,21)

....but that will go wrong after 1st July 2102