Hours to minutes jcs

[nlp239]

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(H3>175,(H3-175),IF(H3=175,0))))

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

Thanks

 

[NoodNutt]

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(G5>175,(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.

 

[Peo Sjoblom]

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

 

[ShaneDevenshire]

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.

 

[nlp239]

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

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(H3>175,(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(H3>175,((H3-175)/1440),IF(H3=175,0))))

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

 

[David Biddulph]

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(H3>175,((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(H3>175,(H3-175)/1440,IF(H3=175,0))))
--
David Biddulph

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(H3>175,((H3-175)/1440),IF(H3=175,0))))

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

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

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(H3>175,(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 -