Jan 09 2020 05:27 AM
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?
Jan 09 2020 05:31 AM
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.
Jan 09 2020 06:36 AM - edited Jan 09 2020 06:41 AM
SolutionHello, @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
Nov 03 2020 08:09 PM
@Sergei Baklan thanks for these 2 formulas to identify both time and date. Very helpful
Jan 09 2020 06:36 AM - edited Jan 09 2020 06:41 AM
SolutionHello, @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