SOLVED

Separating Date & Time for Function

%3CLINGO-SUB%20id%3D%22lingo-sub-1098789%22%20slang%3D%22en-US%22%3ESeparating%20Date%20%26amp%3B%20Time%20for%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098789%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20report%20which%20combines%20the%20date%20with%20a%20time.%20I%20actually%20need%20to%20organize%20and%20work%20with%20the%20data%20associated%20with%20these%20combined%20fields%20based%20upon%20the%20time.%20I%20need%20to%20create%20some%20functions%20which%20are%20based%20upon%20the%20times%2C%20but%20with%20the%20date%20and%20time%20combined%20in%20the%20cell%2C%20I%20am%20unable%20to%20write%20a%20formula%20which%20looks%20at%20the%20time%20since%20it%20is%20combined%20with%20the%20date%20(Example%3A%201%2F5%2F2020%204%3A30%3A46%20PM).%20I%20have%20a%20lot%20of%20data%20associated%20with%20these%20dates%2Ftimes%20on%20each%20row%20that%20I%20need%20to%20manipulate%20dependent%20upon%20the%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%202%20questions%3A%3C%2FP%3E%3CP%3E-%20Is%20it%20possible%20to%20separate%20the%20date%20from%20time%3F%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Is%20it%20possible%20to%20have%20a%20formula%20only%20look%20at%20the%20time%20in%20this%20combined%20field%20for%20a%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1098789%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1098803%22%20slang%3D%22en-US%22%3ERe%3A%20Separating%20Date%20%26amp%3B%20Time%20for%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098803%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511866%22%20target%3D%22_blank%22%3E%40dsmith5740%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20need%20only%20date%20from%20datetime%2C%20that's%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINT(A1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20only%20time%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMOD(A1%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20general%20date%20in%20Excel%20are%20sequential%20numbers%20starting%20from%2001%20Jan%201900%20(on%20Windows)%2C%20and%20the%20time%20is%20decimal%20part%20of%20the%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1098953%22%20slang%3D%22en-US%22%3ERe%3A%20Separating%20Date%20%26amp%3B%20Time%20for%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098953%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511866%22%20target%3D%22_blank%22%3E%40dsmith5740%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20addition%2C%20you%20can%20use%20Power%20Query%20to%20Separate%20the%20Date%20%26amp%3B%20Time%3C%2FP%3E%3CP%3EClick%20anywhere%20in%20the%20data%20and%20execute%20CTRL%20%2B%20T%3C%2FP%3E%3CP%3ECheck%26nbsp%3B%3CSTRONG%3Emy%20table%20has%20headers%26nbsp%3B%3C%2FSTRONG%3Eand%20click%20OK%3C%2FP%3E%3CP%3EIn%20the%26nbsp%3B%3CSTRONG%3EGet%20%26amp%3B%20Transform%2C%26nbsp%3B%3C%2FSTRONG%3Eclick%20on%26nbsp%3B%3CSTRONG%3EFrom%20Table%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIn%20the%20Power%20Query%20editor%2C%20click%20on%26nbsp%3B%3CSTRONG%3ESplit%20Column%26nbsp%3B%3C%2FSTRONG%3Ein%20the%26nbsp%3B%3CSTRONG%3EHome%26nbsp%3B%3C%2FSTRONG%3Etab%3C%2FP%3E%3CP%3ESelect%26nbsp%3B%3CSTRONG%3EBy%20Delimiter%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESelect%20%3CSTRONG%3ESpace%20%3C%2FSTRONG%3Efrom%20the%20drop-down%3C%2FP%3E%3CP%3ECheck%20%3CSTRONG%3ELeft-most%20delimiter%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F164493iF07562D396967765%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EClick%20OK.%20From%20the%20caption%20below%2C%20we%20have%20the%20date%20and%20time%20splited%20into%20two%20columns%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F164494i22A33CB3D818F49B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20you%20can%20choose%26nbsp%3B%3CSTRONG%3EClose%20%26amp%3B%20Load%26nbsp%3B%3C%2FSTRONG%3Eto%20load%20the%20result%20back%20to%20excel%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F164495iCEC8D5114C4DF293%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1851796%22%20slang%3D%22en-US%22%3ERe%3A%20Separating%20Date%20%26amp%3B%20Time%20for%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1851796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20thanks%20for%20these%202%20formulas%20to%20identify%20both%20time%20and%20date.%20Very%20helpful%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1852128%22%20slang%3D%22en-US%22%3ERe%3A%20Separating%20Date%20%26amp%3B%20Time%20for%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1852128%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511866%22%20target%3D%22_blank%22%3E%40dsmith5740%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

4 Replies

@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.

Best Response confirmed by dsmith5740 (New Contributor)
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

clipboard_image_0.png

Click OK. From the caption below, we have the date and time splited into two columns

 

clipboard_image_1.png

 

Then, you can choose Close & Load to load the result back to excel

clipboard_image_2.png

 

 

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

@dsmith5740 , glad to help