Convert date to week number for fiscal year starting July 25

**ARayburn** · 05-14-2013, 10:34 AM

I need to convert dates to week numbers for a spreadsheet where week number 1 starts monday July 25, 2012. Any help would be greatly appreciated.

**nathansav** · 05-14-2013, 10:42 AM

=WEEKNUM(I8-206)

206 is the offset in days to your financial year, I have only quickly tested it,

**daddylonglegs** · 05-14-2013, 10:44 AM

If it's only for dates within that fiscal year try this formula for week number (assuming a date in A1)

=INT((A1-DATE(2012,7,25))/7)+1

format as number

If you want a formula that will work for any year then do you have a rule for when the year starts, e.g. last Monday in July?

**ARayburn** · 05-14-2013, 11:03 AM

Thank you everyone it worked!

**ARayburn** · 05-15-2013, 09:07 AM

I actually had to do WEEKNUM(I8-176) for it to work for 2012-2013 fiscal year starting 07/25/2012. I have another issue. I am trying to do the same for 2013-2014 fiscal year starting 07/01/2013 ending 06/29/2014. I am having an issue when i do WEEKNUM(A1-181) I am only getting 5 days in week 1. Why is it doing this? I have tried playing around with the numbers and can not get 07/01/2013-07/07/2013 to show week 1.

**daddylonglegs** · 05-15-2013, 10:03 AM

That's the way WEEKNUM works - week 1 always starts on 1st Jan and then week 2 starts on the next Sunday, so sometimes week 1 has only 1 day! It only works co-incidentally for 2012 because that year started with a Sunday. The version I suggested should work for you if you change the start date within the formula.

It seems strange that 2013 starts on 1st July when 2012 started on 25th? Is 25th right, it's not a Monday?