How To Put Time Slots In Excel

Most companies need some time tracking method to record and verify how long each employee has worked daily, weekly or monthly on specific projects.

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). 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).

This could be to make regular payments and also to monitor productivity and efficiency of workers.

Timesheets come in various forms. While, traditionally, a timesheet was a ‘sheet’ of paper where you manually wrote and calculated hours worked, it has now evolved into either an online shared document in excel or word format that everyone fills out each day, or an automated online software that runs in the background and tracks what your employees are working on.

1. Excel can store and display dates and times together! First you must format the beginning cell(s). Right-click in the cell(s) and click Format cells. From the Format Cells window, select either Date or Time in the left column, and select a Type that includes both a date and time.
2. In this post I am going to add one more function to the weekly schedule I built in a previous article, an array formula allows you to populate cells with information from a schedule sheet. The first part in this article demonstrates an array formula that contains the TEXTJOIN function, the second part in this post shows you how to show multiple events in a cell with the help of some vba code.

If you want to check out automated time tracking, rather than rely on preparing manual timesheets with Excel, then check out Time Doctor free for 14 days. You’ll see what your team is working on, how long it takes to complete each task, and the apps and websites they visit throughout the day.

Benefits of Time Tracking

Employee time tracking is used by large and small companies, agencies, remote teams, and growing startups, as it provides a quick and easy way to improve the productivity of your business operations.

Timesheets are a way to log what employees were working on for every hour they’re in the office. This way you know what everyone is up to, and where people are spending their time.

How does it help?

You can not only spot your most productive workers but can also analyze which projects or processes are wasting time and which are more profitable for your business.

Once you have this knowledge, you can be a better manager and take insightful decisions for the growth of your business.

In this post, we’ll cover the following:

Excel Time Tracking Templates

We will provide you with templates of the following timesheets:

1. Weekly timesheet
2. Bi-Weekly Timesheet
3. Monthly Timesheet
4. Project Tracking Timesheet

Pros and Cons of Excel Time Tracking

Discuss Excel time tracking sheets which although manual and time-consuming, have certain benefits.

Automated Time Tracking – The Alternative to Excel

Finally, we will show you how to make time far easier, accurate, prevent timesheet fudging and make your team immensely more productive with Time Doctor – an automated time tracking tool.

Why Excel Timesheets?

These timesheets are simple to use because employees only need to log-in the start and end time for work hours and the manager can use automated excel formulas to compute the total pay based on hourly rates and overtime pay.

Moreover, many managers and workers in traditional, non-technical companies, are used to working with Excel rather than SaaS time tracking tools.

Free Excel Time Tracking Templates

Here are 4 free Excel time tracking templates that you can modify to track your employee work hours effectively:

1. Weekly Timesheet

A weekly timesheet is necessary if you calculate your employee work hours, paid-time-off and wages on a weekly basis.

2. Bi-Weekly Timesheet

Some companies pay their employees once every two weeks. This bi-weekly timesheet can come in handy to compute your employee work hours, paid-time-off and wages for every 14 day period.

3. Monthly Timesheet

Use this monthly timesheet if you calculate your employee work hours, paid-time-off and salaries once a month.

4. Project Timesheet

Use this template if you work with different clients and need to calculate hours worked on specific projects. It can also be used in companies where billable hours need to be tracked to invoice customers correctly or where an employee’s time is tracked against projects.

How to use these free excel time tracking sheets

Download the appropriate excel time tracking sheet and modify or add any columns like employee location, employee number, job code or anything else that is necessary to record any specific aspect of your business.

These excel sheets will handle all calculations for you based on formulas included within the spreadsheet cells.

Total Hours are calculated automatically. You only need to enter your Time In and Time Out data.

Total Pay is also calculated automatically based on the Rate Per Hour that you set.

In our free excel templates, the cells containing formulas used to calculate total hours and total pay is locked. This is to prevent any accidental changes being made to them. If you create your timesheets from scratch, specify which cells the users can edit and lock the cells that contain payment calculations before you save your Excel sheet.

You can also incorporate a Vacation/Sick Hours column. The employee can input this time, and it can reflect in the Total Pay depending on your company’s policy of paying for breaks due to illness, vacation, or time off.

Quick Tip: Receiving and storing excel timesheets as printouts or as separate emails from each employee can be cumbersome. To make it streamlined, use Microsoft Sharepoint, to create a document library and enable incoming email. Your employees can then send their time sheets to a central email address.

You can use these excel time tracking sheets to log and monitor employee hours, breaks, overtime, sick time, and vacation time or time away from the computer.

You can also get an overview of how an employee is working – whether they have logged in more overtime hours or taken frequent vacations and sick days – by glancing at his timesheet. Although once more than a few months of data gets accumulated in Excel timesheets, it gets increasingly complicated to get an overview or analyze that data.

Benefits of Excel Time Tracking Sheets

Here are 3 benefits of excel time tracking sheets:

1. Excel is free as it comes bundled with Microsoft Office packages. You can download our templates or create your timesheets on excel from scratch. You don’t have to pay an extra dime to buy any new software or app.

2. It is safe and straightforward to use. All employees need to do is log-in their in-time and out-time. They don’t have to worry about anything else. You as a manager also do not have to compute payments since excel formulas do that for you. It is certainly simpler than using a paper timesheet. Moreover, all data is saved locally on your corporate network drive.

3. No training needed. As a manager or employee, you do not need any additional training to start using Excel timesheets.

Drawbacks of Excel Time Tracking Sheets

But all is not rosy in the Excel world.

Here are 7 reasons why using Excel time tracking sheets may be bad for your business:

1. It is susceptible to human error. If formulas are incorrect, or sum calculations leave out key figures, or you forget to input data in the correct format, it could all lead to gross miscalculations and payroll errors.

Moreover, the most common human error that is faced by excel sheets is mistakenly deleted timesheets. Often, it is challenging to recover data or entire timesheets deleted by accident. This could have disastrous results if there are no backups or systems to recover deleted data.

2. Cannot track actual work hours versus reported work hours. You have to depend on your employees’ ability to clock ‘in’ and ‘out’ times. When reporting time spent on a project, a discrepancy of 5-10 minutes may not be excessive, however, but if these variations are more significant, it can cost your business a lot of money. There is no way to monitor how long a worker is actually working on a task.

3. Difficult to modify or update data. Adding new data or making corrections to spreadsheets is time-consuming. Moreover, corrections may not even be seen until it’s too late for the pay period and the worker may get inadequate wages.

4. Analysis of a workers productivity is also cumbersome with excel timesheets. As a manager, you can look at a workers weekly timesheet to see how long he has worked on various days or specific projects. However, the volume of data presented in a single sheet can make it difficult to see patterns or make any useful insights. It can also lead to wrong interpretation of data and wrong decisions being taken based on those misinterpretations.

5. Historical data is not safe. Excel timesheets are not designed to store historical data. Very often, these files are “updated” to keep their size manageable, and in the process companies lose historical data.

Without historical data, it is difficult to spot long-term trends or strategize how to build productivity systems for the company and its workers.

6. Not designed for ‘real time’ work. When you have a large team, it is very cumbersome to track which employees have submitted their timesheets.

Often, the timesheet that is shared is not in real-time, so managers get delayed information on actual hours worked or inadequate information on changed circumstances. If an employee is on break for medical or emergency reasons, it may not reflect in the timesheet.

Time tracking sheets are, at best, emailed once a week. This can pose the problem of the data getting lost in the managers inbox, duplication of data or even getting the wrong version of the file.

7. Excel tends to crash. The more you update and share a particular excel sheet, the higher the chance of the file getting corrupted and the data being lost.

Automatic, non-intrusive time tracking

Using a cloud based-time tracking app can make it easier and effective to monitor employee work hours, not only for the manager but also for the employee.

A good time tracking app will have multiple features to help you improve employee productivity, prevent misuse of company time for personal reasons, help in workforce planning and allocation, track project progress, manage client billing and so on.

Moreover, they are stored in a single database which makes it easier to generate reports for individuals, small groups of employees, departments or the entire company. Another benefit is the ease of identifying when changes were made to time sheets and by which users. Also, corrections made on time tracking apps are instant which means fewer payroll errors.

Why Time Doctor?

Time Doctor is an automated, cloud-based time tracking app used by companies running remote teams, digital agencies, and more.

The tool monitors work hours in real-time and right down to the second, so they don’t have to remember if they spent 5.30 hours on the APPLE project yesterday or 5.39 hours.

You can track time spent by each employee on specific tasks and projects. The app not only tracks the total time worked by every member on your team but also provides a breakdown on how much time is spent which projects (or clients) and tasks.

However, the tool does not monitor employees’ activity during their free time or after office hours.

Total time worked on each task can be manually modified, and the manager can see whether any manual addition or deletion has been done.

You can use Time Doctor on a desktop as well as mobile. Detailed reports are available on your web-based account.

Here are the key time tracking features of Time Doctor:

Tracking in real time

Once you have finished working, it can get difficult to remember accurately how much time you spent on which tasks. Moreover, if you don’t fill your manual timesheets regularly, it can lead to an inaccurate accounting of start and end times. When using Time Doctor, you don’t need to remember anything, because time is tracked automatically as you’re working.

Time Doctor automatically generates timesheets, and no manual data entry is required.

Reminder to track time

The tool reminds you to start tracking time and also to stop tracking time when employees visit websites that may not be work-related (like Facebook).

Doesn’t track coffee breaks

The moment you leave your computer, Time Doctor automatically stops tracking time. Often, when manually tracking time employees forget to count the numerous coffee breaks and bathroom visits causing inaccurate tracking of work hours.

The moment there is no keyboard or mouse activity for a while, Time Doctor stops tracking. It also gives users the option to show afterward whether or not they were working during that time.

Multiple methods to verify accuracy of time tracked

The tool utilizes various methods to confirm if the time tracked was real work. These include screenshots taken at regular intervals, levels of keyboard and mouse activity, and which websites or applications are used.

You also can’t “double book” your time. For instance, if you allocate time for activity A to 3-5pm, you can’t assign a second activity to that same time slot.

Other features of Time Doctor worth exploring:

Website and application monitoring

Time Doctor keeps track of all the applications and websites employees visit during work hours. Managers and individuals receive a weekly report outlining which websites and applications were used, and for how long.

This report helps identify productivity issues. For example, if you’re a content writer and you’re only spending 20% of your time on writing, you can see what’s occupying the rest of your time and make adjustments to get back on track.

This is also an extremely useful feature if you work with remote employees or freelancers. This feature can help ensure that they are actually working and not doing personal work on company time. If you use excel for time tracking, this would have been impossible to verify.

Privacy Controls

The tool allows you to take screenshots of employees’ screens but only if you choose to enable this feature and with employees’ consent. Employees can also delete screenshots if they were accidentally running Time Doctor when doing personal tasks like commenting on Facebook.

Time tracking with screenshots is useful for monitoring remote employees, and for some types of companies such as design shops which can use them to review work processes and improve productivity.

Billing

You can use Time Doctor to make payments to your employees and freelancers directly by integrating it with PayPal, Payoneer, TransferWise and several other payment apps. Payments can be made based on hours worked.

Managing distractions

If you open a social media or any entertainment site while working on a task, Time Doctor will generate a pop-up to ask you if you are still on your current task. It’s a great way to avoid getting distracted.

The tool also sends a weekly poor time use report to team managers with a list of distracting websites and applications viewed and the duration they were used, during work hours.

Advanced reporting

Time Doctor has the capability of creating several types of reports such as Timesheets, Time Spent on Each Project/ Task and several others. These reports can be customized for different parameters – for a particular employee, a group of employees, a particular project, date ranges, etc.

You can use this information to analyze which project or clients are more profitable and where you may be losing money.

Conclusion

If you are a solopreneur, have a really small team or only just starting out tracking employee time, it may be simple and effective to go with our free excel time tracking sheets.

However, once your business grows, and you are ready to spend on a really cool and efficient time tracking tool that is completely automated and takes the entire burden of tracking employee work hours out of your hands, give Time Doctor a shot.

• The Best Timesheet Apps of 2020-
• Working from Home vs Office: Some Pros and Cons-
• How to Avoid Burnout (10 Actionable Strategies)-
• The Top 10 Challenges of Global Virtual Projects in 2020-
• Synchronous vs Asynchronous Communication for Remote Teams-

I recently showed several ways to display Multiple Series in One Excel Chart. The current article describes a special case of this, in which the X values are dates. Displaying multiple time series in an Excel chart is not difficult if all the series use the same dates, but it becomes a problem if the dates are different, for example, if the series show monthly and weekly values over the same span of time.

This discussion mostly concerns Excel Line Charts with Date Axis formatting. Date Axis formatting is available for the X axis (the independent variable axis) in Excel’s Line, Area, Column, and Bar charts; for all of these charts except the Bar chart, the X axis is the horizontal axis, but in Bar charts the X axis is the vertical axis. Any of the formatting described here applies to all of these chart types.

XY Scatter charts are different: X axes behave like Y axes. I could write a book just on this subject.

Displaying Multiple Time Series in An Excel Chart

The usual problem here is that data comes from different places. While the data may span a similar range of dates, the different data sets may have varying intervals between recorded values. Perhaps you have daily temperature readings you want to plot against historic monthly temperatures.

I’ve generated the following arbitrary monthly and weekly data for this exercise.

When plotted in separate Excel line charts, this is how it looks.

Displaying Multiple Time Series in A Line Chart

Line Chart 1 – Plot by Month

Start by selecting the monthly data set, and inserting a line chart. Excel has detected the dates and applied a Date Scale, with a spacing of 1 month and base units of 1 month (below left). Select and copy the weekly data set, select the chart, and use Paste Special to add the data to the chart (below right).

To get to Paste Special, on the Home tab, click on the Paste dropdown, select Paste Special, and make sure you’ve selected the settings below:

Excel’s line charts use the same data for all series in the chart, or more precisely, for all series on a particular axis. So let’s assign the weekly data to the secondary axis (below left). Excel only gives us the secondary vertical axis, and we really needed the secondary horizontal axis. Using the “+” skittle floating beside the chart (Excel 2013 and later) or the Axis controls on the ribbon, add the secondary horizontal axis (below right).

Finally format the secondary date axis with the same settings as the primary date axis: Minimum: 1/1/2016, Maximum: 4/1/2016, Major Units: 1 Month, Base Units: Months.

And that looks horrible. With Base Unit of Months, Excel plots everything in a month at one horizontal position, so all weekly values in January are plotted with the monthly value for January 1. So let’s look at these base units.

Date Axis Base Units

Among the options for formatting a Date Axis are the units. These include Major Units (major tick mark spacing), Minor Units (minor tick mark spacing), and Base Unit. Base units are the categories that Excel uses to handle the dates in the data. If base unit is Days, then there will be a slot on the axis for each date within the span of the axis; if base unit is Months, then there is one slot per month along the axis; if base unit is Years, well, you get the picture.

I’ve used a darker line for the axes in the two charts below, I’ve formatted the major ticks to cross the axis and the minor ticks to lie outside the axis, and I’ve added faint droplines to the points, all to illustrate this concept.

The chart below left uses Months for its base unit. There is one slot for each month, the slot’s label is centered within the slot, and the slot’s data point is also centered. If there is no data point for a given slot, the line connecting points would have extended across the unpopulated slot. If there are multiple data points for a given slot, such as the four weekly points in each month in our first attempt above, these points will all be aligned with the center of the slot.

The chart below right uses Days for its base unit. Labels appear every month, so they are centered on the slots for the first point of each month. Data points also appear on the first of each month, and the slots for all the other days of the month lie empty, with the series line connecting points across the empty slots.

The points in the first chart are equally spaced horizontally, since the slots for each month are the same width. The points in the second chart are not equally spaced, since the slots for each day are equally spaced (given rounding errors across pixels) and the months have different numbers of slots (days).

You could count minor tick marks to verify this, but I’ve made the following illustration with two copies of this chart. They are identical: I’ve lightened the horizontal gridlines but added vertical gridlines, hidden the plotted data and chart titles, and colored one red and the other blue. I’ve offset the blue chart laterally so its 1/1/2016 gridline is aligned with the red chart’s 2/1/2016 gridline. Note that the next red gridline comes before the next blue gridline: that’s obvious once we note that February’s 29 days make up a shorter month than March’s 31 days. However, the red (4/1/2106) and blue (3/1/2016) gridlines after that line up, since February + March have 29 + 31 = 60 days on the red chart, and January + February have 31 + 29 = 60 days on the blue chart.

What’s really useful is that a 1-month spacing of labels on the line chart lets you put labels on the first of each month, even with months of unequal length.

Line Chart 2 – Plot by Day

Let’s start again by plotting the monthly data in a line chart. But let’s set our base unit to Days (below left). As before, copy the weekly data, and use Paste Special to add it as a new series to the char. Only the first four weekly points show up, aligned with the monthly point, because the monthly series only has four points (below right).

How To Put Time Slots In Excel File

To allow display of all points in the weekly series, format it so it appears on the secondary axis. Excel at first only draws the secondary vertical axis (below left). Use the “+” icon floating beside the chart (Excel 2013 and later) or the Axis controls on the ribbon toadd the secondary horizontal axis (below right).

Rescale the secondary horizontal axis so it matches the primary: make sure the minimum and maximum units are the same (below left). Finally, you can do a little clean-up. Delete the secondary vertical axis, and all data will be plotted on the primary scale (which was the same anyway). Hide the secondary horizontal axis by formatting it to use no line and no labels (below right).

This is pretty good, but it’s a bit complicated, and if we have a third data set with different dates, we have no more axes to plot it on.

Displaying Multiple Time Series in An XY Scatter Chart

XY Scatter charts have X axes which are much more flexible, so let’s try one with our data.

Time Slot Spreadsheet

Select the monthly data, and insert an XY Scatter chart.

That X axis is cluttered, and what’s up with those axis limits? 12/22/2015 to 4/20/2016? Those aren’t nice round numbers.

Let’s take another look at the data. I’ve duplicated the data in columns A to E in columns G to K, but I’ve formatted the dates as General numbers in columns G and J. Those nice dates that go from 1/1/2016 to 4/1/2016 are actually values that go from 42370 to 42461.

If we reformat our chart’s axis to show General numbers, the scale of 42360 to 42480 looks reasonable (see below). So an XY Scatter chart isn’t as smart as a line chart when picking dates. No matter, will fix it in post.

So let’s revert to dates on the axis.

Copy the weekly data, and use the by now ultrafamiliar Paste Special to add it to the chart (below left). Reformat the horizontal axis so it scales from 1/1 to 4/1/2016, and pick a reasonable major unit (below right).

But look at the tick labels. The reasonable major unit of 30 days gives me funny dates: 1/1, 1/31, 3/1, and 3/31. If I used 31 days instead, I’d get 1/1, 2/1, 3/3, and 4/3. Again, an XY Scatter chart isn’t so smart with dates, despite its flexibility in other ways.

Well, we can hide the axis labels and add a dummy series with data labels that provide the dates we want to see. Here is the data for our dummy series, with X values for the first of each month and Y values of zero so it rests on the bottom of the chart.

Hide the axis labels by using a custom number format of ” ” (a space surrounded by quotes). If we just set the axis to show no labels, the margin below the axis would have collapsed, but using this dummy number format uses a space character for each label, preserving the space for our replacement labels (below left).

Use Copy – Paste Special to add the new axis data to the chart as a new series (below right).

Add data labels below this new series (below left), and format the labels to show X Value, not Y Value (below right).

Format the Axis series so it uses no line and a gray cross marker (below left). Finally do some clean-up. Delete the Axis legend entry (click once to select the legend, again to select the Axis entry, then click Delete). Make the plot area a bit narrower so the date labels are centered under the markers. I deleted the vertical gridlines, because they did not line up with the axis markers (below right).

You could simulate vertical gridlines by adding plus error bars to the Axis series, but we’ve already spent way too much time on this stupid chart.

When To Use XY Charts for Timeline Data

If you don’t need monthly increments along the X axis, then is makes sense to use an XY Scatter chart for your time series.

A more important case for using an XY Scatter chart for a timeline is when the spacing of points is on the order of hours or less, rather than days. The data below shows four unevenly-spaced points per day over a two day span. The Line chart with a base unit of Days plots all of the points for each day at one horizontal position, while the XY Scatter chart plots the points horizontally according to the time of day.

This was the same problem our first line chart attempt ran into while trying to plot multiple days within a month, when the base unit was Months.

Displaying Multiple Time Series in A Line-XY Combo Chart

Now for a short trip down Memory Lane.

In Excel 2003 and earlier, you could plot an XY series along a Line chart axis, and it worked really well. The line chart axis gave you the nice axis, and the XY data provided multiple time series without any gyrations.

So the process was, make a line chart from the monthly data (below left). Copy the weekly data and use Paste Special to add it to the chart. Only four points were visible, but be patient (below right).

Change the weekly series to XY Scatter type (below left). Finally, assign the weekly XY series to the primary axis (below right).

This was really nice, because you could get your nice axis even using a dummy/hidden line chart series. Then you could add as many time series as you wanted, with whatever arbitrary and different date values they contained, all using just the primary axis, so you didn’t have to change the scale on one to keep up with another, etc.

This behavior has been broken since 2007, and I suspect it’s gone for good. In fact, this is one reason I was so slow to adopt Excel 2007.

Try the same process in Excel 2007 or later (this is Excel 2016). Chart the monthly data (below left) and add the weekly data (below right).

Convert the weekly data to an XY type (below left), then move it to the primary axis (below right).

Everything was cool until the real maintenance saving step of using the primary axis for everything. But on the primary axis you can only plot as many XY points as there are points in the original line chart series. I guess you could plot a whole blank column as your line chart, and now that I’ve thought of it, maybe that’s what I’ll start doing.

Displaying Multiple Time Series in A Line Chart – A Better Way

In fact, there is a pretty reasonable and not too convoluted way to get multiple timelines with different date sequences on the same chart. It requires laying out your data differently, and tweaking the chart in the most minor but slightly obscure way.

Start with your monthly data in A1:B5. Add the weekly dates below the monthly dates (A6:A18). Add the weekly values below the monthly values, and one column to the right (C6:C18), with the weekly header in C1. (You can repeat this using more rows and columns for many more series as well.)

Time Slot Template Excel

Select the data and insert your line chart.

Looks pretty good, except for the gaps. Why are there gaps, anyway? Excel puts a gap between points that have blank cells, but my data had no such gaps.

Time Slot Template Schedule Excel

Well, in a line chart with a date axis, Excel sorts the data behind the scenes before plotting it. So if I sort my data, I can see the gaps in the worksheet range that correspond to gaps in the chart.

How To Put Time Slots In Excel Word

This is easy to fix. Select the chart, choose Select Data from the ribbon (or right click the chart and choose Select Data), and click the Hidden and Empty Cells button at the bottom left of the Select Data Source dialog. In the mini dialog that pops up, select the Connect Data Points With Line option for Show Empty Cells. The result is the nice multiple time series chart below right, without any gaps, all on one set of axes, with almost no messing around.