I'm trying to calculate the number of months in a given time range that fall within 2019.
My data looks like this:
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:
- If Start Date (year) < 2019 AND End date (year) = 2019, take the number of months between 1-1-2019 and the end date.
- If start Date (year) < 2019 AND End date (year) > 2019, the number of months in 2019 = 12
- If start Date (year) = 2019 AND End date (year) = 2019, take the number of months between Start Date and End date.
- If start Date (year) = 2019 AND End date (year) > 2019, take the number of months between Start Date and 1-1-2020.
- 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:
=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))))
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
Aucun commentaire:
Enregistrer un commentaire