How to add up separate periods of time in Excel

  • Thread starter Thread starter FatKat
  • Start date Start date
F

FatKat

Greetings. I am a NYC criminal attorney who uses Excel for a lot of
my case-management. I've recently found that formula that allows you
to calculate the time between two dates

("=YEAR(I45)-YEAR(R45)-
IF(OR(MONTH(I45)<MONTH(R45),AND(MONTH(I45)=MONTH(R45),
DAY(I45)<DAY(R45))),1,0)&" years, "&MONTH(I45)-
MONTH(R45)+IF(AND(MONTH(I45)<=MONTH(R45),DAY(I45)<DAY(R45)),
11,IF(AND(MONTH(I45)<MONTH(R45),DAY(I45)>=DAY(R45)),
12,IF(AND(MONTH(I45)>MONTH(R45),DAY(I45)<DAY(R45)),-1)))&" months,
"&I45-DATE(YEAR(I45),MONTH(I45)-IF(DAY(I45)<DAY(R45),1,0),DAY(R45))&"
days"")

broken down in years, months, and days. I use that information to
calculate the periods of time in which a criminal defendant is held in
custody.

FYI - this is important because under my state's sentencing laws, a
person charged with committing a felony is subject to mandatory
minimum jail sentences of at least a year if they have been previously
convicted of a felony in the past 10 years - the critical part is that
any subsequent periods of time spent by that person in custody (even
in local lock-ups for misdemeanors) is excluded from calculations of
the 10 year period. This happens quite frequently as you can
imagine. I use the above formula to calculate the length of each
period, but haven't figured out a way to add the separate periods
themselves.

I know I'm missing something simple.

Suggestions warmly appreciated.
 
That looks extremely complicated given that dates and times are just
numbers, so if you have
date and hours in A1 and date an hours in B1 and you want to know how long
time it is between those values

=DATEDIF(A1,B1,"y")

will give you the years


=DATEDIF(A1,B1,"ym")


the months after the years have been stripped


=DATEDIF(A1,B1,"md")


the days after the months have been stripped

=INT(B1-A1)


will give the total in days


=MOD(B1-A1,1)


will give you the hours, minutes and seconds after the days have been
stripped off



and if you want to add separate times you just add them


assume you have a client that has been in custody 3 separate times and you
want to add

B1-A1
B2-A2
B3-A3


=SUM(B1:B3)-SUM(A1:A3)


formatted as general will give you a decimal number that you can get the
total days from using

=INT(SUM(B1:B3)-SUM(A1:A3))

formatted as general


then the hours



=MOD(SUM(B1:B3)-SUM(A1:A3),1)


formatted as hh:mm


--


Regards,


Peo Sjoblom
 
You might be able to use the DATEDIF function to simplify your
formula. The description of this formula is only included in XL Help
for XL2000, for some reason, but Chip Pearson gives some examples of
how you can use it here:

http://www.cpearson.com/excel/datedif.aspx

Your formula, and one based on DATEDIF, will return a text string, so
you will not be able to add the various components together directly -
you will need to split out the numbers which represent years, months
and days into separate columns, using string functions like LEFT, MID
and RIGHT. You could then add these numbers directly, taking account
of the number of days in a month (which is always a thorny subject),
and possibly re-combine them into a similar format.

Alternatively, you could work in days and subtract start date from end
date for each period, then add all the days up and convert this into
year/month/day format (again, you will need to define what you mean by
a "month"). Although this will mean changing the way you have done
things so far, I think it will be easier in the long-term.

Hope this helps.

Pete
 
Greetings. I am a NYC criminal attorney who uses Excel for a lot of
my case-management. I've recently found that formula that allows you
to calculate the time between two dates

("=YEAR(I45)-YEAR(R45)-
IF(OR(MONTH(I45)<MONTH(R45),AND(MONTH(I45)=MONTH(R45),
DAY(I45)<DAY(R45))),1,0)&" years, "&MONTH(I45)-
MONTH(R45)+IF(AND(MONTH(I45)<=MONTH(R45),DAY(I45)<DAY(R45)),
11,IF(AND(MONTH(I45)<MONTH(R45),DAY(I45)>=DAY(R45)),
12,IF(AND(MONTH(I45)>MONTH(R45),DAY(I45)<DAY(R45)),-1)))&" months,
"&I45-DATE(YEAR(I45),MONTH(I45)-IF(DAY(I45)<DAY(R45),1,0),DAY(R45))&"
days"")

broken down in years, months, and days. I use that information to
calculate the periods of time in which a criminal defendant is held in
custody.

FYI - this is important because under my state's sentencing laws, a
person charged with committing a felony is subject to mandatory
minimum jail sentences of at least a year if they have been previously
convicted of a felony in the past 10 years - the critical part is that
any subsequent periods of time spent by that person in custody (even
in local lock-ups for misdemeanors) is excluded from calculations of
the 10 year period. This happens quite frequently as you can
imagine. I use the above formula to calculate the length of each
period, but haven't figured out a way to add the separate periods
themselves.

I know I'm missing something simple.

Suggestions warmly appreciated.
Click to expand...

I interpret your statement to mean that the 10 year "look back period" is
increased by the amount of time the person has been in custody?

So the effect might be that a person who was convicted of a felony 10 years and
1 month and 3 days ago, would still be subject to the mandatory 1 year sentence
had they been in prison, let us say, for 60 days five years ago?

It seems to me your first task is to deal with the imprecision of "years" and
"months". A year can be 365 or 366 days; a month can be 28, 29, 30 or 31 days.
How do the sentencing laws deal with this?

It's simple to get the number of days between two dates. Excel stores dates as
serial numbers (1= 1/1/1900) so you can determine the number of days between
dates by simple subtraction. You probably need to add 1 to the result
depending on whether both the day of admission AND the day of discharge are
counted as a day in prison.

But you need more rules to be able to convert the number of days served, into
years, months and days. If a person serves 28 days in Feb 2003, that would be
one month; but if the same person serves 28 days in July 2003, that would not
be a month?

So there is ambiguity in the "common" definition of years and months. Does
this ambiguity exist in the "legal" definition, and, if so, how do you want to
handle it?


--ron
 
Back
Top