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.

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

4. 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 Enter. Drag the fill handle to the range 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 Enter. Drag the fill handle to the range 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 Enter. Drag the fill handle to the range 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 Enter.  Drag the fill handle to the range 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.

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

2. Rename the table PPData
3. In the Power Pivot editor, select the Add column, type the formula =FORMAT( TIME( HOUR( PPData[Appointment Time] ), MINUTE( PPData[Appointment Time] ) - 30, SECOND( PPData[Appointment Time] ) ), "hh:mm:ss" ), and press Enter.
4. Rename the column header Reporting Time

5. Select the Flattened PivotTable option in the PivotTable command.

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

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

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

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

5. Type the Column name Reporting Time in the New column name input box.
6. Type the formula = [Appointment Time] - #duration( 0, 0, 30, 0 ) in the formula box.
7. Press OK

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

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

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

1. Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
2. 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!