Excel Help

Dougie · 16-04-2009, 10:52

Hello, are there any advanced Excel users on here that can help me?

I have a monthly report that details hours and minutes run for various items. Unfortunately the source of the spreadsheet creates the hours in one column and the minutes in another column. I want to combine these two cells and round them up or down to the nearest hour.

Does that make sense, is it even possible to automate this process?

Thank You

gazzae · 16-04-2009, 11:04

Try this

http://support.microsoft.com/kb/266691

cookie_365 · 16-04-2009, 20:22

I'm sure there are more elegant ways, but

Code:

=ROUND(((A1*60)+B1)/60,0)

where A1 is the hours and B1 the minutes should do it.

DRZ400 · 16-04-2009, 21:10

Or much less elegant if you try hard!!

Code:

 =IF(AND(A1=23,B1>30),0,IF(B1>30, A1+1,A1))

Id use cookies code with an addition of if it's 23:34 for example, it'll round it up to '0' rather than '24'. The 1410 is 23*60 minutes, plus the 30 minutes.

Code:

 =IF((A1*60)+B1>1410,0,ROUND(((A1*60)+B1)/60,0))

Both work.

cookie_365 · 17-04-2009, 19:48

I'm not sure you'd want to reset above 24 down to 0 - aren't we working on numbers of hours, not clock times?

But if you were working on times and wanted to combine separate hour and minute readings into the nearest hour, then I'd go for

Code:

=ROUND(MOD(((A1*60)+B1)/60,24),0)

But like I said - I don't think the OP is after that.