Combine TEXT function with the YEAR and MONTH function

[jski21]

Hi Excelers,

Is there a process to combine the TEXT function with the YEAR and MONTH function? I'm using this formula to calculate the fiscal year:

=YEAR(A1)-(MONTH(A1)<J35)

It works just fine. The result however, displays the date as 1905 because Cell A1 is =TODAY() and I believe I need to precede the formula above in a TEXT function to get it to display properly? Cell J35 is simply the start month of the fiscal year (June).

As always, thanks for your review, consideration, and help.


jski

 

[Joe4]

I find your question a little confusing.
Subtracting month from the year does not seem to make much sense to me.
Can you show us exactly what you want the expected result to look like?

 

[queuesmef]

What result are you trying to get? Are you trying to get the period for something? or the month....please elaborate
Perhaps you just need to FORMAT the cell differently?

 

[queuesmef]

In Column A I have the dates: 6/1/22, 7/1/22 through 5/1/23; in Cell E1 I have the following formula:

=YEAR(A1)&"-"&TEXT(IF(MONTH(A1)<6,MONTH(A1)+7,MONTH(A1)-5),"00")

which will give you YYYY-PP -4 digit year followed by a '-' followed by the number of the Period.

 

[jski21]

Thanks Joe4. Sure:

PR 05 - Drawdown Report by Project and Activity.xlsx
	G	H	I	J	K	L	M
32			Current:				Desired:
33			February 17, 2022				February 17, 2022
34
35		Budget Plan Year	July 1905	6		Budget Plan Year	June 2021
Pivot

Cell Formulas
Range	Formula
I33,M33	I33	=TODAY()
I35	I35	=YEAR(I33)-(MONTH(I33)<J35)

Is this helpful?

 

[Joe4]

Try using this formula, and format the resulting cell with the date format "mmmm-yyyy":

=IF(MONTH(I33)<J35,DATE(YEAR(I33)-1,6,1),DATE(YEAR(I33),6,1))

 

[jski21]

Both of these solutions worked. Joe4's seems to fit the best in the context of what I'm trying to do. Thanks everyone.

 

[Joe4]

You are welcome.
Glad we could help!