Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-16-2010, 12:19 PM
FraserKitchell FraserKitchell is offline Time as text conversion problems Windows XP Time as text conversion problems Office 2007
Novice
Time as text conversion problems
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default Time as text conversion problems


Hi there,

I am having difficulty converting a time text string to a time format. Currently the time stamp is as follows:

2/8/10 11:00:00 AM PST
2/8/10 11:05:00 AM PST

I would like the data to be in two columns, the first a date only

2/8/10

and the second a full timestamp

11:00:00 AM (formatted for time, further functions to be driven off this time)

Problem is I cant make a few things happen: 1) data to columns with a space delimiter is great, but I cannot get the AM back into the formula and get to a correct timestamp.
2) I am unable to us a replace, concatentate, && type formulas it seems, Although promising, I also cant figure out a timevalue, etc type formula.

Let me know if you can think of something to work, happy solving,
Fraser
Reply With Quote
  #2  
Old 02-17-2010, 08:50 PM
zyzzyva57 zyzzyva57 is offline Time as text conversion problems Windows 7 Time as text conversion problems Office 2007
Expert
 
Join Date: Mar 2009
Location: Dawsonville, Ga (NE of Atl)
Posts: 355
zyzzyva57 is on a distinguished road
Default

Select the Cell
Then under Data Tab, in the Data Group, select "Text to Columns" and then follow the Wizard
Reply With Quote
  #3  
Old 02-19-2010, 10:56 AM
FraserKitchell FraserKitchell is offline Time as text conversion problems Windows XP Time as text conversion problems Office 2007
Novice
Time as text conversion problems
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

The fact that splitting this into columns isnt working for this particular time stamp is the problem. I've tried doing using that capability in many ways and none will quite do the job. Problem arises because I have to split on a space delimiter (can't do fixed widths).

Basically I need a way to convert a text date to a number.
Reply With Quote
  #4  
Old 02-21-2010, 07:25 AM
BjornS BjornS is offline Time as text conversion problems Windows Vista Time as text conversion problems Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

Hi there!
Here is a "corrected" solution, that i hope works.
Please check it out!

Note that there are three worksheets in the file!

Kind regards
Bjorn
Attached Files
File Type: xls DateTime NEW.xls (28.5 KB, 6 views)
Reply With Quote
  #5  
Old 02-25-2010, 04:34 PM
FraserKitchell FraserKitchell is offline Time as text conversion problems Windows XP Time as text conversion problems Office 2007
Novice
Time as text conversion problems
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

Wow three great solutions...this works! Thanks Bjorn, I appreciate that.
Reply With Quote
Reply

Tags
text, time

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Anyway to determine time/date of text creation? pureride Word 1 01-05-2010 02:09 PM
Time as text conversion problems Measurement Conversion Tables bhalliday Excel 1 05-10-2009 11:20 PM
Set text in edit control on inspector window indicating time Outlook 2007 saniltalathi Outlook 0 02-20-2009 08:43 AM
Time as text conversion problems Auto Shape Conversion Launchnet Excel 1 04-18-2006 06:25 AM
Time as text conversion problems Specific Lat Long Conversion Help chesspupil Excel 2 04-16-2006 11:06 PM


All times are GMT -7. The time now is 01:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft