Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements - to include calculation?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements - to include calculation?
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)) MissPiggy wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements - to include calculation?
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 "MissPiggy" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements - to include calculation?
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" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements - to include calculation?
Thank you!
"Dave Peterson" wrote: 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)) MissPiggy wrote: 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements - to include calculation?
Simply put - thank you - it works!
"JLatham" wrote: 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" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn off or not include lines in a calculation | Excel Worksheet Functions | |||
Nested If statements returning a sum calculation | Excel Worksheet Functions | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
Time Calculation For A Timesheet To Include Lunch | Excel Worksheet Functions | |||
Averages that include cells with IF statements | Excel Worksheet Functions |