Formula Assistance (Years)

Gohar Shahab

Active Member
Joined
May 17, 2006
Messages
339
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
Joined
May 28, 2005
Messages
48,501
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Maybe

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,501
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 17, 2006
Messages
339

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
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,265
Members
416,963
Latest member
samfuge

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top