Ask Experts Questions for FREE Help !
Ask
    chris1962's Avatar
    chris1962 Posts: 3, Reputation: 1
    New Member
    		  
    #1

    Feb 2, 2009, 04:00 PM
    How do you stop this time calculation formula from rounding
    I did not make this formula, but found it on the internet. I am trying to figure out how to make it stop rounding. It is the "total" cell for the "time in" and "time out" on a timesheet. The cell is formatted as "time". I want it to return exact minutes worked and not round. Can someone please help?

    =IF((OR(K8="",K7="")),0,IF((K8
    chris1962's Avatar
    chris1962 Posts: 3, Reputation: 1
    New Member
    		  
    #2

    Feb 2, 2009, 04:02 PM

    Sorry, prior questions did not post all of the formula.

    Chris

    =IF((OR(K8="",K7="")),0,IF((K8<K7),((K8-K7)*24)+24,(K8-K7)*24))
    LinfieldPA's Avatar
    LinfieldPA Posts: 32, Reputation: 3
    Junior Member
    		  
    #3

    Feb 2, 2009, 04:43 PM

    I guess you're trying to calculate wages from someone's TIME IN and TIME OUT? If so, see if the attached makes sense to you. It will calculate number of minutes and calculate wages with a per minute rate. I can expand/explain further if it doesn't fit into your scenario. (Not sure what the "24"'s were for in the formula you found.)

    Let me know if it's what you're looking for...

    Brian
    Attached Files
  1. File Type: xls TIME Calc.xls (13.5 KB, 221 views)
    2. ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #4

    Feb 2, 2009, 04:45 PM

    If you format a cell as time format then it should display in hh:mm format.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #5

    Feb 2, 2009, 06:08 PM

    Properly formatted as time as Scott suggests gives you the ability to directly add and subtract the cells. Format ALL the requisite cells as hh:mm including the cell that is getting this formula, then you can simply add/subtract:

    =IF(OR(K8="",K7=""),0,IF(K8<K7,K7-K8,K8-K7))
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #6

    Feb 2, 2009, 06:30 PM
    Although, now that I look at what your formula is doing, you do need to take into account the midnight issue. I've fixed that too in the RED section.

    =IF(OR(K8="",K7=""),0,K8-K7+(K7>K8))

    This simple formula assumes all times are represented in 24hr format.

    4:00 - 10:00 = 18hrs with that formula since 4:00 is 4am.
    16:00 - 10:00 = 6hrs as 16 is 4pm
    22:30 - 8:00 = 8:30

    The underlined trick at the end is a simple true/false and will add 24 if true without needing another IF statement.
    chris1962's Avatar
    chris1962 Posts: 3, Reputation: 1
    New Member
    		  
    #7

    Feb 11, 2009, 12:32 PM

    I just wanted to thank all you guys for the help. Both formulas worked. I usually only do simple formulas in Excel and never time formulas, you have saved me a lot of time and frustration!
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #8

    Feb 11, 2009, 04:05 PM

    Always glad to help. Keep us on speed dial.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Car Speed calculation at the time of accident [ 10 Answers ]

Vehicle No. 1 (going from North to South) hit Vehicle No. 2 (going from East to West) at roads intersection. After the collision Vehicle 1 dragged Vehicle 2 around 70 m in the direction from North to South. Weight of Vehicle No. 1 is 2.5 Tons and of Vehicle No. 2 is 2.2 Tons. Given the coefficient...

Time Calculation [ 1 Answers ]

How do you get Access to add time when the time can span over two or meore days. I have a start date field, an End Date field as well as a start and end time field. For instance if I start on 01/01/2009 at 0600 and end on 01/02/2009 at 1800 I need access to tell me the sum is 32 hours... Any way...

Time calculation [ 2 Answers ]

Two trains are running at the same time facing each other between Distances of 100 kms. One train is traveling at 10kms per hour and another At 8kms per hour. Within how much time (rounded to Hrs and Minutes) both Trains will meet. A. 5 Hrs 34 Minutes B. 6 Hrs 4 Minutes C....

Stop Excel from Rounding [ 1 Answers ]

I have created a Timesheet spreadsheet, in Excel, that calulates the amount of time between to manually entered times. This is the formula I am using =IF((OR(D8="";B8=""));0;IF((D8<B8);((D8-B8)*24)+24;(D8-B8)*24)) The formula works very well and does return the corrent amount of elapsed...

Internal Rate of Return Calculation and Formula [ 6 Answers ]

If I have the following cash flows, what is the internal rate of return (IRR)? 12/31/05 invest 1052 1/20/06 return 100 12/1/06 return 25 12/31/06 return 1136 What is the formula used to calculate this IRR?

View more questions Search