Access & Excel Notes

Wednesday August 22 2007

Rounding times to nearest quarter hour

Filed under: Access,Time/Date — ccnotes @ 10:39 am

This is based on the fact that 15 minutes is 1/96th of a day - 24*60/15.

http://exceltips.vitalnews.com/Pages/T0266_Rounding_Time.html

…to round to the nearest 15 minutes, take the time value, multiply it by 96, round it, and then divide it by 96.

For example, if the time value you wish to round is in cell E5, the following formula does the rounding very nicely:

=ROUND(E5*96,0)/96

Notice that this formula uses the ROUND worksheet function, which is intrinsic to Excel and doesn’t require an add-in.

 

To round a number or hours to the nearest quarter hour (not a date/time number)

Round((([dOTout]-[dOTin])*24)*4,0)/4

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: