How to Subtract Military Time in Excel?

While working with Microsoft Excel, sometimes we need to subtract military time. We can subtract military time from one time to another time by applying the Subtract formula, MOD function, and so on. From our dataset, in this article, we’ll learn three quick and suitable ways to subtract military time in Excel with appropriate illustrations.

Military Time in Excel (Quick View)

When the time is measured into hours numbered, from one midnight to the next, the hours are numbered from one to twenty-four format (e.g., 0300 or 1300). Here is the military time conversion chart.

How to Subtract Military Time in Excel: 3 Suitable Ways

Let’s say, we have a dataset that contains the starting and the ending time of 10 employees of any organization in columns C, D, and B. We will subtract the Starting Time from the Ending Time. Here’s an overview of the dataset for today’s task.

1. Apply Subtraction for Subtracting Military Time in Excel

In this method, we will apply the subtraction formula to subtract military time in Excel. This is the easiest and most time-saving way to subtract military time. Let’s follow the instructions below to learn!

Step 1:

• First, select cell E5 to subtract military time.

• Hence, write down the below formula in the Formula Bar. The formula is,

• Where D5 is the ending time, and C5 is the starting time of duties of the employees.

• After that, simply press Enter on your keyboard, and you will get 7:00 AM as the return of the subtraction formula.

Step 2:

• Further, Auto Fill the subtract formula to the entire column, and you will get the output of the subtract formula that has been given in the below screenshot.

Step 3:

• Now, look at our dataset, you will see that the formula returns military time with AM. We will convert these times into military time. To do that, from your Home tab, go to,

Home → Number → More Number Formats

• After clicking on the More Number Formats option, a window named Format Cells will appear in front of you. From the Format Cells window, firstly, select Number. Secondly, select Time from the Category Thirdly, select 37:30:55 from the Type box. At last press OK.

• Finally, after completing the above process, you will get the military time of the employees.

Read More: How to Subtract 30 Minutes from a Time in Excel

2. Use the MOD Function for Subtracting Military Time in Excel

To calculate military time, we will use the MOD function in Excel. Undoubtedly, this is the time-saving function to subtract military time. Let’s follow the steps below to learn!

Step 1:

• First of all, select cell E5 to apply the MOD function.

• After that, type the MOD function in the Formula Bar. The formula is,

• Where D5-C5 is the time difference and 1 is the divisor.

• Hence, simply press Enter on your keyboard, and you will get 7:00:00 as the return of the MOD function in cell E5.

Step 2:

• Further, place your cursor on the bottom-right of cell E5, and an autoFill sign will pop up.

• Finally, drag the autoFill sign downward, and you will get your desired output by using the MOD function that has been given in the below screenshot.

Read More: How to Subtract Minutes from Time in Excel

3. Perform the Custom Format Command for Subtracting Military Time in Excel

We’ll convert civilian time into military time by applying the custom format. Please follow the instructions below to learn!

Step 1:

• First, select cells from E5 to E14, and then press Ctrl + C on your keyboard.

• After that, select cell F5, press right-click on your mouse, and instantly a window pops up. From that window select Values from Paste Options.

• After pasting the values in column F from column E, you will get fraction values.

Step 2:

• Hence, we’ll convert the fraction into military time. To do that, press right-click on your mouse. A dialog box will appear in front of you. From that dialog box select Format Cells.

• Hence, a window named Format Cells will instantly pop up. From the Format Cells window, firstly, select Number. Secondly, select Custom from the Category Thirdly, select “hhmm” from the Type box. At last press OK.

Step 3:

• After completing the above process, you will be able to convert time into military time which has been given below screenshot.

Read More: How to Subtract Hours from Time in Excel

Things to Remember

👉 You can press Ctrl + 1 simultaneously to pop up the Format Cells window instead of the Home ribbon.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

I hope all of the suitable methods mentioned above to subtract military time will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Calculate Difference Between Two Times in Excel
• How to Calculate Time Difference in Numbers
• How to Calculate Time Difference in Minutes in Excel
• [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
• How to Subtract and Display Negative Time in Excel
• How to Subtract Time and Convert to Number in Excel
• How to Subtract Date and Time in Excel

<< Go Back to Subtract Time | Calculate Time | Date-Time in Excel | Learn Excel