# Time Calculations

Time ROUND

### #1 Zella

Zella

Member

• Members
• 12 posts

Posted 07 March 2017 - 11:54 AM

Reference the attached, Cells G23:G24 and K23:K24.  I can't figure out why the time isn't calculating to the quarter hour.  The other cells in these columns are doing what they should.  How can I correct this error, please?

### #2 Jonathan

Jonathan

• 862 posts

Posted 07 March 2017 - 12:54 PM

HI Zella,

My fault - I originally wrote the formula to round the result to 1 decimal place:

=IF(SUM(\$G\$18:\$G19)+MIN(\$S20,EightHours)>FortyHours,ROUND(FortyHours-SUM(\$G\$18:\$G19),1),MIN(\$S20,EightHours))

This works fine when working with whole hours, but not when working with fractions of hours. You could resolve this by changing the rounding to a higher number of decimal places (14 should be sufficient), but a better solution might be to remove the rounding entirely, since it was only intended to act as a safety measure and shouldn't really be needed:

=IF(SUM(\$G\$18:\$G19)+MIN(\$S20,EightHours)>FortyHours,FortyHours-SUM(\$G\$18:\$G19),MIN(\$S20,EightHours))

You can see an explanation of the ROUND function in the video lesson: Essential Skills Lesson 4-3 Format numbers using built-in number formats.

I have attached a copy of your workbook with the altered formula:

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

### Also tagged with one or more of these keywords: Time, ROUND

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users