Dear Mynda
I hope you can help.
Our Financial Year runs from 01 July – 30 June therefore Period 1 will be July, Period 2 will be August, Period 3 will be September, etc.
All I want is a Concatenated text string that looks as follows where the Period Number sequentially increases based on the month of the financial year:
Period # - 2020/21
NB: If you can automate the financial year in the above order as part of the concatenated text string, I would be extremely grateful.
See attached spreadsheet. At least I tried and actually got to something but to me there are too many variables i.e. formula is too long – some context below:
In Cell H4 is today’s date i.e. TODAY()
In Cell H1 is the formula to extract the Number of the Month from Today’s Date (Cell H4)
In Cell G1 is a very, very long IF Statement to extract the Period Number based on the Number of the Month in the Financial Year, and finally
In Cell C5 is how I got the Period Number to increase and do the concatenation of the text string.
I hope you or somebody can help by shortening the formula or make one formula in Cell C5 to get the desired result based on the Date in cell H4.
Regards.
Amien
="Period "&IF(AND(MONTH(H4)>6,MONTH(H4)<=12),MONTH(H4)-6,IF(AND(MONTH(H4)>=1,MONTH(H4)<=6),MONTH(H4)+6,""))&" - "&YEAR(H4)-1&"/"&RIGHT(YEAR(H4),2)
Hello,
You can use following formula to get the period number.
=MATCH(1,MONTH(H4)={7,8,9,10,11,12,1,2,3,4,5,6},0)
In short, MATCH function looks for a 1, in other words for the match of the month number and the numbers in sequence, and return the position of the sequence number. March is month 3 and number three is in position 9 in the sequence, so you get 9 as the result.
If you want all this in one formula in C5, just replace the G1 reference with the above formula.
Br,
Anders
Thanks Anders and Vasim for the solutions.
Anders: I cannot get your solution to work. I see some curly brackets in there and also tried CTRL+SHFT+ENTER, still not working....
Vasim: Yours seems to do the trick but the financial year in the text string does not change e.g. when it coms to 2021/07/01 (01 July 2021) the financial year must change to 2021/22 and 2022/07/01 (01 July 2022), financial year must change to 2022/23, etc.
Hope we can get it to work.
Regards,
Amien
Hello,
My bad, should have tested the formula on a computer also, worked fine for some reason on Excel app on my tablet.
See attached for an example. The formula now in cell C5 is in this example as following (split in different rows for better readability):
=CONCATENATE(
"Period ",
MATCH(TRUE,MONTH($H$4)={7,8,9,10,11,12,1,2,3,4,5,6},0),
" - "&IF(MONTH($H$4)>6,YEAR($H$4)&"/"&RIGHT(YEAR($H$4)+1,2),YEAR($H$4)-1&"/"&RIGHT(YEAR($H$4),2))
)
Hope this helps you further.
Br,
Anders
P.S. If you are on older version of Excel than 2019 or 365 then you need to use CTRL + SHIFT + ENTER.
Hi Anders
You a real STAR! This time, it works perfectly.
I will unpack the formula so that I can understand the context and practice on my own.
Great stuff!
Regards,
Amien.
Hello,
Thanks for the kind words. Just glad I was able to help.
Br,
Anders
