Excel - Subtract time Tutorial

In this video, you will learn about how to search and find what you need using Microsoft 365. The video covers the usage of OneDrive, Yammer, and Delve in the Office 365 subscription to help you access and organize your documents, collaborate with colleagues, and discover relevant information.
This tutorial will enhance your productivity and efficiency in finding and managing your files.

3:58
4256 views

Available in Microsoft365

Objectifs :

This document aims to provide a comprehensive guide on how to subtract time in Excel, detailing the necessary formulas, formatting options, and practical examples to enhance understanding and application of time calculations.

Chapitres :

Introduction to Subtracting Time in Excel
Subtracting time in Excel is similar to subtracting other types of numbers, with the notable exception that Excel does not support negative time values. Instead, negative time is displayed as a series of number signs. This guide will walk you through the process of calculating hours worked and formatting the results appropriately.
Understanding Time Representation in Excel
In Excel, time is stored as a fraction of a 24-hour day. For example, 12 PM is represented as 0.5, which signifies half a day. To convert this fraction into hours, you multiply it by 24. This foundational understanding is crucial for accurate time calculations.
Subtracting Time Using Formulas
To subtract time, you will need to use a formula since there is no automatic subtract button in Excel. Follow these steps: 1. Type an equal sign `=`. 2. Open a parenthesis `(`. 3. Click on the cell containing the later time (e.g., C2). 4. Type a minus sign `-`. 5. Click on the cell containing the earlier time (e.g., B2). 6. Close the parenthesis `)`. 7. Multiply by 24 by typing `* 24`. 8. Press Enter. This formula will yield the total hours worked, for example, 6.5 hours.
Formatting the Result
To display the result with two decimal places: 1. Right-click the cell with the result. 2. Select 'Format Cells'. 3. Under 'Category', choose 'Number'. 4. Ensure the decimal places are set to 2 and click OK. To apply the same formula to other cells without retyping it: - Click the cell with the formula. - Hover the mouse pointer over the bottom right corner until it turns into a black plus sign. - Click and drag to fill the adjacent cells.
Handling Elapsed Time Greater Than 24 Hours
When the elapsed time exceeds 24 hours, you can format the result to display it as time rather than a number. To do this: 1. Type an equal sign `=` and click on the cell with the later time (e.g., C7). 2. Type a minus sign `-` and click on the earlier time cell (e.g., B7). 3. Press Enter. 4. Right-click the result cell and select 'Format Cells'. 5. Choose 'Custom' under 'Category'. 6. Scroll to the square bracket format created in the previous video and click OK. This will display the elapsed time, for example, as 31 hours and 30 minutes.
Subtracting Specific Units of Time
You can also subtract specific units of time, such as one and a half hours, from a given time of day. For instance, to subtract from 10 AM: 1. Type an equal sign `=`. 2. Click on the cell with the time (e.g., A10). 3. Type a minus sign `-` followed by the TIME function: `TIME(1, 30, 0)` for one hour and thirty minutes. 4. Press Enter. This will yield the calculated time, such as 8:30 AM.
Conclusion
By following these steps, you now have a solid understanding of how to add and subtract time in Excel. While this guide covers the basics, there is always more to learn about Excel's time functions and capabilities.

FAQ :

How do I subtract time in Excel?

To subtract time in Excel, you can use a formula that involves an equal sign, parentheses, and a minus sign. For example, to subtract the time in cell B2 from the time in cell C2, you would enter the formula: =(C2-B2)*24. This will give you the result in hours.

What happens if I try to display negative time in Excel?

Excel does not support negative time formatting. If you attempt to display a negative time, it will show as a series of number signs (#####).

How can I format the result of a time calculation to show hours and minutes?

To format the result of a time calculation in Excel to show hours and minutes, right-click the cell with the result, select 'Format Cells', choose 'Custom', and then select the appropriate time format.

Can I subtract a specific amount of time from a time of day in Excel?

Yes, you can subtract a specific amount of time from a time of day using the TIME function. For example, to subtract 1.5 hours from 10 AM, you would use the formula: =A10-TIME(1,30,0).

How do I copy a formula to other cells in Excel?

To copy a formula to other cells in Excel, click on the cell with the formula, hover over the bottom right corner until the cursor changes to a black plus sign, click and hold the left mouse button, and drag to fill the desired cells.

Quelques cas d'usages :

Employee Time Tracking

In a workplace setting, managers can use Excel to track employee hours worked by subtracting clock-in and clock-out times. This helps in calculating total hours worked for payroll purposes.

Project Management

Project managers can utilize time subtraction in Excel to determine the time spent on various tasks. By subtracting start times from end times, they can analyze productivity and allocate resources effectively.

Scheduling

In scheduling applications, users can subtract time to find out how much time is left before a meeting or deadline. This can help in planning and prioritizing tasks.

Event Planning

Event planners can use Excel to calculate the duration of events by subtracting start times from end times, ensuring that events are scheduled efficiently and within the allocated time.

Time Management

Individuals can apply time subtraction techniques in Excel to manage their daily schedules, allowing them to see how much time they have left for personal tasks after accounting for work hours.

Glossaire :

Excel

A spreadsheet program developed by Microsoft that allows users to organize, format, and calculate data with formulas.

Negative Time

A time value that represents a duration less than zero. Excel does not support negative time formatting and displays it as a series of number signs.

Fraction of a 24-hour day

In Excel, time is represented as a fraction of a full day. For example, 12 PM is represented as 0.5, which is half of a 24-hour day.

Formula

An expression in Excel that performs calculations on values in cells. Formulas begin with an equal sign (=).

Format Cells

A feature in Excel that allows users to change the appearance of cell data, including number formats, font styles, and more.

Custom Format

A user-defined format in Excel that allows for specific display options for numbers, dates, and times.

TIME function

An Excel function that returns a time value based on specified hours, minutes, and seconds.

00:00:08

The one exception is that Excel doesn't support

00:00:11

a negative number formatted as time.

00:00:14

Excel displays negative time, as an endless series of numbers signs.

00:00:22

In this example, we want to figure out how many hours a person worked,

00:00:26

and we want the results displayed as a number

00:00:28

with a fraction of an hour;

00:00:30

such as 6.50 hours as opposed to 6:30,

00:00:36

6 hours and 30 minutes.

00:00:39

Time is stored in Excel as a fraction of a 24 hour day.

00:00:44

So 12 pm would be 0.5, half a day.

00:00:49

To go from half a day to hours,

00:00:51

we need to multiply it by 24 hours in a day.

00:00:56

Enough said, let's subtract time.

00:00:59

To do this, you use a formula.

00:01:02

There isn't an auto-subtract button.

00:01:05

Type an equal sign, open parenthesis, click cell C2,

00:01:12

type a minus sign, click cell B2, type a closing parenthesis,

00:01:18

asterisk, which multiplies numbers,

00:01:22

24 for 24 hours in a day.

00:01:26

The parenthesis ensure that subtraction occurs first,

00:01:30

and the resulting number is then multiplied by 24,

00:01:34

And press Enter.

00:01:36

And we see that Richard worked: 6.5 hours.

00:01:40

I want 2 digits after the decimal.

00:01:43

So I right click the cell, and click Format Cells.

00:01:48

Under Category, I click Number.

00:01:51

The default is 2 digits, so I click OK.

00:01:56

To fill in the rest of the cells, you don't have to type

00:01:59

the formula multiple times.

00:02:02

Click the cell with the formula,

00:02:04

put the mouse pointer over the bottom right hand corner

00:02:07

until it becomes a black plus sign,

00:02:10

click and hold the left mouse button, and drag the plus sign

00:02:14

over the cells you want to fill.

00:02:16

The formula and formatting

00:02:18

are copied into the cells.

00:02:22

In this example, the elapsed time is greater than 24 hours,

00:02:26

and I want it shown as time, not a number.

00:02:30

I type an equal sign, click cell C7,

00:02:34

type a minus sign, click cell B7 and press Enter.

00:02:41

To format the cell to show the results the way I want it,

00:02:45

I right click the cell and click Format Cells.

00:02:49

Under Category, I click Custom.

00:02:53

In the type list, I scroll down to the square bracket format

00:02:56

we created in the add-time video and click OK.

00:03:01

The elapsed time is 31 hours and 30 minutes.

00:03:07

You can also subtract a unit of time, such as one and

00:03:10

a half hours from a time of day such as 10 am by using the time function.

00:03:18

I type an equal sign,

00:03:20

click A10, type a minus sign,

00:03:25

TIME, left parenthesis,

00:03:29

1 for the number of hours we want to subtract, comma,

00:03:34

30 for the minutes we want to subtract,

00:03:37

comma, 0 for the seconds, a closing parenthesis, and press Enter.

00:03:44

And we have our calculated time of 8:30 am.

00:03:51

Now you've got a pretty good idea about how to add and subtract time.

00:03:55

Of course there's always more to learn.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2494 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES