I am trying to get a formula which calculates the difference of two dates in
dd/mm/yy format. Eg: I have 'Joining Date' and 'Resigned Date' in A1 & B1
respectievly.

A B
1 28/05/1996 11/08/2008 = 15/03/12 (B1-A1),(Total no. of days
worked is 4458)

whereas the correct answer is 14/02/12. I tried the formula
DATEDIF(C6,D6,"md")&"/"&DATEDIF(C6,D6,"ym")&"/"&DATEDIF(C6,D6,"y"); I got the
answer as'14/2/12', but the total working days is different which is not
relevent for further calculations.

Please help.....
--
Geo

On Mon, 11 Aug 2008 08:17:10 -0700, Geo wrote:

I am trying to get a formula which calculates the difference of two dates in
dd/mm/yy format. Eg: I have 'Joining Date' and 'Resigned Date' in A1 & B1
respectievly.

A B
1 28/05/1996 11/08/2008 = 15/03/12 (B1-A1),(Total no. of days
worked is 4458)

whereas the correct answer is 14/02/12. I tried the formula
DATEDIF(C6,D6,"md")&"/"&DATEDIF(C6,D6,"ym")&"/"&DATEDIF(C6,D6,"y"); I got the
answer as'14/2/12', but the total working days is different which is not
relevent for further calculations.

Please help.....

You've got to format your numbers:

=TEXT(DATEDIF(C6,D6,"md"),"00")&"/"&
TEXT(DATEDIF(C6,D6,"ym"),"00")&"/"&
TEXT(DATEDIF(C6,D6,"y"),"00")

--ron

Thanks ron, but the same problem exists. The total working days would
actually be 4458, but by your formula I get a total no. of 40953 which is not
suitable for further calculations.
--
Geo


"Ron Rosenfeld" wrote:

On Mon, 11 Aug 2008 08:17:10 -0700, Geo wrote:

I am trying to get a formula which calculates the difference of two dates in
dd/mm/yy format. Eg: I have 'Joining Date' and 'Resigned Date' in A1 & B1
respectievly.

A B
1 28/05/1996 11/08/2008 = 15/03/12 (B1-A1),(Total no. of days
worked is 4458)

whereas the correct answer is 14/02/12. I tried the formula
DATEDIF(C6,D6,"md")&"/"&DATEDIF(C6,D6,"ym")&"/"&DATEDIF(C6,D6,"y"); I got the
answer as'14/2/12', but the total working days is different which is not
relevent for further calculations.

Please help.....

You've got to format your numbers:

=TEXT(DATEDIF(C6,D6,"md"),"00")&"/"&
TEXT(DATEDIF(C6,D6,"ym"),"00")&"/"&
TEXT(DATEDIF(C6,D6,"y"),"00")

--ron

Ron's formula returns 14 days, 2 months and 12 years which seem to be
correct.


--


Regards,


Peo Sjoblom

"Geo" wrote in message
...
Thanks ron, but the same problem exists. The total working days would
actually be 4458, but by your formula I get a total no. of 40953 which is
not
suitable for further calculations.
--
Geo


"Ron Rosenfeld" wrote:

On Mon, 11 Aug 2008 08:17:10 -0700, Geo
wrote:

I am trying to get a formula which calculates the difference of two
dates in
dd/mm/yy format. Eg: I have 'Joining Date' and 'Resigned Date' in A1 &
B1
respectievly.

A B
1 28/05/1996 11/08/2008 = 15/03/12 (B1-A1),(Total no. of days
worked is 4458)

whereas the correct answer is 14/02/12. I tried the formula
DATEDIF(C6,D6,"md")&"/"&DATEDIF(C6,D6,"ym")&"/"&DATEDIF(C6,D6,"y"); I
got the
answer as'14/2/12', but the total working days is different which is not
relevent for further calculations.

Please help.....

You've got to format your numbers:

=TEXT(DATEDIF(C6,D6,"md"),"00")&"/"&
TEXT(DATEDIF(C6,D6,"ym"),"00")&"/"&
TEXT(DATEDIF(C6,D6,"y"),"00")

--ron

On Mon, 11 Aug 2008 09:02:01 -0700, Geo wrote:

Thanks ron, but the same problem exists. The total working days would
actually be 4458, but by your formula I get a total no. of 40953 which is not
suitable for further calculations.
--
Geo

I guess I'm not sure what your problem is.

The formula I used is the one you used, merely formatting the returned values
to get it into the form you said you wanted:

You wrote:

whereas the correct answer is 14/02/12.

The formula I gave you -- really just your formula displayed differently,
applied to your data returns 14/02/12. You must be doing something different
if you are getting 40953 as a result.

Seems like you need to be more clear as to what you want.

After all, if 14/02/12 is the correct answer, and the formula returns 14/02/12,
it's difficult to know what the problem is.
--ron

As it happens 40953 equals the serial date number for Feb 14 2012 which
happens to be
equal to the formula result as a date and not a display of 14 days 2 months
and 12 years

--


Regards,


Peo Sjoblom

"Ron Rosenfeld" wrote in message
...
On Mon, 11 Aug 2008 09:02:01 -0700, Geo
wrote:

Thanks ron, but the same problem exists. The total working days would
actually be 4458, but by your formula I get a total no. of 40953 which is
not
suitable for further calculations.
--
Geo

I guess I'm not sure what your problem is.

The formula I used is the one you used, merely formatting the returned
values
to get it into the form you said you wanted:

You wrote:

whereas the correct answer is 14/02/12.

The formula I gave you -- really just your formula displayed differently,
applied to your data returns 14/02/12. You must be doing something
different
if you are getting 40953 as a result.

Seems like you need to be more clear as to what you want.

After all, if 14/02/12 is the correct answer, and the formula returns
14/02/12,
it's difficult to know what the problem is.
--ron

Yes. So it's interesting as to how the OP has converted the text output of
Ron's formula and interpreted it as a date?

=--(TEXT(DATEDIF(C6,D6,"md"),"00")&"/"&TEXT(DATEDIF(C6,D6,"ym"),"00")&"/"&TEXT(DATEDIF(C6,D6,"y"),"00"))
would do it, as would
=VALUE(TEXT(DATEDIF(C6,D6,"md"),"00")&"/"&TEXT(DATEDIF(C6,D6,"ym"),"00")&"/"&TEXT(DATEDIF(C6,D6,"y"),"00"))
but
=TEXT(DATEDIF(C6,D6,"md"),"00")&"/"&TEXT(DATEDIF(C6,D6,"ym"),"00")&"/"&TEXT(DATEDIF(C6,D6,"y"),"00")
should give a text output.
--
David Biddulph

"Peo Sjoblom" wrote in message
...
As it happens 40953 equals the serial date number for Feb 14 2012 which
happens to be
equal to the formula result as a date and not a display of 14 days 2
months and 12 years

"Ron Rosenfeld" wrote in message
...
On Mon, 11 Aug 2008 09:02:01 -0700, Geo
wrote:

Thanks ron, but the same problem exists. The total working days would
actually be 4458, but by your formula I get a total no. of 40953 which is
not
suitable for further calculations.
--
Geo

I guess I'm not sure what your problem is.

The formula I used is the one you used, merely formatting the returned
values
to get it into the form you said you wanted:

You wrote:

whereas the correct answer is 14/02/12.

The formula I gave you -- really just your formula displayed differently,
applied to your data returns 14/02/12. You must be doing something
different
if you are getting 40953 as a result.

Seems like you need to be more clear as to what you want.

After all, if 14/02/12 is the correct answer, and the formula returns
14/02/12,
it's difficult to know what the problem is.
--ron

On Mon, 11 Aug 2008 09:43:23 -0700, "Peo Sjoblom" wrote:

As it happens 40953 equals the serial date number for Feb 14 2012 which
happens to be
equal to the formula result as a date and not a display of 14 days 2 months
and 12 years

I wish I knew what the OP wants.

He posted that 14/02/12 is the correct answer, which is what the formula gives.

14 yrs, 2 months, 12 days is 4,550 days *IF* you assume that a year has 365
days and a month 30 days, which we all know is not a good assumption if you
need to be accurate.

If he wants the number of workdays, which he sort of hinted at, he's going to
need to supply a holiday list, and also information as to what constitutes a
workday (in this business).

Hopefully, he will clarify things soon.
--ron