Menu

Before we start working upon the MILITARY TIME, it is important to understand what it is.

MILITARY TIME is a very old time format, which of course is not as popular as our standard 12 hrs time format.

MILITARY TIME IS LIKE 24 HRS TIME FORMAT WITHOUT THE COLON [:] BETWEEN THE HRS AND MINUTES. FOR EXAMPLES, 1215, 1645 etc.

The origin of military time has been found way back to 2100 BC found through the archaeological evidences in the Egypt.

The military time is not very popular in day to day and normal jobs but there are many areas where we prefer using this time. For example

- MILITARY
- HEALTH CARE PROFESSIONALS
- SCIENTIST
- ASTRONAUTS
- FIREFIGHTERS AND COPS
- PILOTS

MILITARY TIME is expressed as the 24 hour time but without the colon [:]. There is no separator between the hours and minutes.

For example 1 AM as 0100 and 10:30 pm AS 2230. So the time ranges from 0000 to 2359.

Let us find out the conversion of NORMAL TIME to MILITARY TIME and MILITARY TIME to NORMAL TIME.

**CONVERT STANDARD TIME TO MILITARY TIME:**

We can convert it simply using the **TEXT FUNCTION**.

- Enter the standard time in a cell.
- Select the cell where we want to calculate the military time.
- For our example, the standard time is stored in E15. So the formula used is
- Enter the formula as =TEXT(E15,”HHMM”)

**We have the following challenges before we convert the military time to standard time in GOOGLE SHEETS**

- The military time is not recognized by
*GOOGLE SHEETS*and it is just a text. - We need to put the military text in the time format and convert the text into time.
- We can change the format using the left and right function and can convert it to time using time value function.

**STEPS TO CONVERT MILITARY TIME TO STANDARD TIME.**

- Enter the MILITARY time in a cell.
- Select the cell where we want to calculate the STANDARD TIME.
- For our example, the military time is store in cell E21. So the formula used is
- Enter the formula as =TIMEVALUE(LEFT(TEXT(E21,”0000″),2)&”:”&RIGHT(TEXT(E21,”0000″),2))
- Convert the format to time using the MORE FORMATS on the TOOLBAR and then choosing TIME.

- We can also change the format to time by going to FORMAT MENU > NUMBER > TIME.

The formula used is **=TIMEVALUE(LEFT(TEXT(E21,”0000″),2)&”:”&RIGHT(TEXT(E21,”0000″),2))**

The outer function is the TIMEVALUE and it takes the text as time.

We pick the left two digits with the help of LEFT FUNCTION and right two digits with the RIGHT FUNCTION.

We used **TEXT FUNCTION** to make the time forcibly contain the 4 digits as if the time is before 10 am it will be shown as 3 digits in *GOOGLE SHEETS* for example 630,800 etc.

We can directly convert the military time to standard time by using the TEXT FUNCTION as

For our example, the military time is stored in cell E31, so the formula becomes

The formula for our example is **=TIMEVALUE(TEXT(E31,”00\”00″))**

- Converting the format to time using the MORE FORMATS on the TOOLBAR and then choosing TIME.

- We can also change the format to time by going to FORMAT MENU > NUMBER > TIME.

It’ll give us the output as Timevalue which can be converted into standard time format using the format change.

OTHER WAYS TO REACH THIS ARTICLE

## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.