How to Subtract Military Time in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Standard Time Military Time Standard Time Military Time
12:00 AM / Mid Night 0000 / 2400 12:00 PM / Noon 1200
1:00 AM 0100 1:00 PM 1300
2:00 AM 0200 2:00 PM 1400
3:00 AM 0300 3:00 PM 1500
4:00 AM 0400 4:00 PM 1600
5:00 AM 0500 5:00 PM 1700
6:00 AM 0600 6:00 PM 1800
7:00 AM 0700 7:00 PM 1900
8:00 AM 0800 8:00 PM 2000
9:00 AM 0900 9:00 PM 2100
10:00 AM 1000 10:00 PM 2200
11:00 AM 1100 11:00 PM 2300

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.

Subtracting Military Time in Excel


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.

Apply Subtraction to Subtract Military Time in Excel

  • Hence, write down the below formula in the Formula Bar. The formula is,
=D5-C5
  • Where D5 is the ending time, and C5 is the starting time of duties of the employees.

Apply Subtraction to Subtract Military Time in Excel

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

Apply Subtraction to Subtract Military Time in Excel

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.

Apply Subtraction to Subtract Military Time in Excel

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.

Use the MOD Function to Subtract Military Time in Excel

  • After that, type the MOD function in the Formula Bar. The formula is,
=MOD(D5-C5,1)
  • Where D5-C5 is the time difference and 1 is the divisor.

Use the MOD Function to Subtract Military Time in Excel

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

Use the MOD Function to Subtract Military Time in Excel

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.

Use the MOD Function to Subtract Military Time in Excel

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.

Perform the Custom Format Command to Subtract Military Time in Excel

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

Perform the Custom Format Command to Subtract Military Time in Excel

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.

Perform the Custom Format Command to Subtract Military Time in Excel

Step 3:

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

Perform the Custom Format Command to Subtract Military Time in Excel

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

2 Comments
  1. If you are going to do military time don’t use AM / PM, did not help my issue of getting ###### in the calculation field.

  2. Reply Avatar photo
    Md. Abdur Rahim Rasel Aug 29, 2022 at 9:10 AM

    Hello MARK,
    To solve your ###### issue in the calculation field simply AutoFit the column width. You can AutoFit the column width like below screenshot.

    Thank you for your comment.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo