DATEDIF To show negative

[LilPeteMordino]

Apologies. I have had a quick look but can't really find anything that I can utilise.

I have a relatively simple formula that takes an estimated completion date against a required completion date and gives me the number of months between the two. All happy days, however if the estimate goes past the required, it is showing a negative. How can I fix this please?

=IFERROR((DATEDIF(H14,G14,"m")), "")

So H14 is Estimated, G14 is Required.

Unless I have this all wrong!

Cheers in advance.

 

[MARK858]

Assuming you meant "however if the estimate goes past the required, it is showing an error....

Test if H14>G14, if it is then reverse the formula and multiply by minus 1 (I've left the IFERROR in but I doubt you'll need it)

Book1
	G	H	I	J
14	01/06/2024	01/04/2024		2
15	01/04/2024	01/06/2024		-2
Sheet1

Cell Formulas
Range	Formula
J14:J15	J14	=IFERROR(IF(H14>G14,DATEDIF(G14,H14,"m")*-1,DATEDIF(H14,G14,"m")),"")

 

[LilPeteMordino]

Test if H14>G14, if it is then reverse the formula and multiply by minus 1 (I've left the IFERROR in but I doubt you'll need it)

Book1
	G	H	I	J
14	01/06/2024	01/04/2024		2
15	01/04/2024	01/06/2024		-2
Sheet1

Cell Formulas
Range	Formula
J14:J15	J14	=IFERROR(IF(H14>G14,DATEDIF(G14,H14,"m")*-1,DATEDIF(H14,G14,"m")),"")

That's worked perfectly! Thank you. I didn't even think about incorporating an IF to consider the arrangement first.

The IFERROR is included because I have 'pre-populated' a table with the next two years worth of calculations (Its a sheet that pulls monthly data in and shows the trends). So it just keeps the sheet looking tidy.

 

[MARK858]

You're welcome