Excel 2007 : Calculating and Rounding Time to Nearest 15 min Interval

**SherylH** · 01-25-2010, 05:10 PM

After finally getting my spreadsheet to do exactly what I want it to do, the payroll department made a change...grrr.

Now the goal is to automatically round the time to the nearest 15 minute increment within the same cell. I also want to total these times at the end of the row to show hours and minutes instead of converted to decimals. I already have code to enter a time in one cell and it automatically insert the colon without having to type it. Another drawback, we have some shifts that work past midnight. This is overcome by entering the date and the time, and I don't have any problems with that...yet.

My current VBA:

Please Login or Register  to view this content.

And the current formula for calculating the row of times:

=+((C5-B5+E5-D5+G5-F5+I5-H5)*1)*24

Example of what I would enter on the spreadsheet:

B5=0550 C5=1536 D5=1605 E5=1805 J5=11.77 (Total converted to decimal)

What I want each cell to look like:

B5=05:45 C5=15:30 D5=16:00 E5=18:00 J5=12.30

Sorry if this is confusing, but I have racked my brain for the past week. Let me know if you'd like to see the actual spreadsheet.

Thanks

**NBVC** · 01-25-2010, 05:14 PM

Welcome to the forum,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**JBeaucaire** · 01-25-2010, 06:11 PM

Would this be easier with an uploaded sample excerpt from your real workbook? It would be easier for you to show us there, wouldn't it?

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**NBVC** · 01-25-2010, 10:34 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.thecodecage.com/forumz/me...-interval.html