Separating Date & Time for Function

[dsmith5740]

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?

[#2]

@dsmith5740 

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.

[#3 · Accepted solution]

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]

@Sergei Baklan  thanks for these 2 formulas to identify both time and date. Very helpful

[#5]

@dsmith5740 , glad to help