Hello, Hello everyone, today I will share with you how we can quickly separate time and date in Excel. Many people encounter such problems, copy and paste one by one, which is very time-consuming. Today I will share with you three methods that can be easily done. Let’s operate them together.
1. Using the formula
First of all, we need to understand: date and time are essentially numerical values, but date is an integer value, and time is a decimal value (not greater than 1).
For the data input by mixing date and time, first we can use the int function to round down, and only retain the integer part. In this way, We will get the date, and then we use the mixed input data to subtract the date to get the time
date formula: =INT(A3)
time formula: =A3-B3
2. Use quick fill (shortcut key: Ctrl+E)
quick fill can automatically fill the data downward according to the data we entered, but version support is required, Excel minimum 2013, wps minimum 2019
We only need to copy the date and time in the corresponding position, and then press the shortcut key Ctrl+E to quickly extract the
3. Column
We can see that there are spaces in the data input mixed with date and time, so we can use the function of column to split them
First we copy a copy of the data next to it, then click the [Data] function group, find the [Classification] and then select the [Delimiting Symbol] to separate the column, then click [Next] to set the split symbol to space, and then click OK. In this way, the date and time are separated. Finally, we select the data of the date and set its format to a short date
4. Custom cell Formula
uses custom cell format, and I can also achieve the effect of splitting dates and time. What you need to note here is that the data is still the data we copied, but it is just displayed in this way
1. Extract date
First, we copy the data next to it, and then set the format to short date
2. Extract time
Similarly, copy the data next to it, and then press ctrl+1 to call up the format window. Set the format to: h:mm and click OK
above are the 4 methods shared today. How about it? Have you learned it?
I am Excel from zero to one, follow me and continue to share more Excel skills
(A circle card has been added here, please go to Today's Headlines client to view)
