Formula Assistance (Years)

[Gohar Shahab]

I have got following formula that gives me dates in the following format upto the year 2009-10, i want to add in this formula now 2010-11 which starts from 01.01.2010 to 31.12.2010, how to do that...or any other formula to handle it.

=IF(E131="","",YEAR(E131)-(MONTH(E131)<6)&"-"&RIGHT(YEAR(E131)+(MONTH(E131)>5),2))

kindly assist.

 

[Peter_SSs]

Not clear to me. Can you explain in more detail? Examples of what could be in E131 and the corresponding result(s) should help.

 

[dave3009]

Maybe

=IF(E1="","",YEAR(E1))&"-"&RIGHT(YEAR(E1)+(MONTH(E1)>=1),2)

 

[Peter_SSs]

Maybe

=IF(E1="","",YEAR(E1))&"-"&RIGHT(YEAR(E1)+(MONTH(E1)>=1),2)

If this is what is required, then it could be simplified to

=IF(E1="","",YEAR(E1))&"-"&RIGHT(YEAR(E1)+1,2)

since, for any date MONTH(date)>=1 will always be True

 

[Gohar Shahab]

ADVERTISEMENT

If this is what is required, then it could be simplified to

=IF(E1="","",YEAR(E1))&"-"&RIGHT(YEAR(E1)+1,2)

since, for any date MONTH(date)>=1 will always be True

Thanks Peter

But i need to include this statement in the original formula where some dates will be from the history therefore to be put in within the original formual i provided. E131 is the inception date of the contract and basis of the year.

hope this clarifies

 

[Peter_SSs]

hope this clarifies

It doesn't for me, but sample data and expected results might.

 

[barry houdini]

Your original formula defines the year start as Jun 1st, if you now want the year start for 2010-2011 to be Jan 1st then what do you want to do in the transition period?

You already get 2009-2010 for dates from 1st Jun 2009 to 31st May 2010 and you now say that 2010-2011 is 1st Jan 2010 to 31st Dec 2010 so there's an overlap between those. If you want 2009-2010 to be only dates from Jun to Dec 2009 try

=IF(E131="","",IF(YEAR(E131)>=2010,YEAR(E131)&"-"&TEXT(E131,"yy")+1,YEAR(E131+214)-1&"-"&RIGHT(YEAR(E131+214),2)))