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!
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
One test is worth a thousand opinions.
Click the * below to say thanks.
Try FLOOR function to round down
=FLOOR(A1,"1:00")
....or another way
=HOUR(A1)/24
format as time
Audere est facere
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks