Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel String Date/Time to numeric Date/Time


  • Please log in to reply

#1
jess22osu

jess22osu

    Member

  • Member
  • PipPip
  • 15 posts
Ok, first let me give a little background. I am working with giant excel spreadsheets and I am transferring them to SPSS to do data analysis for my research.

Before I transfer these excel files into SPSS, I need to convert string date and times in the following format:
"Sep 25 2001 9:00 PM" into a date and time that is recognized by excel.

I have so far divided the text into columns, and I can then concatenate them back together to form a date, but when the time is added it reports it in the fraction (e.g. for 9:00 PM = 21:00 = 21/24 hours in a day = 0.875) How can I make it NOT turn the time into a fraction?

I figured changing these into a date/time that is not string would be easier on the excel side than on SPSS as I am just learning SPSS. However, if anyone knows how to convert string date/times to a date/time that is recognized by SPSS, that would be A-OK, too! :whistling:
  • 0

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
Jess,
Try changing the display properties for the cell
eg. Format Cells / Number / Time / 1:30pm
  • 0

#3
Ketan0510

Ketan0510

    New Member

  • Member
  • Pip
  • 1 posts
Split the String into Date and Time and Convert them using DATEVALUE() and then add them
Eg
Sep 09 2010 10:00 AM

1. Split into two strings date and time separately using =MID()
2. Convert both these values to Date using C1=DATEVALUE(A1) and D1=DATEVALUE(B1)
3. Add these two values eg =C1+D1.
4. Format the number displayed into Date Time format by right clicking the cell and Format cells

Edited by Ketan0510, 30 December 2010 - 06:36 PM.

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP