24 hour clock problems text to column

**andyspeak** · 01-19-2014, 11:47 AM

Hello
I have a date and time column which i am trying to split.
It is currently displaying like this:

Date & time
30-06-12 10:00
30-06-12 11:00
30-06-12 12:00
30-06-12 13:00
30-06-12 14:00
30-06-12 15:00

but when you click on an individual cell, in the formula window it looks like:

30-06-2012 11:00:01 AM
30-06-2012 12:00:01 PM

When i do a text to columns split it only returns the time column in the 12 hour format but i want it in the 24 hour format. im sure it must be something to do with how the date and time column is formatted BEFORE i attempt the column split but i really cant seem to alter it to a format that works. Any help?

**AlKey** · 01-19-2014, 12:02 PM

Try this

In B1

=LEFT(A1,FIND(" ",A1)-1)

In C1

=RIGHT(A1,FIND(" ",A1)-4)

	A	B	C
1	30-06-12 10:00	30-06-12	10:00
2	30-06-12 11:00	30-06-12	11:00
3	30-06-12 12:00	30-06-12	12:00
4	30-06-12 13:00	30-06-12	13:00
5	30-06-12 14:00	30-06-12	14:00
6	30-06-12 15:00	30-06-12	15:00

**andyspeak** · 01-19-2014, 12:10 PM

Thanks but that doesnt work. It just says #VALUE! and i dont know enough about what the formula does to try tweaking it for my data

**AlKey** · 01-19-2014, 12:11 PM

I suggest that you upload a sample spreadsheet with about 10-15 rows of data so we could figure our a solution for you.

To upload a file click on Go Advanced on the bottom of your screen, then click on paperclip icon and Add Files.

**andyspeak** · 01-19-2014, 12:14 PM

See attached

**AlKey** · 01-19-2014, 12:18 PM

In B1

=INT(A1)

and format cell as Date

In C1

=MOD(A1,1)

Fromat cell as Time

**alvin-chung** · 01-19-2014, 12:20 PM

Originally Posted by andyspeak

Hello
I have a date and time column which i am trying to split.
It is currently displaying like this:

Date & time
30-06-12 10:00
30-06-12 11:00
30-06-12 12:00
30-06-12 13:00
30-06-12 14:00
30-06-12 15:00

but when you click on an individual cell, in the formula window it looks like:

30-06-2012 11:00:01 AM
30-06-2012 12:00:01 PM

When i do a text to columns split it only returns the time column in the 12 hour format but i want it in the 24 hour format. im sure it must be something to do with how the date and time column is formatted BEFORE i attempt the column split but i really cant seem to alter it to a format that works. Any help?

Hi, I could only guess you're using space as the delimiter for Text to Columns?
You can just use AlKey method, or simply copy/format the timestamp to a format to "dd-mm-yy" and "hh:mm" (all depends on your objective for the splitting ;=))

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

**andyspeak** · 01-19-2014, 12:20 PM

That works - thanks a lot

**AlKey** · 01-19-2014, 12:22 PM

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

**6StringJazzer** · 01-19-2014, 01:27 PM

alvin-chung, Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

**alvin-chung** · 01-19-2014, 10:15 PM

Hi Jeff, ah ok

**martindwilson** · 01-19-2014, 10:50 PM

interesting i can split that perfectly ok with text to columns
ah i see your problem
in your regional settings in windows you have long and or short time set to hh:mm:ss tt
if you change that to
hh:mm:ss the am/pm goes away in the formula bar
however i can still split with text to columns ok

24 hour clock problems text to column

LinkBack

Thread Tools

Rate This Thread

Display

24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Re: 24 hour clock problems text to column

Thread Information

Users Browsing this Thread

Similar Threads

Subtracting 1 hour from 24 hour clock

Default time from 12 hour clock to 24 hour clock

Decimal to 24 hour clock please.

Decimal to 24 hour clock please.

Decimal to 24 hour clock please.

Bookmarks

Bookmarks

Posting Permissions