How to Subtract Military Time in Excel (3 Methods)

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 for subtracting 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

Download Practice Workbook

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


3 Suitable Ways for Subtracting Military Time in Excel

Let’s say, we have a dataset that contains the starting and the ending time of 10 several employees of the Armani group in columns C, D, and B. We will subtract 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, autoFill 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

Related Content: How to Subtract Date and Time in Excel (6 Easy Ways)


Similar Readings


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:

Use the MOD Function to Subtract Military Time in Excel

  • After that, type the MOD function in the Formula Bar. The MOD function 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 on 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

Related Content: How to Subtract and Display Negative Time in Excel (3 Methods)


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 Time in Excel (7 Quick Methods)


Things to Remember

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


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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

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

ExcelDemy
Logo