INTRODUCTION
DATES and TIME , if you are new to Excel and you have already tried using those format, I am pretty sure that you must have felt panicky at some sort of time.
It is quite obvious because when we don’t know the exact working of any system or process, we try to use it just by hit and trial method. It works sometimes and sometimes not. Here we are talking about Dates and Time in Excel. These are the tricky formats which we need frequently in our reports or charts.
Many times, we need to do operations on them. We need to put them in the conditions to trigger some event which makes it very important for us to learn the exact procedures to perform a task concerned with the dates and time.
In this article we would learn different tricks and methods to handle and manipulate Dates and Time formats so that they don’t mess up with our reports.
In this article we would learn about.The different ways to use DATE AND TIME to solve our day to day problems.
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.
In this article we would learn the trick to find out the time slot for any given time.
It is done by ROUNDING THE TIME to the nearest hour.The time slots are required in many reports such as TIME IN and TIME OUT attendance sheet and many more.
PUTTING THE TIME IN SLOTS [ ROUNDING THE TIME]
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.
STEPS TO CONVERT THE TIME INTO SLOTS
- Select the cell where the slot time is to be decided.
- Put the formula as =TIME(HOUR(CELL CONTAINING THE TIME),CEILING.MATH(MINUTE(CELL CONTAINING THE TIME),15,0),0)
- For our example, the time is kept in E17 so the formula is =TIME(HOUR(E17),CEILING.MATH(MINUTE(E17),15,0),0).
EXPLANATION OF THE FORMULA USED:
=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.
