IF Statements - to include calculation?

[MissPiggy]

I am trying to apply a function that works out the time worked, and then if
below 6hrs display as time, or if over six hours - deduct 30 mins before
displaying the time.

The feeder cells have been formated to time and it all seems to work - apart
from deducting the 30 mins. Here is what I have used (well one of many
attempts!)

=IF(O3-N3<=6,O3-N3,O3-N3-$U$1) [where U1 is a time cell containing 0:30:00]

Any ideas?

 

[Dave Peterson]

6 hours is 6/24 of a day. The whole number 6 would represent 6 days.

So try:
=IF(O3-N3<=6/24,O3-N3,O3-N3-$U$1)
or
=IF(O3-N3<=.25,O3-N3,O3-N3-$U$1)
or
=IF(O3-N3<=time(0,6,0),O3-N3,O3-N3-$U$1)

or even
=IF(O3-N3<=.25,O3-N3,O3-N3-time(0,30,0))

I am trying to apply a function that works out the time worked, and then if
below 6hrs display as time, or if over six hours - deduct 30 mins before
displaying the time.

The feeder cells have been formated to time and it all seems to work - apart
from deducting the 30 mins. Here is what I have used (well one of many
attempts!)

=IF(O3-N3<=6,O3-N3,O3-N3-$U$1) [where U1 is a time cell containing 0:30:00]

Any ideas?

 

[Kassie]

You are not using a time format in your formula. To achieve this, use a cell
to hold the 6 hour limitation. I used U2, and converted your formula to read
=IF(O3-N3<=$U$2,O3-N3,(O3-N3)-$U$1)
Works like a bomb!

--
HTH

Kassie

Replace xxx with hotmail

 

[JLatham]

What you see is not what you have. At least when dealing with time in Excel.
Excel tracks time as days and parts of days. 6 hours is not 6, but instead
it is .25 of a day. So change your IF to O3-N3<=.25, and see where that
takes you.

 

[MissPiggy]

Thank you!

6 hours is 6/24 of a day. The whole number 6 would represent 6 days.

So try:
=IF(O3-N3<=6/24,O3-N3,O3-N3-$U$1)
or
=IF(O3-N3<=.25,O3-N3,O3-N3-$U$1)
or
=IF(O3-N3<=time(0,6,0),O3-N3,O3-N3-$U$1)

or even
=IF(O3-N3<=.25,O3-N3,O3-N3-time(0,30,0))

I am trying to apply a function that works out the time worked, and then if
below 6hrs display as time, or if over six hours - deduct 30 mins before
displaying the time.

The feeder cells have been formated to time and it all seems to work - apart
from deducting the 30 mins. Here is what I have used (well one of many
attempts!)

=IF(O3-N3<=6,O3-N3,O3-N3-$U$1) [where U1 is a time cell containing 0:30:00]

Any ideas?

 

[MissPiggy]

Simply put - thank you - it works!

What you see is not what you have. At least when dealing with time in Excel.
Excel tracks time as days and parts of days. 6 hours is not 6, but instead
it is .25 of a day. So change your IF to O3-N3<=.25, and see where that
takes you.

I am trying to apply a function that works out the time worked, and then if
below 6hrs display as time, or if over six hours - deduct 30 mins before
displaying the time.

The feeder cells have been formated to time and it all seems to work - apart
from deducting the 30 mins. Here is what I have used (well one of many
attempts!)

=IF(O3-N3<=6,O3-N3,O3-N3-$U$1) [where U1 is a time cell containing 0:30:00]

Any ideas?