If Statement for time

**wtangow** · 11-01-2011, 05:21 PM

Hi,
I'm trying to put together a formula that I can apply to some spreadsheets.
Here is an example of my data
time hour
14:00
14:20
14:40
15:00
15:20
15:40
16:00
16:20
16:40
17:00

I want to be able to us an IF statement to round down the data in my "time" column to the nearest hour and place that in the "hour" column. I have messed with this a bit but keep getting "invalid" errors. Here is what I tried,

IF(OR($A$1=14:00,$A$1<15:00),14:00,IF(OR($A$1=15:00,$A$1<16:00)15,IF(OR(etc...

any ideas?
Thanks for the help!

**MarvinP** · 11-01-2011, 05:27 PM

Hi wtangow and welcome to the forum,

Because you have 2010 Excel you have a function called MRound. I think this is what you need. Read about it at:
http://excel.tips.net/T009359_Roundi...rter_Hour.html

**daddylonglegs** · 11-01-2011, 05:27 PM

Try FLOOR function to round down

=FLOOR(A1,"1:00")

....or another way

=HOUR(A1)/24

format as time

**wtangow** · 11-15-2011, 11:38 AM

Thank you for the quick response and I apologize for the late reply. Both suggestions were great. I ended up using the FLOOR method which was a huge help. Thanks again.