INTRODUCTION
DATES and TIME , if you are new to GOOGLE SHEETS 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 GOOGLE SHEETS. These are the tricky formats which we need frequently in our reports or charts.
Many times, we need to perform 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. 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 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.
MILITARY TIME IN GOOGLE SHEETS
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.
- For our example, the standard time is stored in E15. So the formula used is
- 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 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.
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 GOOGLE SHEETS for example 630,800 etc.
ANOTHER 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″))
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″))Change the format of the cell by
- 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.
