IF Statements - to include calculation?

  • Thread starter Thread starter MissPiggy
  • Start date Start date
M

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?
 
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?
Click to expand...
 
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
 
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.
 
Thank you!

Dave Peterson said:
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?
Click to expand...
Click to expand...
 
Simply put - thank you - it works!

JLatham said:
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 said:
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?
Click to expand...
Click to expand...
 
Back
Top