HOW TO CREATE TIME SLOTS IN EXCEL

CONTENTS

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.

CLICK HERE TO VISIT.


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).
CREATING THE TIME SLOTS

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.

UPDATE 1:

There can be some confusion about the CREATION OF THE SLOTS. The previous discussion was about taking a standard slot ending by rounding the time.

Let us complement the article by creating simple slots too if you require them.

CREATE TIME SLOTS IN EXCEL

Time slots are created if we want to group the time within small equal portions within an hour.

For example, if we want to create 15 minute slots, we can do so by taking the time as [ say between 10 am to 11 am ]

  1. 10:00 am to 10:15 am
  2. 10:16 am to 10:30 am
  3. 10:31 am to 10:45 am
  4. 10:46 am to 11:00 am

So these are the slots which we can create.

we can create any number of slots.

Let us learn the process with the help of an example.

CONCEPT: HOW TO CREATE TIME SLOTS

The slots can be created very easily.

If we have the TIME FORMAT, we can add the time to the same and drag down the cell to create the slots.

But, we can’t add the minutes to the time directly. We need to convert the minutes to the decimal portion first.

LEARN THE TIME CONCEPT HERE.

We can create the slots in two ways.

  1. CREATING SLOTS IN THE EXCEL BY ADDING THE NUMERICAL VALUE TO THE TIME
  2. CREATING THE SLOTS USING THE AUTO FILL OPTION



EXAMPLE DETAILS: CREATE THE 15 MIN TIME SLOTS FOR A COMPLETE DAY [ 24 HRS ]

Let us create the 15 minutes slots with the use of the above said methods.

The following picture shows the slots, which we want to create.

We’ll be creating the slots in two ways. One

EXAMPLE



CREATING SLOTS IN THE EXCEL BY ADDING THE NUMERICAL VALUE TO THE TIME

TIME SLOTS STYLE 1:

We’ll be mixing the ways to create different types of slots. You can choose whatever suits you.

Let us create the column E with the starting time and Column F with the ending time.

Column E contains the starting time and Column F contains the ending time.

The starting time of the next slot starts from the next minute from the ending time of the previous slot.


FOLLOW THE STEPS TO CREATE COLUMN E

  • Enter the time as 00:01 in any row in the column E. [ For example, put the value in E4 ]
  • As soon as we type 00:01, Excel will sense it as a time and automatically change the format to the time. If not change the format to the time.
  • In the next row, under the same column enter the formula as =E5+0.010417. [ The added value is the numerical equivalent of 15 minutes. REFER HERE FOR DETAILS ]

FORMULA ENTERED IN E6 CELL
  • Hold the DRAG HANDLER of the cell E5 and drag it through the 23:46 which will mark our last slot of the day.

CREATING COLUMN E IN STYLE 1

Our COLUMN E i.e. TIME START COLUMN is ready.

Let us now prepare the Column F to show the ending time.

FOLLOW THE STEPS TO CREATE COLUMN F

The column F will be created in a different way using the auto fill.

The values which are going to appear in COLUMN F are in the proper round and divisible by 5 which will help the auto fill work perfectly.

  • Enter the first value as 00:15 in the cell F4 which is the ending time of the first slot.
  • As, we want to use the auto fill , at least two values need to be given to EXCEL so that it can try to automate the values.
  • Enter the value 00:30 in the cell F5.
  • Select the cells F4 and F5 and drag down the formula through all the slots.

DRAG DOWN THE CELLS TO AUTO FILL ALL THE VALUES

Check if auto fill is properly working or not.

OUR TIME SLOTS ARE READY AS PER SLOT STYLE 1.

Let us now learn to create the time slots in SLOT STYLE 2.

TIME SLOTS STYLE 2:

By now, you must have got an idea to create the SLOT STYLE 2 using the simple ways.

So, we can simply follow any of the way, which are already discussed in the SLOT STYLE 1 i.e. by adding the numerical value or by using the autofill.

Even then, just follow the steps to create the slots as per SLOT STYLE 2.

FOLLOW THE STEPS TO CREATE SLOTS AS IN COLUMN I IN SLOT STYLE 2

  • Simply add the first two values in the cell I4 and I5 as 00:00 and 00:15.
  • Select both the cells and drag down the handler through the length enough to get all the slots needed.
  • Auto fill will provide all the desired values.

CREATING TIME SLOTS IN STYLE 2
IF THE VALUES ARE LESS , YOU CAN AGAIN SELECT ANY TWO OR MORE VALUES FROM THE BOTTOM AND AGAIN DRAG THE HANDLER TO GET MORE VALUES AND FILL AS MANY AS CELLS YOU REQUIRE.

Leave a Reply

Your email address will not be published. Required fields are marked *