| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting time data to nearest ten minute block
Hi,
I have a column of time data in hh:mm format which I need to adjust. I need the time to be changed to the nearest ten minutes so 13:01 would change to 13:00 and 13:05 would round up to 13:10. Can you help? Would I need to try and if statement with Right(A1,1) etc? Thanks, Matt |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adjusting time data to nearest ten minute block
Hi,
Do it like this =ROUND(A1*(24*6),0)/(24*6) where 24*6 equals the number of 20 minute periods in a day, there are 6 ten minute periods in an hour. Changing the to 24*4 makes it work for 15 minutes -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "MJKelly" wrote: Hi, I have a column of time data in hh:mm format which I need to adjust. I need the time to be changed to the nearest ten minutes so 13:01 would change to 13:00 and 13:05 would round up to 13:10. Can you help? Would I need to try and if statement with Right(A1,1) etc? Thanks, Matt . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adjusting time data to nearest ten minute block
I meant
where 24*6 equals the number of 10 minute periods in a day -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Do it like this =ROUND(A1*(24*6),0)/(24*6) where 24*6 equals the number of 20 minute periods in a day, there are 6 ten minute periods in an hour. Changing the to 24*4 makes it work for 15 minutes -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "MJKelly" wrote: Hi, I have a column of time data in hh:mm format which I need to adjust. I need the time to be changed to the nearest ten minutes so 13:01 would change to 13:00 and 13:05 would round up to 13:10. Can you help? Would I need to try and if statement with Right(A1,1) etc? Thanks, Matt . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Round to the nearest minute if more than 6 seconds | Excel Worksheet Functions | |||
| Adjusting numbers to nearest preferred number | Excel Worksheet Functions | |||
| Rounding Up To Nearest Minute | Excel Discussion (Misc queries) | |||
| how do i round time to the nearest half a minute | Excel Discussion (Misc queries) | |||
| convert time from 60 minute hour to 100 minute hour | Excel Discussion (Misc queries) | |||
Hybrid Mode
