Anyone get that song reference? I give you a clue down the bottom of the post…

Well have to say I do love my students (in a good way…not a weird way I hasten to add. :-).). I recently had a woman in class who had the following Excel conundrum…

1. She needed to calculate the hours worked by staff members…so she had their clock in time/clock out time.
2. I’m not sure if this was the case but what I’ve done in this example is assume that the staff members clocked in and out for lunch.
3. So she needed to be able to calculate their hours worked but rounded up to the next 15 minutes e.g. if someone worked 10 hours and 10 minutes, that had to be rounded up to 10 hours and 15 minutes for payment purposes.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/time_calculations.mp4″]

1. But what I did was enter the following formula…after I had formatted the times to HH:MM (see Format Cells – (use Ctrl and 1 – Custom)
2. =(C3-B3)*24-(E3-D3)*24
3. That gave me the total number of hours/minutes used in decimal format.
4. However, I then combined this with the Ceiling function to round up to the next 15 minutes (.25 of an hour
5. You can download the file here

PS you can find out about the lyrics of the song here..