Calculation of Period

[Zaigham]

Hi,

I have to calculate the total period of different spells as shown below:

Excel Workbook
	A	B	C	D	E
1	From	To	Year	Month	Days
2	01.05.1984	18.07.1985	1	14	18
3	03.06.1989	27.06.1989	0	0	25
4	15.02.1991	05.03.1991	0	0	19
5	Total Period ==>>	*	1	14	62
6	Result Required	*	2	4	2
Sheet1

Thanks in advance for any help.

Regards
Zaigham

 

[drsarao]

Maybe this?
(Not tried all the bounds)

Excel Workbook
	A	B	C	D	E
1	From	To	Year	Month	Days
2	01.05.1984	18.07.1985	1	14	18
3	03.06.1989	27.06.1989	0	0	25
4	15.02.1991	05.03.1991	0	0	19
5	Total Period ==>>	*	1	14	62
6	Result Required	*	2	4	2
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C6	=C5+INT(D5/12)
D6	=MOD(D5,12)+INT(E5/30)
E6	=MOD(E5,30)

 

[barry houdini]

Why is the result 2 years, that would be one surely?

....also wouldn't you want D2 to be 2 not 14? I suggest you use this formula in D2 copied down

=DATEDIF(A2,B2,"YM")

For the whole period the answer would necessarily be an approximation because you aren't dealing with "real" months - try this version for C5

=DATEDIF(SUM(A2:A4),SUM(B2:B4),"y")

and similarly for the months and days in D5 and E5

=DATEDIF(SUM(A2:A4),SUM(B2:B4),"ym")

and

=DATEDIF(SUM(A2:A4),SUM(B2:B4),"md")

That gives a result of 1 year, 4 months, 0 days in total

 

[barry houdini]

Note that if you have many more date ranges then probably better to use this version

=DATEDIF(0,SUM(B2:B4)-SUM(A2:A4)+1,"y")

 

[Zaigham]

ADVERTISEMENT

Hi barry houdini

Thanks for your compliments. I will try to explain that Why is the result 2 years, that would be one surely and why I want D2 to be 2 not 14?
Well, 62 days are equal to 2 months and 2 days, 14 months are equal to 1 year 2 months. As I already told that I want to calculate the total period of different spells. So 60 days from "days" are equal to 2 months which added to months i.e. 14+2=16 months that means 1 year and 4 months so total period comes to 2 years 4 months and 2 days. Hope I have explained correctly.

However, drsarao's solution is exactly which I needed. Thank a lot. I have another related question, how can I subtract the above period i.e., 1 year 4 months and 2 days from a period of 18 years and 25 days?
Regards
Zaigham

 

[barry houdini]

......so total period comes to 2 years 4 months and 2 days......

No, I still don't see how you come up with that figure - even including both start and end dates the first period is 444 days, the second one is 25 days and the third 19....so grand total of 488 days - that isn't more than 2 years by any method I know

For the first period you are double counting, the 1 year in C2 is also being counted within the 14 months in D2, hence my suggestion to change the formula in D2 then you would only get the months left after whole years have been taken out

 

[Zaigham]

ADVERTISEMENT

Hi barry houdini

You are absolutely right, I have corrected the formula and now the result is 1 year 4 months and 2 days.
Will you please suggest me about my 2nd related question that how can I subtract the above result from 18 years and 25 days. I have to calculate the period in "year, month and days" format and not in whole year.

Regards
Zaigham

 

[shg]

Barry's the expert on date formulas (well, formulas in general), but from his suggestion, maybe

=DATEDIF(SUM(B2:B4) - SUM(A2:A4) + 1, --"1/25/1918", "y")
=DATEDIF(SUM(B2:B4) - SUM(A2:A4) + 1, --"1/25/1918", "ym")
=DATEDIF(SUM(B2:B4) - SUM(A2:A4) + 1, --"1/25/1918", "md")

 

[Zaigham]

Hi shg

Thanks for your suggestion but result is "#VALUE!". I think it is my fault that I haven't clearly told that I want to subtract the result i.e, 1 year 4 months and 2 days from a total period of 18 years 0 month and 25 days. I didn't mean it by 25th day of January 1918.

Y M D
18 0 25
1 4 2
16 8 23

So what will be the formula now?

Regards
Zaigham

 

[drsarao]

Try this:
(assuming 30 days month)

Excel Workbook
	A	B	C
1	Y	M	D
2	18	0	25
3	1	4	2
4	16	8	23
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A4	=IF(A2>A3,A2-A3,A2+12-A3)-(B3>B2)
B4	=IF(B2>B3,B2-B3,B2+12-B3)-(C3>C2)
C4	=IF(C2>C3,C2-C3,C2+30-C3)