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.
One of the requirement is Separating the date and time in Excel.
In this article we would learn about
HOW TO SEPARATE DATE AND TIME IN EXCEL
SEPARATE DATE AND TIME IN EXCEL
Just like we combined the date and time, a need can arise to separate them as well. Let us take an example where we have a long date i.e. date and time combined. We’ll separate it.
CONCEPT:
The easy way to separate the date and time is by using the TEXT FUNCTION. Although we can think of subtracting the Date from the combined date, if available, which will also give us the Time, and subtracting the time would give us the Date. But if none is available using the TEXT FUNCTION is best. Text function helps us to format the data as per choice.
[CLICK HERE TO LEARN TEXT FUNCTION]
THIS CAN BE SEPARATED WITH THE HELP OF TEXT FUNCTION. The combined date is present in the cell.
STEPS TO SEPARATE THE DATE AND TIME
- The combined date and time are put in cell J41. The Date is to be extracted in K41 and TIME in M41.
- Enter the formula in K41 =TEXT(J42,”DD-MM-YYYY”) . It’ll extract the date in the mentioned format i.e. dd mm yyyy.
- Enter the formula for time in M41 as =TEXT(J42,”HH:MM”). It’ll extract the time in the mentioned format i.e. hh:mm. We can also modify it to hh:mm:ss. [Try it yourself]
EXPLANATION:
We have made use of the TEXT FUNCTION to separate the date and time.
As we know that TEXT FUNCTION helps us in changing the format of the data very easily, we applied the same here.
Cell J42 contains the combined date and time.
In cell K42, we put the function as
=TEXT(J42,”DD-MM-YYYY”), which will convert the format of the given date to the format mentioned in the function.
It’ll drop the time portion.
Similarly, to separate the Time, we used the formula as
=TEXT(J42,”HH:MM”) which will result in the time.
The result shows 22:37 which is the time portion in the given combined date and time.