# Effective Month/Date Calc - multiple years

Discussion in 'Business Applications' started by Gevans0666, Apr 30, 2019.

Not open for further replies.

Joined:
Sep 6, 2018
Messages:
40
Okay - so for the most part this is working - thanks to you, but I have run into a slight issue recently

Issue: 2 dates where both "Contract Start Date" equals "Contract Expiry Date"
Result should be "1" (as the contract while only 1 day was effective within 1 month)

I appear to have figured out columns A & B by using MAX(0, but unsure if there is a more effective alternative?

Current Formula for the 4 columns are:

Col C Previous to this year
=IF(OR(J16<DATE(2018,1,1),G16>DATE(2018,12,31)),0,DATEDIF(IF(G16<DATE(2018,1,1),DATE(2018,1,1),G16),IF(J16>DATE(2018,12,31),DATE(2018,12,31),J16)+15,"m"))

Col D current year
=IF(OR(J16<DATE(2019,1,1),G16>DATE(2019,12,31)),0,DATEDIF(IF(G16<DATE(2019,1,1),DATE(2019,1,1),G16),IF(J16>DATE(2019,12,31),DATE(2019,12,31),J16)+15,"m"))

Col E Next Year
=IF(OR(J16<DATE(2020,1,1),G16>DATE(2020,12,31)),0,DATEDIF(IF(G16<DATE(2020,1,1),DATE(2020,1,1),G16),IF(J16>DATE(2020,12,31),DATE(2020,12,31),J16)+15,"m"))

Col F Balance
=IF(J16<DATE(2021,1,1),0,DATEDIF(IF(G16<DATE(2021,1,1),DATE(2021,1,1),G16),J16+15,"m"))

File size:
11.7 KB
Views:
2
2. ### Chawbacon

Joined:
Jul 9, 2018
Messages:
381
First Name:
Jack
Hello again!

You may want to consider turning the formula into a Nested IF statement; such as:

For Column C
=IF((G16=J16),1,(IF(OR(J16<DATE(2018,1,1),G16>DATE(2018,12,31)),0,DATEDIF(IF(G16<DATE(2018,1,1),DATE(2018,1,1),G16),IF(J16>DATE(2018,12,31),DATE(2018,12,31),J16)+15,"m"))))

I hope this helps,

Joined:
Sep 6, 2018
Messages:
40
Almost - but in a further calculation to breakdown by month, it doesn't recognize the "1":

=IFERROR(IF(AND(G16<=DATE(2019,1,1),J16>=DATE(2019,1,1)),K16/D16),0)

File size:
12.4 KB
Views:
2
4. ### Chawbacon

Joined:
Jul 9, 2018
Messages:
381
First Name:
Jack
Hello again,

My apologies for the slow response; however, I have been unavailable due to multiple work projects.

As for the formula. The "1" should be recognized; however, the formula for the "L" column is testing for a specific date range. When there is not a date present, or when the dates do not meet the predefined range, the "False" message will be displayed.

You can test for a a blank cell by using the ISBlank option; however, this will still result in "False" values where the the dates do not meet the predefined range.

=IF(ISBLANK(J16),0,IFERROR(IF(AND(G16<=DATE(2019,1,1),J16>=DATE(2019,1,1)),K16/D16),0))

Are you looking for a "Zero" value when the dates do not meet the indicated range criteria?

Joined:
Sep 6, 2018
Messages:
40
Yes!

Joined:
Sep 6, 2018
Messages:
40
=IF((G16=J16),1,(IF(OR(J16<DATE(2018,1,1),G16>DATE(2018,12,31)),0,DATEDIF(IF(G16<DATE(2018,1,1),DATE(2018,1,1),G16),IF(J16>DATE(2018,12,31),DATE(2018,12,31),J16)+15,"m"))))
The above is now giving the "1" but on ALL years - not just 2018

=IF(ISBLANK(J16),0,IFERROR(IF(AND(G16<=DATE(2019,1,1),J16>=DATE(2019,1,1)),K16/D16),0))

The above works great and the zero result would be nice (aesthetically) but I can live without the zero - and it to be FALSE if I had to

Joined:
Sep 6, 2018
Messages:
40
To clarify, the most important one is this one as it drives everything else... how many months are active (in this fiscal year) between 2 dates:

=IF((G16=J16),1,(IF(OR(J16<DATE(2019,1,1),G16>DATE(2019,12,31)),0,DATEDIF(IF(G16<DATE(2019,1,1),DATE(2019,1,1),G16),IF(J16>DATE(2019,12,31),DATE(20189,12,31),J16)+15,"m")))

Some Dates cover months: 01 Jan 2019 to 31 Mar 2019 (results in 3)

Some cover only 1 day but need to be counted as an active month: 01 Jan 2019 to 01 Jan 2019 (results in 1)

Some will cover other years not current and therefore not active this current fiscal. 01 Jan 2021 to 01 Jan 2021 (results in 0)

Currently - it's doing what I want - except for the out years ... it's counting 1's for anything after 31 Dec 2019

I hope I've explained correctly

G

Joined:
Sep 6, 2018
Messages:
40
Here is the file with an example

#### Attached Files:

• ###### Date - Month Effective Formulas.xlsx
File size:
12.9 KB
Views:
1

Joined:
Sep 6, 2018
Messages:
40
@Chawbacon ... also - no apologies for 'slow response" - I appreciate each and every effort you guys make!

10. ### Chawbacon

Joined:
Jul 9, 2018
Messages:
381
First Name:
Jack
Alrighty! I had to look at this cross-eyed for a few minutes.

Try modifying the formula for the "Effective Current Fiscal Year2" column (D) to check the year value within the "Calc Date" column (G), and return a value of zero if the year is greater than 2019:

=IF(YEAR(G18)>2019,0,IF(G18=J18,1,IF(OR(J18<DATE(2019,1,1),G18>DATE(2019,12,31)),0,DATEDIF(IF(G18<DATE(2019,1,1),DATE(2019,1,1),G18),IF(J18>DATE(2019,12,31),DATE(2019,12,31),J18)+15,"m"))))

I will have to ponder on the Months formula a bit. Is the IFERROR function a necessity for that column, or could a simple IF statement be used? I am thinking something along the lines of:

=IF(AND(\$G19<=DATE(2019,3,1),\$J19>=DATE(2019,3,1)),\$K19/\$D19,IF(D19=0,0,IF(ISBLANK(\$J19),0,\$K19/\$D19)))

Oh... If you want to display zeros in the months columns, you will have to change the cell format to something other than "Accounting"... or force the formula to display text by quoting the return; but, this could cause challenges if you try to make calculations based upon the cell containing the text value.

=IF(AND(\$G19<=DATE(2019,3,1),\$J19>=DATE(2019,3,1)),\$K19/\$D19,IF(D19=0,"0",IF(ISBLANK(\$J19),"0",\$K19/\$D19)))

As Seen On