Rounding Time to Nearest 5 Minutes

C

carl

I have some time series data. The format of the data is like this -
hh:mm:ss.000 am/pm

I would like to take the value in the cell and round it up to the
nearest 5 minute interval.

For example:

09:31:45.002 would round up to 09:35

12:09:22.004 would round up to 12:10.

Thank you in advance.
 
J

joeu2004

I would like to take the value in the cell and round
it up to the nearest 5 minute interval.
For example:
09:31:45.002 would round up to 09:35
12:09:22.004 would round up to 12:10.
Click to expand...

=ROUNDUP(A1*1440/5,0)*5/1440

formatted as hh:mm:ss.000.

Time is stored as a fraction of a day. So 1 hour is 1/24, 1 minute is
1/1440, and 1 second is 1/86400.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rounding up to the nearest 1000 1
rounding to nearest 100 2
Rounding to nearest 250 by 125s 3
rounding figures to nearest thousand 3
Rounding Time. 4
Rounding time values up and down 2
Is there a formula to round a number to the nearest .09? 5
Rounding to Nearest 250 6

Top