Calculate difference in dates

[Geo]

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.....

 

[Ron Rosenfeld]

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

 

[Geo]

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.

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[Ron Rosenfeld]

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.

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:

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

 

[Peo Sjoblom]

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

 

[David Biddulph]

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.

 

[Ron Rosenfeld]

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

 

[Geo]

Thanks for the effort, let me try to clarify. Say, I would like to calculate
the final settlement of a staff and the details are as follows. Also, for
calculating the settlement I think there is no need for the list of holidays.

A B C
D E
1 Joining Date Resigned Date Total Days worked Basic Salary
Settlement Amnt.
2 dd/mm/yy dd/mm/yy dd/mm/yy 1000.00
=(c2*(365/30))*(d2/30)

 

[Ron Rosenfeld]

Thanks for the effort, let me try to clarify. Say, I would like to calculate
the final settlement of a staff and the details are as follows. Also, for
calculating the settlement I think there is no need for the list of holidays.

A B C
D E
1 Joining Date Resigned Date Total Days worked Basic Salary
Settlement Amnt.
2 dd/mm/yy dd/mm/yy dd/mm/yy 1000.00
=(c2*(365/30))*(d2/30)

That is not sufficient detail to be able to advise you.

If you want the number of weekdays between Joining and Resigned, take a look at
the NETWORKDAYS function which counts M-F only.

If you want something else, you are really going to have to supply more
information.
--ron

 

[Geo]

I want the difference between the Resigned date and Joining date; if I use
the formula B1-A1, I get the correct answer in the General number format. But
if I use the date format it is showing one month in excess.
Eg:

A B C
28/05/1996 12/08/2008 =16/03/12 (B1-A1)

B1-A1 will give you 4459 as answer in General number format which is
correct, whereas date format will give you 16/03/12 as answer which is one
month excess.

Hope this clarifiaction is quite ok. please help....

 

[Ron Rosenfeld]

I want the difference between the Resigned date and Joining date; if I use
the formula B1-A1, I get the correct answer in the General number format. But
if I use the date format it is showing one month in excess.
Eg:

A B C
28/05/1996 12/08/2008 =16/03/12 (B1-A1)

B1-A1 will give you 4459 as answer in General number format which is
correct, whereas date format will give you 16/03/12 as answer which is one
month excess.

Hope this clarifiaction is quite ok. please help....

If all you want is a particular format, then the formula I supplied earlier,
with dates in C66

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

already gave you the result in the format you wanted.

You felt that was unsatisfactory, but you haven't responded to any of the
comments that were made to explain why it was unsatisfactory.

You indicated you got some large number when you tried to use it in a formula,
but you did not tell us how you got that number.

Using the above dates, it gives a result of 15/02/12

12 years
2 months
15 days

This is a text string, not a numeric value.

I hope you are aware that you cannot specify any exact time interval that
includes years and months, without further rules. Neither a "year" nor a
"month" are an exact interval. A year can be 365 or 366 days. A "month" can
be 28, 29, 30 or 31 days.

If you are computing something that depends on days in service, use the number
of days in you calculations and not this text string.
--ron