LinkBack URL
About LinkBacks
Hello, does anyone know how to separate Date and time? I have a table that comes with a cell that has date and time together. I would like to separate them with some kind of "=()" code.
Hello, does anyone know how to separate Date and time? I have a table that comes with a cell that has date and time together. I would like to separate them with some kind of "=()" code.
Simple!
In B2:
=A2 (column formatting: short date)
In C2:
=A2 (column formatting: time)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
It works on the table but it does not work in the pivot table. It keeps coming back as date. Is there a = code that I can do it with out formatting the cell?
OK, so you never mentioned a pivot table, nor is there one in your sample workbook! I am not psychic ...
Try attaching a properly representative sample workbook - give us a fighting chance.
Last edited by AliGW; 10-20-2019 at 04:40 AM.
Dates are always whole numbers and times are the decimalparts of the datenumber
So you can use
In B2: =INT(A2) For the date
In C2: =MOD(A2,1) For the time
You still need to format the cells as date onky for col B and time only for col c but due to the formulas the pivottable no should interpret the colums formats correctly and show as date and time respectively.
Then again it is also posibble to format a pivottable manualy to show the correct date or time format.
Last edited by Roel Jongman; 10-20-2019 at 05:53 AM.
It worked. Thanks a lot!
Last edited by AliGW; 10-20-2019 at 12:02 PM. Reason: Please don't quote unnecessarily!
Thank you for trying. If you want I can upload the workbook how it worked. In Mac OS pivot table automatically separates the date and time, but on my windows excel version it does not. I'm assuming it has something to do with pay per month Office excel which usually has many updates.Originally Posted by AliGW
Last edited by AliGW; 10-20-2019 at 12:03 PM. Reason: Please don't quote unnecessarily!
It won't have anything at all to do with the Office 365 subscription and its updates.
If you'd like to upload the workbook, one of us can take a look.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks