Table of Contents
- INTRODUCTION
- WHAT IS MILITARY TIME
- MILITARY TIME IN EXCEL
- ONE SHORTCUT METHOD TO CONVERT MILITARY TIME TO STANDARD TIME:
INTRODUCTION
DATES and TIME , if you are new to Excel and you have already tried using those format, I am pretty sure that you must have felt panicky at some sort of time.
It is quite obvious because when we don’t know the exact working of any system or process, we try to use it just by hit and trial method. It works sometimes and sometimes not.
Here we are talking about Dates and Time in Excel. These are the tricky formats which we need frequently in our reports or charts.
Many times, we need to do operations on them.
We need to put them in the conditions to trigger some event which makes it very important for us to learn the exact procedures to perform a task concerned with the dates and time.
In this article we would learn different tricks and methods to handle and manipulate Dates and Time formats so that they don’t mess up with our reports.
In this article we would learn about the different ways to use DATE AND TIME to solve our day to day problems.
BEFORE READING THIS ARTICLE , IT IS REQUESTED TO VISIT THE PART I WHICH DISCUSSES THE CONCEPT OF THE DATE AND TIME IN EXCEL FOR BETTER UNDERSTANDING.
In this article we would learn about the MILITARY TIME and its conversion from normal time to military time and military time to normal time.
`
WHAT IS MILITARY TIME
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 WITH THE COLON [:] MISSING BETWEEN THE HRS AND MINUTES.
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.
MILITARY TIME IN EXCEL
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.
STEPS TO CONVERT STANDARD TIME TO MILITARY TIME.
- Enter the standard time in a cell.
- Select the cell where we want to calculate the military time.
- Enter the formula as =TEXT(E15,”HHMM”)
CONVERT MILITARY TIME TO STANDARD TIME
We have the following challenges before we convert the military time to standard time in Excel
- The military time is not recognized by EXCEL 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.
- Enter the formula as =TIMEVALUE(LEFT(TEXT(E21,”0000″),2)&”:”&RIGHT(TEXT(E21,”0000″),2))
- Convert the format to time using the w2qw
EXPLANATION OF THE FORMULA: CONVERSION OF MILITARY TIME TO STANDARD 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 Excel for example 630,800 etc.
ONE SHORTCUT METHOD TO CONVERT MILITARY TIME TO STANDARD TIME:
We can directly convert the military time to standard time by using the TEXT FUNCTION as
=TIMEVALUE(TEXT(CELL CONTAINING THE MILITARY TIME,”00\:00″))
It’ll give us the output as Timevalue which can be converted into standard time format using the format change.
These are the ways by which we can convert standard time to military time and military time to standard time.