Jump to content


Photo

Time Calculations

Time ROUND

  • Please log in to reply
1 reply to this topic

#1 Zella

Zella

    Newbie

  • Members
  • Pip
  • 7 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?

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 797 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:

 

Attached File  Test Timesheet w Formula Errors-2.xlsx   18.38KB   9 downloads

timesheet3.png


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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users