Hi,
I'm working on an excel function after many years away and I have come a little unstuck. I understand the problem I am just struggling to find a solution to it and would appreciate any help anyone might be able to offer.
The function is straightforward
<code>
=SUMPRODUCT(--(DATEDIF(People!$H2:$H1000,AB$1,"m")=1))
</code>
The people sheet has a range of dates on it
e.g.
Jan-18
Mar-18
Jun-18
Aug-19
AB$1 is a date e.g Jun-18
The issue I have is that DATEDIF throws a #num error if the start date is greater than the end date. This causes the error to permeate up through the SUMPRODUCT. Is there a way to protect against the error within the SUMPRODUCT or switch the DATEDIF around dynamically.
e.g. I would do this outside of a SUMPRODUCT when working with DATEDIF
<code>
=IFERROR(DATEDIF($H2:$H2000,$AB1,"M"),DATEDIF($AB1,$H2:$H2000,"M")
</code>
Any help welcome
Thanks
I'm working on an excel function after many years away and I have come a little unstuck. I understand the problem I am just struggling to find a solution to it and would appreciate any help anyone might be able to offer.
The function is straightforward
<code>
=SUMPRODUCT(--(DATEDIF(People!$H2:$H1000,AB$1,"m")=1))
</code>
The people sheet has a range of dates on it
e.g.
Jan-18
Mar-18
Jun-18
Aug-19
AB$1 is a date e.g Jun-18
The issue I have is that DATEDIF throws a #num error if the start date is greater than the end date. This causes the error to permeate up through the SUMPRODUCT. Is there a way to protect against the error within the SUMPRODUCT or switch the DATEDIF around dynamically.
e.g. I would do this outside of a SUMPRODUCT when working with DATEDIF
<code>
=IFERROR(DATEDIF($H2:$H2000,$AB1,"M"),DATEDIF($AB1,$H2:$H2000,"M")
</code>
Any help welcome
Thanks