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 learnt about
- How date works in Excel
- How time is treated by excel
- How to perform many types of frequently used operations directly on the date and time formats.
COMBINE DATE AND TIME IN EXCEL
A certain situation may arise when we need to combine the separately put DATE and TIME in excel. For this, we can again use very simple steps.
CONCEPT:
If we remind the basics of DATE AND TIME , we know that both are handled together by Excel.The integral part of the number is the date and decimal part of the same is time for Excel.
The format in which we see the date and time are just the format. While calculating, Excel treats the date as an integer and time as a decimal portion of the same.For example Jan,1,2020 , if represented as the DATE SERIAL evaluates to 43831.
Similarly 12 PM [noon] evaluates to 0.5.
Now if we simply sum the Date and Time, our problem is solved. As the sum won’t interfere with each other.
STEPS TO COMBINE DATE AND TIME IN EXCEL
- The solution is very easy. As we know the date is a number and time is the fractional part associated with the same number.
- Enter the following formula in third cell “=cell containing the date+cell containing the time” [“” not included].
- Convert the number format to DATE if needed.
EXPLANATION:
In the example, we have a date given in the cell J34 and time in the cell K35.
We try to find the combined date and time in the cell L35.
The formula used is =CELL CONTAINING DATE +CELL CONTAINING TIME
It’ll result in the combined date and time.
In our example date is 26 june 2020 and time is 3:30 pm.
the combined result is 26-06-2020 15:30 which is as expected.
We can change the format using TEXT FUNCTION easily.
