If(text(U:U,"mm">6,....

[cbarth]

Good Morning,

I am trying to create a formula where I can get the fiscal year based of the effective date in column U in my spreadsheet. This is the formula I have in my spreadsheet.

=IF(TEXT(U:U,"mm")>6,TEXT(U:U,"yyy")+1,TEXT(U:U,"yy"))

The problem is it has everything as "true" so it is putting everything in the following fiscal year. For instance if the date is 1/5/2009 that would be fiscal year 2009 but the formula above gives me 2010. However, if the date is 7/8/2009 it gives me 2010 which is be correct. The cut off is June 30th of each year so I thought if I just used the month of June so "mm" greater than 6 I would be okay.

How do I correct this formula to make it so that it gives me the "false" part of the equation?

Thanks you,
Carmen

[Pete_UK]

TEXT returns a text result (naturally), so you can't compare the value "06" , or whatever the month is, directly with 6 - why not just use MONTH to get the month from the date (as a number)?

Hope this helps.

Pete

[Jonmo1]

The TEXT function returns...well...TEXT
Even if the value looks like a number, Excel considers it a Text String
Try IF(TEXT(cell,"mm")+0>6,...

or even just use the MONTH function
IF(MONTH(cell)>6,..

[romperstomper]

Try this:
=TEXT(U2,"yy")+(month(U2)>6))
or if you want the full year:
=YEAR(U2)+(month(U2)>6))
and copy down.

[cbarth]

Thank you for the help everyone. I was haven't used the month and year functions before. It worked much better for what I was trying to do. I'll keep them in my memory bank.

Have a great day,
Carmen