1

I'm trying to calculate the number of months in a given time range that fall within 2019.

My data looks like this:

enter image description here

I changed the format of my Start Date and End Date columns to "date". The "Duration in months" and output column have number formatting.

I approached it as follows:

  1. If Start Date (year) < 2019 AND End date (year) = 2019, take the number of months between 1-1-2019 and the end date.
  2. If start Date (year) < 2019 AND End date (year) > 2019, the number of months in 2019 = 12
  3. If start Date (year) = 2019 AND End date (year) = 2019, take the number of months between Start Date and End date.
  4. If start Date (year) = 2019 AND End date (year) > 2019, take the number of months between Start Date and 1-1-2020.
  5. For all other cases, the number of months = 0.

I then followed the instructions from this website for nested IF functions.

I came up with the following formula: (Edit: changed last datedif to 2020 instead of 2019)

=IF(AND(YEAR(A3)<2019;YEAR(C3)=2019);DATEDIF(DATE(2019;1;1);C3;"m");IF(AND(YEAR(A3)<2019;YEAR(C3)>2019);12;IF(AND(YEAR(A3)=2019;YEAR(C3)=2019);DATEDIF(A3;C3;"m");IF(AND(YEAR(A3)=2019;YEAR(C3)>2019); DATEDIF(A3;DATE(2020;1;1);m);0))))

For the first 4 rows, it correctly returns 12. For row 7 and 8, however, it returns #NAME? .

No matter what I try, I can't seem to get it to work. Any ideas on how I can solve this?

Much appreciated!

Amy

JvdV
  • 70,606
  • 8
  • 39
  • 70
AmyV
  • 63
  • 3
  • 7
  • You deleted your last question too quickly. I have a shorter version of your formula see: https://stackoverflow.com/questions/59289520/non-adjecent-cells-as-input-to-array-function-min-and-isblank – Scott Craner Dec 11 '19 at 15:54

3 Answers3

2

For the entire formula you have been consistent, apart from the last IF statement, where you went wrong within your DATEDIF( statement:

DATEDIF(A3;DATE(2019;1;1);m);0)

First of all you forgot to put m in quotes: "m"

Secondly you accidentally swapped the Date and Cell references, so it would have resolved #NUM. The full correct formula should be:

=IF(AND(YEAR(A7)<2019;YEAR(C7)=2019);DATEDIF(DATE(2019;1;1);C7;"m");IF(AND(YEAR(A7)<2019;YEAR(C7)>2019);12;IF(AND(YEAR(A7)=2019;YEAR(C7)=2019);DATEDIF(A7;C7;"m");IF(AND(YEAR(A7)=2019;YEAR(C7)>2019);DATEDIF(DATE(2019;1;1);A7;"m");0))))

Which will give you the result 4.

Plutian
  • 2,276
  • 3
  • 14
  • 23
1

Alternatively, the following will give you the amount of full months between two dates. This works with your sample where your dates start on the first day of a month:

enter image description here

Formula in D2:

=SUMPRODUCT((DATE(2019,ROW($1:$12),1)>=A2)*(DATE(2019,ROW($1:$12),1)<=C2))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Interesting approach. The expected output in the bottom 2 rows, however, is 4 instead of 8. (If it started in May 2019, only 4 months fall within 2019). Would this approach also work in that case? – AmyV Dec 11 '19 at 12:09
  • Actually, you're right! It should be 8 in that case. I got confused myself. I still can't seem to get this formula working, however. Could you explain how it is supposed to work? – AmyV Dec 11 '19 at 12:26
  • The idea is return an array of 1st days of each month in 2019 and check if they exists withing the range of the two given dates @AmyV. Can you tell me what about this you can't get to work? Have you started on your sample data? – JvdV Dec 11 '19 at 12:36
  • @AmyV, have you changed the comma's for semi-colons? – JvdV Dec 11 '19 at 13:16
  • I did put in semi-colons instead. It does not seem to make a difference. I get the "you entered too many arguments for this function" pop-up. – AmyV Dec 11 '19 at 15:33
  • I see what went wrong, there is a comma in the `2019` value when I translated it automatically @AmyV, please see edit – JvdV Dec 11 '19 at 15:54
0

You've missed the quotes round the final "m" - try

=IF(AND(YEAR(A3)<2019;YEAR(C3)=2019);DATEDIF(DATE(2019;1;1);C3;"m");IF(AND(YEAR(A3)<2019;YEAR(C3)>2019);12;IF(AND(YEAR(A3)=2019;YEAR(C3)=2019);DATEDIF(A3;C3;"m");IF(AND(YEAR(A3)=2019;YEAR(C3)>2019); DATEDIF(A3;DATE(2019;1;1);"m");0))))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Thanks, I missed that. It does not seem to solve the issue, however. The error message only changed to #NUM! for the two final rows. – AmyV Dec 11 '19 at 11:55