This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi,
I have a formula that is working. It is completing the function I need, which is to show the Year and calendar date range. For this formula it shows in the header cell I created as Year 3 09/01/2027 - 08/31/2028
The formula is:
=IF(OR(E11="",G11=""),"Year 3",IF(AND(DAYS360(E11,G11)/365>=2.9,DAY($E$11)=1),"Year 3
"&TEXT(EDATE($E$11,24),"mm/dd/yy")&" - "&TEXT(EOMONTH($E$11,35),"mm/dd/yy"),IF(AND(DAYS360(E11,G11)/365>=2.96,DAY($G$11)>1),"Year 3
"&TEXT(EDATE($E$11,24),"mm/dd/yy")&" - "&TEXT(DATE(YEAR($E$11)+3,MONTH($E$11),DAY($E$11)-1),"mm/dd/yy"),IF(AND(DAYS360(E11,G11)/365>=2,DAYS360(E11,G11)/365<2.9,DAY($E$11)=1),"Year 3
"&TEXT(EDATE($E$11,24),"mm/dd/yy")&" - "&TEXT(EDATE($G$11,0),"mm/dd/yy"),IF(AND(DAYS360(E11,G11)/365>=2,DAYS360(E11,G11)/365<2.96,DAY($E$11)>1),"Year 3
"&TEXT(EDATE($E$11,24),"mm/dd/yy")&" - "&TEXT(EDATE($G$11,0),"mm/dd/yy"),IF(DAYS360(E11,G11)/365<2,"Year 3","Year 3"))))))
I have a similar formula for Year 4 and Year 5, however no matter what I do the calendar date ranges for the subsequent years will not show up.
For the Year 4 header the formula is
=IF(OR(E11="",G11=""),"Year 4",IF(AND(DAYS360(E11,G11)/365>=3.9,DAY($E$11)=1),"Year 4
"&TEXT(EDATE($E$11,36),"mm/dd/yy")&" - "&TEXT(EOMONTH($E$11,47),"mm/dd/yy"),IF(AND(DAYS360(E11,G11)/365>=3.94,DAY($G$11)>1),"Year 4
"&TEXT(EDATE($E$11,36),"mm/dd/yy")&" - "&TEXT(DATE(YEAR($E$11)+4,MONTH($E$11),DAY($E$11)-1),"mm/dd/yy"),IF(AND(DAYS360(E11,G11)/365>=3,DAYS360(E11,G11)/365<3.9,DAY($E$11)=1),"Year 4
"&TEXT(EDATE($E$11,36),"mm/dd/yy")&" - "&TEXT(EDATE($G$11,0),"mm/dd/yy"),IF(AND(DAYS360(E11,G11)/365>=3,DAYS360(E11,G11)/365<3.94,DAY($E$11)>1),"Year 4
"&TEXT(EDATE($E$11,36),"mm/dd/yy")&" - "&TEXT(EDATE($G$11,0),"mm/dd/yy"),IF(DAYS360(E11,G11)/365<3,"Year 4","Year4"))))))
If anyone could fix the formula or tell me why only Year 4 shows up and not Year 4 09/01/2028 - 08/31/2029 I would really really greatly appreciate it.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
I'd like the formula to show the result Year 4 plus the date range of for example 09/01/2028 - 08/31/2029 . The previous years (for Year 1,2,3) work but when the cell for Year 4 shows up it only shows up as Year 4. So, it'd would look like Year 4 09/01/2028 - 08/31/2029.
Can you explain in words what you want the formula to do?