Question on using time in Excel

C

C Tate

I am trying to work out some times in Excel. If the figure in, say cell A8,
is over 8 hours I want a formula which tells me by how much I'm over. If it's
under 8 hours I want the formula to tell me by how much I am under. Any help
would be appreciated.
PS: Usually I am just over under or over by several minutes. However my
formula must be able to accommodate the fact that do sometimes go an hour or
more over.
 
N

Niek Otten

=A8-(8/24)

Format as time

Make sure cell A8 is entered as real Excel time, not just a number

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
C

C Tate

Hmm ... this seems to work if the number is over 8 hours but not if it's
under. Am I doing something wrong?
 
N

Niek Otten

You're right. Use Bob's formula instead

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
C

C Tate

Bob's formula is great. The only disadvantage is that I want to be able to
sum all these minutes over and under 8 hours and the way it is set up won't
allow me to do that!
 
N

Niek Otten

The only way you can work with negative times in Excel is to use the 1904
date system (Tools>Options, Calculation tab).
That will change any dates you may already have in your workbook.
But you can use my formula then.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
B

Bob Phillips

That is not absolutely correct Niek, although a negative time is not
displayable, the value is still valid, and when included in a sum, it works
fine.

To the OP,

I would simply do the addition in a single formula

SUMPRODUCT(--(A2:A20>--"08:00:00"),(A2:A20-TIME(8,0,0)))

gives the amount of time where the cells are above 8 hours, and

=SUMPRODUCT(--(A2:A20<--"08:00:00"),--(A2:A20<>""),(ABS(A2:A20-TIME(8,0,0)))
)

gives the amount of time where the cells are below 8 hours.
 
N

Niek Otten

Correct again, Bob. This doesn't seem to be my best thread!

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
Top