Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Marck
 
Posts: n/a
Default Converting Text to Time format of XX:XX

I have a question on converting number in text to time format of 24 hours.

The information is copied from another data file and gets pasted as a text
file of 4 digits. IE: 'XXXX

I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
to get a time format but it is loosing #s when converting 0.

IE 0503 converted is 5:3
it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
05:03???

any and all help would be appreciated.. Thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
bpeltzer
 
Posts: n/a
Default Converting Text to Time format of XX:XX

If you want a real time value, as opposed to a text string that looks like a
time, you can use =time(left(d2,2),right(d2,2),0). If you just want to
insert the :, you can use =left(d2,2)&":"&right(d2,2).
--Bruce

"Marck" wrote:

I have a question on converting number in text to time format of 24 hours.

The information is copied from another data file and gets pasted as a text
file of 4 digits. IE: 'XXXX

I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
to get a time format but it is loosing #s when converting 0.

IE 0503 converted is 5:3
it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
05:03???

any and all help would be appreciated.. Thanks

  #3   Report Post  
Posted to microsoft.public.excel.misc
Marck
 
Posts: n/a
Default Converting Text to Time format of XX:XX

Thank you but I need it to show just the 4 digits. No AM or PM after it.

0503 converted to 05:03

Thanks any suggestions?

"bpeltzer" wrote:

If you want a real time value, as opposed to a text string that looks like a
time, you can use =time(left(d2,2),right(d2,2),0). If you just want to
insert the :, you can use =left(d2,2)&":"&right(d2,2).
--Bruce

"Marck" wrote:

I have a question on converting number in text to time format of 24 hours.

The information is copied from another data file and gets pasted as a text
file of 4 digits. IE: 'XXXX

I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
to get a time format but it is loosing #s when converting 0.

IE 0503 converted is 5:3
it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
05:03???

any and all help would be appreciated.. Thanks

  #4   Report Post  
Posted to microsoft.public.excel.misc
bpeltzer
 
Posts: n/a
Default Converting Text to Time format of XX:XX

Then go with the second option offered: =left(d2,2)&":"&right(d2,2).


"Marck" wrote:

Thank you but I need it to show just the 4 digits. No AM or PM after it.

0503 converted to 05:03

Thanks any suggestions?

"bpeltzer" wrote:

If you want a real time value, as opposed to a text string that looks like a
time, you can use =time(left(d2,2),right(d2,2),0). If you just want to
insert the :, you can use =left(d2,2)&":"&right(d2,2).
--Bruce

"Marck" wrote:

I have a question on converting number in text to time format of 24 hours.

The information is copied from another data file and gets pasted as a text
file of 4 digits. IE: 'XXXX

I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
to get a time format but it is loosing #s when converting 0.

IE 0503 converted is 5:3
it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
05:03???

any and all help would be appreciated.. Thanks

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Text numbers to time format jermsalerms Excel Discussion (Misc queries) 3 January 20th 06 01:03 AM
converting general cells format to text junkgrrl Excel Discussion (Misc queries) 1 January 12th 06 06:50 PM
Time format to number while keeping value Jim Excel Worksheet Functions 2 January 12th 06 06:45 PM
converting text in cell to a date time Herman Excel Worksheet Functions 3 October 22nd 05 07:18 PM
Converting to Time Format Amber Excel Discussion (Misc queries) 1 July 7th 05 10:56 PM


All times are GMT +1. The time now is 05:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"