Prev: Merging Columns
Next: Excel 2007 -- Dates in Pivot Tables in wrong order after refresh
From: Mark D on 8 Mar 2010 05:44
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
From: David Biddulph groups [at] on 8 Mar 2010 05:58
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")
--
David Biddulph


"Mark D" wrote:

> 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
From: Mark D on 8 Mar 2010 06:02
Awesome, thank you so much

"David Biddulph" wrote:

> 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")
> --
> David Biddulph
>
>
> "Mark D" wrote:
>
> > 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
 | 
Pages: 1
Prev: Merging Columns
Next: Excel 2007 -- Dates in Pivot Tables in wrong order after refresh