Archive for June, 2017

The other day, I was building a spreadsheet to calculate FTE (full time equivalent) for staff based on hours worked on various days in a fortnight. While building the spreadsheet, I came across an interesting problem. Rounding Time to nearest minute.  We can’t use ROUND() or MROUND() to round time as these formulas aren’t designed to work with time values. Although time values are technically decimal, rounding time to nearest minute (or quarter hour etc.) can be tricky when usual round formulas. Let me share a few formulas to round time to nearest point.

Let’s say you have a time value (either user input or calculated) in cell A1.

Use below formulas to round time in A1.

When faced with tough problems I react in one of three ways

1. Come up with ingenious solutions
2. See if a simpler cheat solution is possible
3. Sit back and ignore

For most problems, I choose 3rd reaction. Occasionally, I rely on 2nd option and very rarely the first one.

When faced with a tricky time sheet summary problem (as outlined above), after initial lethargy I wanted to solve it.

Jo, my lovely wife quit her job as my partner in crime at Chandoo.org recently and took up a lucrative position at NZ govt. agency. The other day I asked her “how was your day?” when she got home. She smiled and said, “I learned my first Excel shortcut!”.
Guess what it is?

F4.

That is right. The mighty F4 key. You can use it to repeat any action.

Jo was using it to insert rows in her workbook. After inserting first row (using CTRL+ of course), she would press F4 to add more rows as needed.