Problem: DateDif, count quarters + IF statements

[berger01]

Good Day,

In the attached I am trying to accomplish: (Row 5)

1. Count How many calendar quarters (3 mo) between columns "H" and "A", IF column F is blank.
2. IF column "F" is not blank, then count quarters between columns "F" and "A".

And

3. IF column "P" is a (1) then I want to subtract 12 months from the count.
4. IF column "P" is a (2) then I want to subtract 24 months from the count.
5. IF column "P" is a (3) then I want to subtract 36 months from the count.

To get the quarters, I am attempting to devide by 4 at the end of the formula.

Here is what I have so far:

=IFERROR(IF(OR(AND(P5=1, IF(ISBLANK(F5),DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))-12,IF(OR(AND(P5=2, IF(ISBLANK(F5),DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))-24,IF(OR(AND(P5=3, IF(ISBLANK(F5),DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))-36,)),0)/4

I'm not even sure if I'm close. any help would be great......

Thank you

[ConneXionLost]

First, you'll need to convert column A from text to date format. Then, try:

=MAX(0,IF(ISBLANK(F5),DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))/4-P5*12)

Cheers,

[berger01]

Thank you. I do see where the other IF Statements have been developed. However the result from the suggested formula is null. This is the one I am trying:


=MAX(0,IF(ISBLANK(F5),DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))/4-P5*12)

[DGagnon]

Try out this formula and see if it is getting the results you are after

=IFERROR((IF(F5="",DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))-IFERROR(P5*12,0))/4,"")

[berger01]

This looks helpful, thanks. Can you take a shot at the other IF statements:

3. IF column "P" is a (1) then I want to subtract 12 months from the count.
4. IF column "P" is a (2) then I want to subtract 24 months from the count.
5. IF column "P" is a (3) then I want to subtract 36 months from the count.

[ConneXionLost]

That's what the "-P5*12" is doing in the formula.

[DGagnon]

that should be already included in both mine and ConneZionLost's solutions.

[ConneXionLost]

I used a MAX function to eliminate the return of negative values. If you want to see them then try:

=IF(ISBLANK(F5),DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))/4-P5*12

Cheers,

[ConneXionLost]

Oops, lets try this by subtracting quarters instead of months:

=MAX(0,IF(ISBLANK(F5),DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))/4-P5*4)

=IF(ISBLANK(F5),DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))/4-P5*4

[berger01]

Wow, both of you are awesome. I think I have it now and am just fine tunning. I will post with the final result that works best and mark as solved here in a momement.

thanks so much.

[berger01]

Thanks to both of you, I have used this suggestion with success:

=IFERROR((IF(F5="",DATEDIF(H5,A5,"m"),DATEDIF(F5,A5,"m"))-IFERROR(P5*12,0))/4,"0")