Forum Discussion
Separating Date & Time for Function
I have a report which combines the date with a time. I actually need to organize and work with the data associated with these combined fields based upon the time. I need to create some functions which are based upon the times, but with the date and time combined in the cell, I am unable to write a formula which looks at the time since it is combined with the date (Example: 1/5/2020 4:30:46 PM). I have a lot of data associated with these dates/times on each row that I need to manipulate dependent upon the times.
I have 2 questions:
- Is it possible to separate the date from time?
- Is it possible to have a formula only look at the time in this combined field for a formula?
Hello, dsmith5740
In addition, you can use Power Query to Separate the Date & Time
Click anywhere in the data and execute CTRL + T
Check my table has headers and click OK
In the Get & Transform, click on From Table
In the Power Query editor, click on Split Column in the Home tab
Select By Delimiter
Select Space from the drop-down
Check Left-most delimiter
Click OK. From the caption below, we have the date and time splited into two columns
Then, you can choose Close & Load to load the result back to excel
4 Replies
Hello, dsmith5740
In addition, you can use Power Query to Separate the Date & Time
Click anywhere in the data and execute CTRL + T
Check my table has headers and click OK
In the Get & Transform, click on From Table
In the Power Query editor, click on Split Column in the Home tab
Select By Delimiter
Select Space from the drop-down
Check Left-most delimiter
Click OK. From the caption below, we have the date and time splited into two columns
Then, you can choose Close & Load to load the result back to excel
If you need only date from datetime, that's
=INT(A1)if only time
=MOD(A1,1)In general date in Excel are sequential numbers starting from 01 Jan 1900 (on Windows), and the time is decimal part of the numbers.
SergeiBaklan thanks for these 2 formulas to identify both time and date. Very helpful
dsmith5740 , glad to help
