Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Separating date and time in Excel Office 2007

12 Dec 2012   #1
rcd1947

Windows 7 64 bit
 
 
Separating date and time in Excel Office 2007

I have Office 2007 running on a Windows 7 64 bit Toshiba laptop. I am trying to separate the data and time in an Excel file in one column and put the date in one column and the time in another. I need a "how to" procedure to do that.


My System SpecsSystem Spec
.

12 Dec 2012   #2
DavidE

Win 7 Pro x64 SP1, Win 7 Ult x86 SP1
 
 

Excel has formatting for Date and Time.
For column A use a Date format, and for column B use a Time format.
To copy the value from column A to column B a function in column B like this should work
= IF(A1="","",A1)
My System SpecsSystem Spec
13 Dec 2012   #3
rcd1947

Windows 7 64 bit
 
 

I tried that, but all the elements of the date and time are still there and that results in just what is seen in the viewing. In one column I want only the date data, and in the other column only the time data so those two different elements can be used separately in a variable data program.
My System SpecsSystem Spec
.


13 Dec 2012   #4
DavidE

Win 7 Pro x64 SP1, Win 7 Ult x86 SP1
 
 

Yes, formatting only affects the displayed data.

To get the date, add a column with this formula
=IF(A1="","",TEXT(A1,"mm/dd/yyyy"))

To get the time, add a column with this formula (remove the am/pm if you want 24hr fmt)
=IF(A1="","", TEXT(A1,"hh:mm:ss am/pm"))
My System SpecsSystem Spec
20 Dec 2012   #5
rcd1947

Windows 7 64 bit
 
 

Thanks, DavidW7ncus, that works great on my laptop with Windows 7. I appreciate the help very much. Now if I can also figure out how to do it on a Mac OS X I will have both computers covered.
My System SpecsSystem Spec
20 Dec 2012   #6
DavidE

Win 7 Pro x64 SP1, Win 7 Ult x86 SP1
 
 

Hello rcd1947,

You're welcome and thanks for posting back.

I don't have a Mac, so i don't know that can help with that.
What happens on the Mac?
Is the formula invalid, not work correctly, ... ?

If adding a screen print showing the problem is easier/better, this explains how to to this (Windows instructions).
Screenshots and Files - Upload and Post in Seven Forums
My System SpecsSystem Spec
Reply

 Separating date and time in Excel Office 2007




Thread Tools





Similar help and support threads
Thread Forum
Error on saving MS Office Excel 2007 files to MS Office 2013
We have an upgrade on our computer units here in the company, my problem is when users save their 2007 excel files an error pops up, Errors were detected while saving "File Name". Microsoft maybe able to save the file by removing or repairing some features. To make the repairs in new file, click...
Microsoft Office
Excel 2007 (MS Office 2007 enterprise)
I recently formatted my HD and reinstalled MS Office Enterprise 2007, I last used MS Excel on the 08/06/2014 without a problem, today I go to use my Excel spreadsheet and for some weird reason it wants to roundup my cells. I have cleared the cells formatted the number but it still keeps on...
Microsoft Office
Microsoft Office 2007 Excel Find and Replace Command
I know I can do this as I have done it in the past a long time ago. Using the Find and Replace option add and extension to my sku numbers, such as: SKU # 8686708052 I have a column of these sku's and want to add the extension .jpg to all of them at one time! 8686708052.jpg
Microsoft Office
Excel 2007 Date Factor
I have a spreadsheet of inventory items and the dates are in various formats Europe , USA , and Eastern. Example below. What I need to do is select all the dates and covert into one recognizable formula Month (1) Day (1) Year (****). Let me know how to do this. Thanks! 7/12/00 28/12/00...
Microsoft Office
Office 2007 Excel File Association
Hello In the document library if I click on an word document the document opens in word. If I click on an excel document it opens in notepad. If I open Excel and drill down to the libary document excel file the document will open in Excel. I looked at the document library excel files and...
Microsoft Office
Excel 2007: a question about date and time manipulation
I have pasted data in to Excel that is in two columns Col A contains a list of dates, dd/mm/yy; col B times hh/mm/yy. How do I set about calculating and displaying the period in dd/hh/mm/ss that elapsed between col A row B with col B row B, and col A row C with col B row C ?
Software

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 00:43.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App