Excel 2007 : Rounding up minutes to nearest hour?

**Belatrix101** · 12-17-2009, 10:24 AM

Hi, could anyone help me with this, I am doing a spreadsheet for my work rosters to total my hours worked(& eventually automatically work out my pay!)

Some shifts start at midnight but are shown as starting at 00:01, I would like to keep this but when totalling my hours it will state hours as 07:59 & i'd like to round it up to 08:00. I have searched the forums & come across a few variations but haven't the knowledge to implement it into my spreadsheet!

Have uploaded the spreadsheet with a few explanations, hope you can help

**DonkeyOte** · 12-17-2009, 10:29 AM

What should happen with say 10:00 to 14:24 ?

Should that be 2:24, 2:25, 2:30 or 3:00 ?

Assuming 2:25 (ie round up to nearest 5 min interval) then

=CEILING((B3>C3)+C3-B3,"00:05")

change the significance as per your requirement

**Belatrix101** · 12-17-2009, 10:43 AM

Originally Posted by DonkeyOte

What should happen with say 10:00 to 14:24 ?

Should that be 2:24, 2:25, 2:30 or 3:00 ?

Assuming 2:25 (ie round up to nearest 5 min interval) then

=CEILING((B3>C3)+C3-B3,"00:05")

change the significance as per your requirement

DonkeyOte, all shifts finish on the hour, so your example wouldn't be an issue, being a newbie to Excel I was thinking along the lines of say...... if minutes > than 50 then reset to 00 but add 1 to the hour but in code format!