I have a formula which works out my Week Number according to our financial week.
=if(ISBLANK(B3), "", if(month(B3) >= 4, TEXT(B3,"YY") & TEXT(WEEKNUM(B3)-13,"0#"), TEXT(B3, "YY") & WEEKNUM(B3)+39))
this would make 01/04/18 > 1801
the problem i Have is that our financial year is FY19 so I need this number to say 1901. I cannot figure out how to increment within the text field.. has anyone run into a similar problem before? I'm trying to keep this as one formula so it's as elegant as possible..
https://docs.google.com/spreadsheets/d/1fPYUK2WhsdpFzIFJgKGivSWgbR_WQWYLhmk150erqtI/edit?usp=sharing
Sheet with current formula and desired result.
Best Answer
Took me a while but finally got it, I had to use ABS (absolute sum) before the YEAR sum and +1 writing a script in python tipped me off when I had to call .astype(str) in pandas.
this gives me 1901 as expected.