Hours to minutes jcs
 

I'm calculating the following:

Start time in hh:mm (F3)
End time in hh:mm (F4)
Total time (F5) this cell is been custom formatted with [m] to convert
to minutes and then calculate penalties.

If F5 is under 175 (minutes) I need to find the difference and
multiply x 2
If over 175 I need to find the difference
If 175 there are no penalties.

I'm using the following formula
=IF(H3=0,0,IF(H3<175,((175-H3)*2),IF(H3175,(H3-175),IF(H3=175,0))))

Why do I get 349.6458333 for a value of 255 in F5

Thanks

Hours to minutes jcs
 

G'day

I think it has to do with the actual time/date value that excel assigns it.

I came up with a work around that you can use, unless you get a better
resolve from one of the other contributors.

Your code, slightly modified:
=IF(G5=0,0,IF(G5<175,((175-G5)*2),IF(G5175,(G5-175),0)))

I added the following in the next cell down
F6 =VALUE(F5)*1440

This works both ways.

assuming
example.1
start time: 08.00
finish time: 18:00 = total: 10.00hrs
which = 600mins - 175 = 425

example.2
start time: 08.00
finish time: 10:00 = total: 2.00hrs
which = 175 - 120mins = 55 x 2 = 110


HTH
Mark.

Hours to minutes jcs
 

In the cell that returns the minutes formatted as [m] use this

=(end-start)*24*60

and format as general or number


Then do your IF formula

otherwise you need to convert the 175 to time which would be H3<175/24/60



one hour
"nlp239" wrote in message
...
I'm calculating the following:

Start time in hh:mm (F3)
End time in hh:mm (F4)
Total time (F5) this cell is been custom formatted with [m] to convert
to minutes and then calculate penalties.

If F5 is under 175 (minutes) I need to find the difference and
multiply x 2
If over 175 I need to find the difference
If 175 there are no penalties.

I'm using the following formula
=IF(H3=0,0,IF(H3<175,((175-H3)*2),IF(H3175,(H3-175),IF(H3=175,0))))

Why do I get 349.6458333 for a value of 255 in F5

Thanks

Hours to minutes jcs
 

So the question is what's going on? 175 is the number of minutes that you
see but Excel is storing this as a percentage of the day 175/(60*24) or
175/1440 which is something like 0.121527777777778 even though you don't see
it. Therefore when you write your formula you would need to test with the
decimal number not whole number. Rather than do that its easier to convert
the decimal to actual minutes by multiplying by 1440.

Hope this helps.


--
Thanks,
Shane Devenshire


"nlp239" wrote:

I'm calculating the following:

Start time in hh:mm (F3)
End time in hh:mm (F4)
Total time (F5) this cell is been custom formatted with [m] to convert
to minutes and then calculate penalties.

If F5 is under 175 (minutes) I need to find the difference and
multiply x 2
If over 175 I need to find the difference
If 175 there are no penalties.

I'm using the following formula
=IF(H3=0,0,IF(H3<175,((175-H3)*2),IF(H3175,(H3-175),IF(H3=175,0))))

Why do I get 349.6458333 for a value of 255 in F5

Thanks

Hours to minutes jcs
 

On Oct 21, 12:24*am, ShaneDevenshire
wrote:
So the question is what's going on? *175 is the number of minutes that you
see but Excel is storing this as a percentage of the day 175/(60*24) or
175/1440 which is something like 0.121527777777778 even though you don't see
it. *Therefore when you write your formula you would need to test with the
decimal number not whole number. *Rather than do that its easier to convert
the decimal to actual minutes by multiplying by 1440.

Hope this helps.

--
Thanks,
Shane Devenshire



"nlp239" wrote:
I'm calculating the following:

Start time in hh:mm (F3)
End time in hh:mm (F4)
Total time (F5) this cell is been custom formatted with [m] to convert
to minutes and then calculate penalties.

If F5 is under 175 (minutes) I need to find the difference and
multiply x 2
If over 175 I need to find the difference
If 175 there are no penalties.

I'm using the following formula
=IF(H3=0,0,IF(H3<175,((175-H3)*2),IF(H3175,(H3-175),IF(H3=175,0))))

Why do I get 349.6458333 for a value of 255 in F5

Thanks- Hide quoted text -

- Show quoted text -

Thanks EVERYONE. I tried individual suggestions but couldn't get it to
work - probably because of my own inexperience.
Re-read the explanations and combined your suggestions and it works
great.

Here's what I came up with:

Cell H3 the following: =SUM(G3-F3)*24*60
Cell I3 the following: =IF(H3=0,0,IF(H3<175,(((175-H3)*2)/
1440),IF(H3175,((H3-175)/1440),IF(H3=175,0))))

Thanks again YOUR time and suggestions have helped me a great deal.

Hours to minutes jcs
 

A couple of simplifications for your formulae:

In =SUM(G3-F3)*24*60 your SUM function is doing nothing.
Try =(G3-F3)*24*60

In
=IF(H3=0,0,IF(H3<175,(((175-H3)*2)/1440),IF(H3175,((H3-175)/1440),IF(H3=175,0))))
you've got quite a few surplus parentheses
Try
=IF(H3=0,0,IF(H3<175,(175-H3)*2/1440,IF(H3175,(H3-175)/1440,IF(H3=175,0))))
--
David Biddulph

"nlp239" wrote:

Thanks EVERYONE. I tried individual suggestions but couldn't get it to
work - probably because of my own inexperience.
Re-read the explanations and combined your suggestions and it works
great.

Here's what I came up with:

Cell H3 the following: =SUM(G3-F3)*24*60
Cell I3 the following: =IF(H3=0,0,IF(H3<175,(((175-H3)*2)/
1440),IF(H3175,((H3-175)/1440),IF(H3=175,0))))

Thanks again YOUR time and suggestions have helped me a great deal.

On Oct 21, 12:24 am, ShaneDevenshire
wrote:
So the question is what's going on? 175 is the number of minutes that you
see but Excel is storing this as a percentage of the day 175/(60*24) or
175/1440 which is something like 0.121527777777778 even though you don't see
it. Therefore when you write your formula you would need to test with the
decimal number not whole number. Rather than do that its easier to convert
the decimal to actual minutes by multiplying by 1440.

Hope this helps.

--
Thanks,
Shane Devenshire



"nlp239" wrote:
I'm calculating the following:

Start time in hh:mm (F3)
End time in hh:mm (F4)
Total time (F5) this cell is been custom formatted with [m] to convert
to minutes and then calculate penalties.

If F5 is under 175 (minutes) I need to find the difference and
multiply x 2
If over 175 I need to find the difference
If 175 there are no penalties.

I'm using the following formula
=IF(H3=0,0,IF(H3<175,((175-H3)*2),IF(H3175,(H3-175),IF(H3=175,0))))

Why do I get 349.6458333 for a value of 255 in F5

Thanks- Hide quoted text -

- Show quoted text -