Question on using time in Excel
 

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.

=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 Tate" wrote in message
...
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.

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

--
HTH

Bob Phillips

"C Tate" wrote in message
...
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.

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" wrote:

=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 Tate" wrote in message
...
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.

You're right. Use Bob's formula instead

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"C Tate" wrote in message
...
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" wrote:

=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 Tate" wrote in message
...
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.

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" wrote in message
...
You're right. Use Bob's formula instead

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"C Tate" wrote in message
...
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" wrote:

=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 Tate" wrote in message
...
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.

The only way you can work with negative times in Excel is to use the 1904
date system (ToolsOptions, 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

"C Tate" wrote in message
...
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" wrote in message
...
You're right. Use Bob's formula instead

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"C Tate" wrote in message
...
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" wrote:

=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 Tate" wrote in message
...
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.

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.

--
HTH

Bob Phillips

"Niek Otten" wrote in message
...
The only way you can work with negative times in Excel is to use the 1904
date system (ToolsOptions, 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

"C Tate" wrote in message
...
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!

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

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Bob Phillips" wrote in message
...
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.

--
HTH

Bob Phillips

"Niek Otten" wrote in message
...
The only way you can work with negative times in Excel is to use the 1904
date system (ToolsOptions, 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

"C Tate" wrote in message
...
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!