I cannot believe it’s almost July and we’re halfway through the year. And what a year it’s been so far. (The other day a friend of mine sent me a picture on WhatsApp stating “2020 - The year your wheelie bin goes out more than you.") But I digress.
Inspired by halves and because I am keeping tabs on the hours I work from home for one of my clients I was looking for an easy way to round my numbers down to the nearest half. Excel has three Functions you might find helpful: FLOOR, CEILING, and MROUND.
Here’s how (using the example below; click here to open the file in Excel):
- =FLOOR(C2,.5)
- =CEILING(C2,.5)
- =MROUND(C2,.5)
You can also write 0.5 rather than .5 but you all know by now that I try to be as efficient as possible. ;)
| A | B | C | D | E | F |
1 | start | finish | total | FLOOR | CEILING | MROUND |
2 | 7.45 | 12.30 | 4.85 | 4.5 | 5 | 5 |
3 | 8.00 | 14.00 | 6.00 | 6 | 6 | 6 |
4 | 8.13 | 13.15 | 5.02 | 5 | 5.5 | 5 |
5 | 12.00 | 14.15 | 2.15 | 2 | 2.5 | 2 |
6 | 9.00 | 15.40 | 6.40 | 6 | 6.5 | 6.5 |
| Total | 23.5 | 25.5 | 24.5 |
To make it look good, turn it into a table (CTRL + T) with totals (CTRL + SHIFT + T).
By the way, if you ever plan to hire me on a time and materials basis, don’t worry… I always round down ;)
Related tips
Quickly format and enable filtering of your data
Our current course schedule can be found online. Virtual training sessions are also still available on request.
Hope to see you soon – whether it's virtually or socially distanced in our training venue!
29 June 2020
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk