Cable Forum - Excel Help

Cable Forum (https://www.cableforum.uk/board/index.php)

- General IT Discussion (https://www.cableforum.uk/board/forumdisplay.php?f=19)

- - Excel Help (https://www.cableforum.uk/board/showthread.php?t=33648687)

Dougie

16-04-2009 10:52

Excel Help

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

Re: Excel Help

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

Re: Excel Help

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

Re: Excel Help

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.

All times are GMT +1. The time now is 23:10.