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.
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!
- 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.
- 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.
- 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.
Related Content: How to Subtract Date and Time in Excel (6 Easy Ways)
- How to Add Time in Excel Over 24 Hours (4 ways)
- Excel Formula To Calculate Time Worked
- How to Add Hours to Time in Excel (8 Quick Ways)
- Calculate Average Response Time in Excel (4 Methods)
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!
- First of all, select cell E5 to apply the MOD function.
- After that, type the MOD function in the Formula Bar. The MOD function 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.
- 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.
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!
- 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.
- 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.
- After completing the above process, you will be able to convert time into military time which has been given below screenshot.
Things to Remember
👉 You can press Ctrl + 1 simultaneously to pop up the Format Cells window instead of the Home ribbon.
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.
- How to Calculate Turnaround Time in Excel (4 Ways)
- Calculate Hourly Rate in Excel (2 Quick Methods)
- Elapsed Time Between Two Dates in Excel (5 Methods)
- How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)
- Excel Formula for Overtime over 8 Hours (4 Examples)
- How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)
- How to Calculate the Duration of Time in Excel (7 Methods)