Formula Assistance (Years)

Gohar Shahab

Active Member
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.

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Peter_SSs

MrExcel MVP, Moderator
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

Well-known Member
Maybe

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

Peter_SSs

MrExcel MVP, Moderator
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

Active Member
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

MrExcel MVP, Moderator
hope this clarifies
It doesn't for me, but sample data and expected results might.

barry houdini

MrExcel MVP
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)))

Last edited:

Replies
5
Views
168
Replies
6
Views
192
Replies
4
Views
247
Replies
12
Views
383
Replies
4
Views
196

1,196,027
Messages
6,012,947
Members
441,740
Latest member
abaz21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back