Question on using time in Excel

[Guest]

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.

 

[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

 

[Bob Phillips]

=TEXT(ABS(TIME(8,0,0)-A20),"hh:mm")&(IF(A20>TIME(8,0,0)," over"," short"))

 

[Guest]

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

 

[Niek Otten]

You're right. Use Bob's formula instead

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[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!

 

[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

 

[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.

 

[Niek Otten]

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

--
Kind regards,

Niek Otten

Microsoft MVP - Excel