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 find out the way to convert the time to decimal. The time is in the decimal format, which is the real way the Excel handles it.
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.
CONVERT THE TIME TO DECIMAL
While performing the calculations what if we need the decimal equivalent of any time. Yes, it is quite easy.Let us find out a few ways to get the decimal equivalent of any time.
We will discuss two ways to calculate the decimal part.
1. USING FORMAT CELLS
2. MANUAL WAY TO FIND OUT THE DECIMAL EQUIVALENT TO TIME.
USING FORMAT CELLS
We can find out the decimal equivalent simply converting the format from TIME to GENERAL or CUSTOM.
STEPS TO CONVERT TIME TO DECIMAL:
- Place the cursor in any cell and enter the time in any of the format acceptable to Excel.
- Check if the format of the cell in the home page changed to CUSTOM or TIME. If not changed, it means Excel doesn’t recognize the format you entered.
- RIGHT CLICK the cell and go to FORMAT CELLS and choose GENERAL NUMBER TYPE and click OK.
- Or go to HOME TAB and choose GENERAL NUMBER TYPE.
- The value in the cell will convert to the decimal number which the decimal equivalent of the time which we entered.
- Look at the picture below.
- We can see that the cell value is converted into decimal number which is the equivalent for 10 am as per our example.
MANUALLY FINDING THE DECIMAL EQUIVALENT OF TIME.
It is always easy if we know the concept behind any logic.
Let us try to find out the decimal equivalent of the time in excel without opening the excel.
We had already introduced the concept in our first article MANIPULATING DATE AND TIME PART -I about the same.
Let us revisit this.
The time is calculated as the nth part of the complete 24 hrs.
We have 24x24x60 seconds in a day. So our smallest unit which is a second equals to 1/86400=0.00001157
and one minute is equal to =1/(24×60)=0.0006944One hour is equal to =1/24=0.041666….=0.041667
Steps to find out the decimal equivalent manually:
- open CALCULATOR in windows or manual calculator.
- Find out the total hours, minutes and seconds from the 12 AM midnight.
- Our example is 10 AM which means 10 hours 00 minutes and 00 seconds from the midnight.
- Multiply the hours with the hour factor [derived above 0.041667], minutes with the minute factor [0.0006944] and seconds with the seconds factor [0.00001157].
- The result comes out to be 10*0.041667 =0.41667.
- Let us enter it into the cell and change the format to time and check the result.