# How to Subtract 30 Minutes from a Time in Excel: 8 Best Ways

Do you need to subtract minutes from a time in Microsoft Excel? This blog post covers how to subtract minutes from a time with arithmetic operators, excel functions, VBA, and Office Scripts.

You would subtract the start date from the end date to get the total number of days, hours, or minutes that have occurred from the beginning and finish of a project.

You may need to remind your employee to pop up a message box 30 minutes before the meeting starts. If your company has a grace period of 30 minutes for the employee sign-off, you subtract 30 minutes from the employee’s actual sign-off time for your analysis.

Excel uses serial numbers to store dates and timings **ddddd.tttttt** The number of days since January 1, 1900, plus a fraction for the current time. Time values **tttttt** decimal integers are a fractional portion of the date value.

In this article, you will learn 8 different methods to subtract minutes from a time in Excel.

It covers how to use the Excel command **Paste Special**, **Operators**, **TIME**, **TIMEVALUE**, and **TEXT** functions to subtract 30 minutes from a time. Exploring the **Power Query** data transformation tool, and **Power Pivot** to subtract minutes from a time. Understanding the **VBA** Code or **Office Scripts to work both desktop and web** for your automation to subtract 30 minutes from a time.

The data table has a field **Appointment Time. **You have to calculate the reporting time for the appointment and send a mail to the patient mail id mentioning the reporting time for the doctor’s appointment, which is 30 minutes before the **Appointment Time**.

Get your copy of the example workbook used in this blog post to follow along!

## Subtract 30 Minutes from a Time in Excel with **Paste Special**

**Paste Special** allows you to paste data from one area of a worksheet to another with more control than a standard paste. You can choose to paste only the values, formulas, formats, comments, or validation from the copied cells. Also you have the option to transpose data from rows to columns or vice versa in Paste Special.

Please follow the below steps to subtract 30 minutes from the **Appointment Time** using **Paste Special** Subtract operation.

- Type 30 minutes
**00:30:00**in cell**F2**, and copy. - Select the range of cells
**C2:C16.** - Choose
**Paste Special…**inthe**Paste**command, to open the Paste Special dialog box.

- Select
**Subtract**option in the operation section and then Press**OK.**

## Subtract 30 Minutes from a Time in Excel with **Operators**

**Operators** are used to performing a calculation. Excel offers a range of operators including arithmetic, comparison, text, and reference operators. In this section, you use basic arithmetic operators to subtract 30 minutes.

Go to cell **E2**, type the formula ** =C2 - ( 30/1440 )**, and Press

**. Drag the fill handle to the range**

`Enter`**E2:E16**to copy the formula.

30/1440 in the formula will give you the 30-minute serial number. One hour is equal to sixty minutes. To convert 1440 minutes to 30 minutes, you have to divide the number of minutes, 30 by 1440.

Alternatively, you can use the formula ** =C2 - "00:30"** to subtract 30 minutes from a time. Please refer the column

**F**.

## Subtract 30 Minutes from a Time in Excel with **TIME **function

The Excel **TIME** function allows you to add or subtract a specified number of hours, minutes, and seconds from a time. It helps you to work with time-based data such as calculating the duration of an event or the time between two events.

`TIME( hour, minute, second )`

**TIME** returns the decimal number for a particular time.

Go to cell **E2**, type the formula ** =C2 - TIME( 0, 30, 0 )**, and Press

**. Drag the fill handle to the range**

`Enter`**E2:E16**to copy the formula.

Type this alternate formula in cell **F2** ** =TIME( HOUR( C2 ), MINUTE( C2 ) - 30, SECOND( C2 ) )** to subtract 30 minutes from a time. Please refer the column

**F**.

## Subtract 30 Minutes from a Time in Excel with **TEXT **and** TIMEVALUE **function

In this section, you learn how to use **TEXT** and **TIMEVALUE** functions to subtract 30 minutes. You create one support field **Grace period** in column **E** and then use these two functions.

**TEXT** function

The **TEXT** function is a useful function, which can be used to convert numbers to text, format text, change the case of text, extract specific words from a text string and more.

**TEXT**( value, format_text )

Go to cell **F2**, type the formula ** =TEXT( C2, "h:mm:ss" ) - TEXT( E2, "h:mm:ss" )**, and Press

**. Drag the fill handle to the range**

`Enter`**F2:F16**to copy the formula.

**TIMEVALUE** function

**TIMEVALUE** function converts a text string representing a time into a numerical value. This function can be used to add and subtract time values, or to compare two different times.

`TIMEVALUE( time_text )`

**TIMEVALUE** returns a decimal number, it is ranging from 0 to 0.99988426.

Eg. TIMEVALUE( “06:00:00” ) returns 0.25 and TIMEVALUE( “12:00:00” ) returns 0.5.

Go to cell **G2**, type the formula ** =TIMEVALUE( C2 ) - TIMEVALUE( E2 )**, and Press

**. Drag the fill handle to the range**

`Enter`**G2:G16**to copy the formula.

## Subtract 30 Minutes from a Time in Excel with **Power Pivot**

**Power Pivot** is a powerful Excel add-in. It provides you to combine data from multiple sources, create relationships between data sets, and perform complex calculations and analyses. This makes it a great tool for data analysis and reporting.

- Select the range of cells
**A1:D16**and press the command**Add to Data Model**in the**Power Pivot**Tab.

- Rename the table
**PPData** - In the Power Pivot editor, select the
**Add column**, type the formulaand press`=FORMAT( TIME( HOUR( PPData[Appointment Time] ), MINUTE( PPData[Appointment Time] ) - 30, SECOND( PPData[Appointment Time] ) ), "hh:mm:ss" )`

,`Enter`. - Rename the column header
**Reporting Time**

- Select the
**Flattened PivotTable**option in the**PivotTable**command.

- You have options to select the New Worksheet or Existing worksheet and select the worksheet location. Once you press the
**OK**button, Excel adds an empty pivot table in the worksheet.

- You can just drag both the
**Doctor ID**and**Reporting Time**fields in the**Rows**Area and see the Output.

## Subtract 30 Minutes from a Time in Excel with **Power Query**

**Power Query** is a handy data-transformation tool that allows you to access and reshape data from multiple sources. you can use the Power Query editor to combine data from different sources, such as databases, tables, and text files. Additionally, Power Query can be used for data cleaning, creating reports and visualizations, and deriving insights from data.

Steps to subtract 30 minutes from a time using **Power Query**

- Select the range of cells
**A1:D16**in the**Data**Sheet - Choose
**From Table/Range**option in**Data**Tab – to open the Power Query window.

- Make sure the field
**Appointment Time**should be in Time Data Type. If not, change the data type to Time. - Select the
**Custom Column**command in**Add Column**Tab, to open the Custom column dialog box.

- Type the Column name
**Reporting Time**in the New column name input box. - Type the formula
in the formula box.`= [Appointment Time] - #duration( 0, 0, 30, 0 )`

- Press
**OK**

- Make sure the added column
**Reporting Time**should be in the**Time**Data Type.

- Select the
**Close & Load**command and Press**Close & Load To… –**to popup**Import Data**dialog box.

- In the
**Import Data**dialog box, select the**Table**option, and provide the location in the**Existing worksheet**to insert the transformed Power Query table into your worksheet.

## Subtract 30 Minutes from a Time in Excel with **VBA**

**VBA** in Excel allows you to record repetitive tasks, automate processes, create custom functions, user interfaces, customize your workflows and save time. You can edit and run VBA scripts in Visual Basic Editor. Alt + F11 is a keyboard shortcut to open VBE.

```
Sub ReportingTime()
'Declare variables
Dim rge As Range
'Assign values
Set rge = Sheets("VBA").Range("C2:C16")
'loop through each cell in the range
For Each rg In rge
'Subtract 30 minutes
ReportingTime = TimeValue(rg.Value) - TimeSerial(0, 30, 0)
' Format the Reporting Time
rg.Offset(0, 2).Value = Format(ReportingTime, "hh:mm:ss")
Next
End Sub
```

Add the above code to your **VBE **module.

Macro declares the variable and assigns the range **C2:C16** to the variable **rge**. Loop through the range of cells, the **TimeSerial** function converts **30** minutes to the equivalent serial number and subtracts it from the Appointment Time. Finally, The **ReportingTime** store in a respective cell in column **F**.

Steps to Run the **ReportingTime** macro.

- Press
+**Alt**, the keyboard shortcut to open the Macro dialog box.`F8` - Choose the
**ReportingTime**Macro and then Press**Run**.

## Subtract 30 Minutes from a Time in Excel with **Office Scripts**

**Office Scripts** in Excel helps you to automate repetitive tasks, such as data entry, formatting, calculations, and more. You can share the Office Scripts with your teams. It can be linked to the button click event.

In excel, the serial number **0.****0208333** is equivalent to **30** minutes. You can type the formula in excel cell **=MINUTE ( 0.0208333)** and check.

```
function main(workbook: ExcelScript.Workbook) {
// Assign worksheet and data range to variables
let ws = workbook.getWorksheet("Office Scripts")
let rng = ws.getRange("C2:C16")
let rows = rng.getRowCount();
// loop through the selected range of cells
for (let i = 0; i < rows; i++) {
let cellValue = rng.getCell(i, 0).getValue() as number
//subtract - 0.0208333, which is equivalent to 30 minutes
let reportingTime = cellValue - 0.02083333333333330;
rng.getCell(i, 2).setValue(reportingTime)
}
}
```

Office Scripts assigns the range **C2:C16** to the variable **rng**. Next, the scripts loop through each cell in the range, and assign the current cell value to the variable **cellValue. **Subtract **0.0208333,** which is 30 minutes, from the variable **cellValue. **Finally, store the reporting time in the respective cell in column F.

To execute the **Reporting Time** Office script in your excel. Go to **Automate **menu. Select the **Reporting Time** Script, to open the Code editor on the right side of your excel application. Press **Run **

## Conclusions

**TIME** is an important factor to consider performing arithmetic operations also you should consider units, such as minutes, seconds, or hours. Time is used to determine the trends and patterns in data and identify correlations between different events.

By understanding and using time operations, you will get important insights of your data and help you to take decisions.

If you like to work with the keyboard and it is a one-time operation in your data, you can use the **Paste Special** method. Excel **Operators** or **TIME** function formula helps you to subtract 30 minutes from a time by just dragging and applying the formula when you add more data to your data table.

You have added a support field **Grace Period** and used the **TEXT** and **TIMEVALUE** functions to do the subtraction. **Power Pivot** and **Power Query** methods work if the data are large. You can use **VBA** or **Offices Scripts** code to subtract 30 minutes from a time in your automation, dashboard, or user interface.

Do you know any other method to **Subtract 30 Minutes from a Time** in Excel? Let me know in the comments section!