Calculation of Period

Zaigham

Board Regular
Joined
Dec 22, 2010
Messages
144
Hi,

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


Excel Workbook
ABCDE
1FromToYearMonthDays
201.05.198418.07.198511418
303.06.198927.06.19890025
415.02.199105.03.19910019
5Total Period ==>>*11462
6Result Required*242
Sheet1


Thanks in advance for any help.

Regards
Zaigham
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Maybe this?
(Not tried all the bounds)
Excel Workbook
ABCDE
1FromToYearMonthDays
201.05.198418.07.198511418
303.06.198927.06.19890025
415.02.199105.03.19910019
5Total Period ==>>*11462
6Result Required*242
Sheet1
Excel 2003
Cell Formulas
RangeFormula
C6=C5+INT(D5/12)
D6=MOD(D5,12)+INT(E5/30)
E6=MOD(E5,30)
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Dec 22, 2010
Messages
144

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? :eek:
Regards
Zaigham
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
......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
 
Last edited:

Zaigham

Board Regular
Joined
Dec 22, 2010
Messages
144

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 22, 2010
Messages
144
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

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Try this:
(assuming 30 days month)

Excel Workbook
ABC
1YMD
218025
3142
416823
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,569
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top