Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting total minutes into hours and minutes in Excel | Excel Worksheet Functions | |||
converting Days Hours & minutes into just minutes in excel | Excel Discussion (Misc queries) | |||
how to change a decimal number (minutes) into hours and minutes? | Excel Discussion (Misc queries) | |||
add hours & minutes to other hours & minutes to receive total hou. | Excel Worksheet Functions | |||
add column of minutes, show total in hours & minutes | Excel Worksheet Functions |