asked on

Date time format in Excel

I have a date time column in column A with the following (sorted) values
10/1/2011 1:31:00
10/1/2011 21:18:00
10/1/2011 22:51:00
.
.
10/2/2011 1:32:00
10/2/2011 2:56:00

I want to assign bin of step size 15 minutes, to values in column A. The bin assignment formula I was using was

 time(hour(A2),round((minute(A2)/60)*60/15,0)*15,0)

Open in new window

However, this formula ignores the date part. For instance, it will assign the same bin to 10/1/2011 1:31:00 and 10/2/2011 1:32:00.

How do I include the date in the formula to get assign different bin to the time in different date?
pax

byundt

If I understand you correctly, you want to calculate the nearest 15 minute time to the date/time serial number in column A. If so, consider:
=MROUND(A2,15/1440)

cpeters5

ASKER

Not quite. This works the same way as the formula I used. Since it disregard the day has changed to the next day.

=MROUND(11/03/2012 4:09) and =MROUND(11/04/2012 4:09) both give 4:15.

I need a function that produces 28:15 for the second date (11/04/2012 4:09)

byundt

Format the cell with MROUND as date and time. You will see that it has a different date.
Data        MROUND formula
10/1/2011 1:31      10/1/2011 1:30
10/1/2011 21:18      10/1/2011 21:15
10/1/2011 22:52      10/1/2011 22:45
10/2/2011 1:32      10/2/2011 1:30
10/2/2011 2:56      10/2/2011 3:00

ASKER CERTIFIED SOLUTION

byundt

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

cpeters5

ASKER

Very clever! Yes it works perfectly.
Thank you,
pax