financial year in Excel

[sedgely]

I have a spreadsheet that contains a column of dates, does anyone know of a formula i could use to convert these into week numbers based on the financial year i.e week 1 starts on the first Monday of April

Cheers Craig

 

[Bowers74]

Try this:

=INT(NETWORKDAYS(DATE(YEAR(A1),4,1),A1)/5)+1

Assuming that the date you want to check is in cell A1.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[sedgely]

Thanks for that Mike, almost works - the only problem is that for the 1st April the result is "-12" where it should be "1"
Any thoughts

Craig

 

[Bowers74]

Check your date format! ;-)

I think you are checking January 4th and not April 1st!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[bluedragon2]

Will this work?

=IF(MONTH(A1)>3,WEEKNUM(A1)-13,WEEKNUM(A1)+39)

Blue

 

[Bowers74]

This is an optimized version of the above formula, it also works with dates before the date you are checking:

=IF((INT(NETWORKDAYS(DATE(YEAR(G24),4,1),G24)/5)+1)<0,(INT(NETWORKDAYS(DATE(YEAR(G24)-1,4,1),G24)/5)+1),(INT(NETWORKDAYS(DATE(YEAR(G24),4,1),G24)/5)+1))

I hope &quot;this&quot; helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[sedgely]

Hey Blue
That worked a treat

Thanks to both of you

Craig

 

[bluedragon2]

This will work for all years:

=IF(MONTH(A1)>3,WEEKNUM(A1)-(WEEKNUM(DATE(YEAR(A1),4,1))-1),52-(WEEKNUM(DATE(YEAR(A1),4,1)-1))+WEEKNUM(A1))