ratio using DATEDIF function

[Mark D]

Morning everyone

I am trying to put 2 formulas into one cell but seem to be struggling, I am
sure this is simple and could really do with some help

A1 = 4
B1 = 12/02/2007
C1 = 08/03/2010

In D1 I am doing the following forumla (I am trying to get a result of 4:36)

=A1&":" + DATEDIF(B1,C1,"M")

I keep getting VALUE returned back not the 4:36 I am after

Any help appreciated

Thanks

 

[David Biddulph]

The reason you are getting #VALUE! is that you have a text string "4:", and
then you are trying to add it with a plus sign which denotes an arithmetic
operation. An arithmetic operation on a text string will tend to give you a
#VALUE! error.

You correctly had the concatenate operator & in the first part of the
formula, and you need the samne again in the second part.
Change =A1&":" + DATEDIF(B1,C1,"M") to =A1&":" & DATEDIF(B1,C1,"M")

 

[Mark D]

Awesome, thank you so much

The reason you are getting #VALUE! is that you have a text string "4:", and
then you are trying to add it with a plus sign which denotes an arithmetic
operation. An arithmetic operation on a text string will tend to give you a
#VALUE! error.

You correctly had the concatenate operator & in the first part of the
formula, and you need the samne again in the second part.
Change =A1&":" + DATEDIF(B1,C1,"M") to =A1&":" & DATEDIF(B1,C1,"M")