PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
BEFORE READING THIS ARTICLE , IT IS REQUESTED TO VISIT THE PART I WHICH DISCUSSES THE CONCEPT OF THE DATE AND TIME IN EXCEL FOR BETTER UNDERSTANDING.CLICK HERE TO VISIT.
Many times the time is divided into slots and we need to take the working time from the slot only and not before that.
In such case we need to round the time to the nearest slot.
Suppose we have a situation in which the time would be taken from the next 15 min slot for example if the activity starts at 10:10 the effective time would be from 10:15.
The effective timings will be every 15 min, e.g. 10, 10:15, 10:30, 10:45 and so on.
=TIME(HOUR(E17),CEILING.MATH(MINUTE(E17),15,0),0)
We started with the outer function TIME, which would take three inputs as HOUR, MINUTES and SECONDS.
HOURS are extracted simply using the HOUR FUNCTION which will give the number of hours from the given time.
As we need to round off the minutes, we use the function CEILING.MATH in which we put the inputs and have given the significance of 15 minutes, seconds are zero.
Now any time would be rounded to the next 15 min slot.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE