Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
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