Adding Minutes & Seconds expressing time differnce if total is over an hour

  • Thread starter Thread starter Roxx
  • Start date Start date
R

Roxx

I am working on a document in which I can do the following:

1)format a column in minutes and seconds ie. 2:30 is two
minutes 30 seconds - (Format - Cells - Time - 13:30:55)

2)total the time of the column using SUM

3) Ideally every time my column will add up to one hour,
but when it doesn't I want to be able to do the following:
if the total is over one hour I want the word "OVER" and
the minutes:seconds I'm over one hour
and when the total is short I want the word "UNDER" and
the number of minutes:seconds I'm short. It's okay (&
actually better) if the "OVER" or "UNDER" appear in the
column deside the time.

Any ideas out there.

With thanks
Roxx
 
See if this works for you:

=IF(SUM(A1:A3)>1/24,"OVER ",IF(SUM(A1:A3)
<1/24,"UNDER ",""))&TEXT(ABS(1/24-SUM(A1:A3)),"[mm]:ss")

HTH
Jason
Atlanta, GA
 
Works beautifully
Thanks Jason in Atlanta!
-----Original Message-----
See if this works for you:

=IF(SUM(A1:A3)>1/24,"OVER ",IF(SUM(A1:A3)
<1/24,"UNDER ",""))&TEXT(ABS(1/24-SUM(A1:A3)),"[mm]:ss")

HTH
Jason
Atlanta, GA
-----Original Message-----
I am working on a document in which I can do the following:

1)format a column in minutes and seconds ie. 2:30 is two
minutes 30 seconds - (Format - Cells - Time - 13:30:55)

2)total the time of the column using SUM

3) Ideally every time my column will add up to one hour,
but when it doesn't I want to be able to do the following:
if the total is over one hour I want the word "OVER" and
the minutes:seconds I'm over one hour
and when the total is short I want the word "UNDER" and
the number of minutes:seconds I'm short. It's okay (&
actually better) if the "OVER" or "UNDER" appear in the
column deside the time.

Any ideas out there.

With thanks
Roxx





.
Click to expand...
.
Click to expand...
 
Back
Top