Contextures News

Round Time to 1/4 Hour in Excel

December 6, 2016

Round time to the nearest 1/4 hour, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Rounding Time

To calculate the time spent on tasks, subtract the start time from the end time, then multiply by 24, to get the number of hours. For example: =(B2-A2)*24

If you're keeping track of your time in 15-minute blocks, use the MROUND function to round the time to the nearest quarter hour: =MROUND((B2-A2)*24,0.25)

See more Excel rounding functions on my Contextures website, and another example of MROUND on my blog.

round time to quarter hour

Week Grouping Formula - Solution

Last week, I showed a formula to get the Sunday start date for a week: =A2-WEEKDAY(A2+1,3)

Your challenge was to change the formula, so the week starts on Saturday. Here's my solution -- subtract 5 from the date: =A2-WEEKDAY(A2-5,3)

On my Date Functions page, you can download the sample file, where I've added a new sheet with a Week Start Day selector. Did you find a different solution? With Excel, there are usually several ways to solve something!

week start day formulas

Your Feedback

Organizing: Thanks for sending your tips on organizing Excel files! I'm working on an article with your ideas. The link will be in next week's newsletter.

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Excel Memory - Earlier this year, 32-bit Excel (2013/2016) was updated, to increase the available virtual memory. Charles Williams explains the change, and download his free tool to see the status on computer. (Level - All)

Excel Data - If you're working on your dashboard skills, you can get USA energy or economic data to practise with. Read about the free Excel add-in for EIA or FRED data, and download the file. (Level - All)

Excel Dashboards - To learn more about Excel dashboards, attend one of Mynda Treacy's free Excel Dashboard webinars this week. She also opened registration for her Excel dashboard course, and you can get a 20% early bird discount if you sign up by Thursday Dec 8th, at 8pm (Pacific time zone).

Also, see all my Excel products on my Contextures website, and my weekly collection of humorous Excel tweets. For example, "Actually getting somewhere with excel code writing, miracles do happen".

The Christmas Village

On the weekend, I set up the Christmas village, even though there weren't any little helpers visiting. My daughter and grandson were busy at their own house, redecorating their tree, after it fell over!

At this little house, would you be outside, sliding down the snowy hill, or inside, working with Excel?

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

ddalgleish @

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.




About Debra






Last updated: March 15, 2017 11:55 AM