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.
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)
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!
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.
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).
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?
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 @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:55 AM