Convert times to text string

's Computer Specifications

In adjacent cells (A1 and A2) I have formatted start/stop times showing as
7:00 AM and 5:30 PM. In cell A3 I want concantenated text to appear as
"7:00am-5:30pm". So far this formula >

=TEXT(HOUR(A1),"0")&":"&TEXT(MINUTE(A1),"00")&IF(HOUR(A1)<12,"am-","pm-")&TEXT(HOUR(B1),"0")&":"&TEXT(MINUTE(B1),"00")&IF(HOUR(B1)<12,"am","pm")

gives 7:00am-17:30pm. I want the 17:30pm to appear as 5:30pm

Please help, I haven't quite cracked this nut. And is there an easier way
than what I am doing? Using Excel 2003.

's Computer Specifications

Try this formula at A3
=TEXT(A1,"h:mm am/pm") &"-" & TEXT(B1,"h:mm am/pm"

Lee Li Fon
http://smartusemicrosoftoffice.blogspot.com/

---
frmsrcurl: http://msgroups.net/microsoft.public...to-text-string

's Computer Specifications

Copy and paste the below formula in A3 cell
=TEXT(A1,"H:MM AM/PM")&" - "&TEXT(A2,"H:MM AM/PM")

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------

"AZSteve" wrote:

> In adjacent cells (A1 and A2) I have formatted start/stop times showing as
> 7:00 AM and 5:30 PM. In cell A3 I want concantenated text to appear as
> "7:00am-5:30pm". So far this formula >
>
> =TEXT(HOUR(A1),"0")&":"&TEXT(MINUTE(A1),"00")&IF(HOUR(A1)<12,"am-","pm-")&TEXT(HOUR(B1),"0")&":"&TEXT(MINUTE(B1),"00")&IF(HOUR(B1)<12,"am","pm")
>
> gives 7:00am-17:30pm. I want the 17:30pm to appear as 5:30pm
>
> Please help, I haven't quite cracked this nut. And is there an easier way
> than what I am doing? Using Excel 2003.
>

's Computer Specifications

=LOWER(TEXT(A1,"h:mmam/pm-")&TEXT(B1,"h:mmam/pm"))

"AZSteve" wrote:

> In adjacent cells (A1 and A2) I have formatted start/stop times showing as
> 7:00 AM and 5:30 PM. In cell A3 I want concantenated text to appear as
> "7:00am-5:30pm". So far this formula >
>
> =TEXT(HOUR(A1),"0")&":"&TEXT(MINUTE(A1),"00")&IF(HOUR(A1)<12,"am-","pm-")&TEXT(HOUR(B1),"0")&":"&TEXT(MINUTE(B1),"00")&IF(HOUR(B1)<12,"am","pm")
>
> gives 7:00am-17:30pm. I want the 17:30pm to appear as 5:30pm
>
> Please help, I haven't quite cracked this nut. And is there an easier way
> than what I am doing? Using Excel 2003.
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Count number times text appears in string	nc	Microsoft Excel Misc	1	5th May 2010 02:53 PM
Trying to calculate # of times text string is displayed	data_entry	Microsoft Excel Worksheet Functions	1	5th Apr 2010 10:58 PM
count no of times a letter occurs in a text string	nishad	Microsoft Excel Misc	4	10th Jan 2008 03:13 PM
get the text string appear the most times !	luanhoxung@gmail.com	Microsoft Access Reports	0	14th Jun 2007 12:02 PM
convert html-string to plain text-string	Nedo	Microsoft Dot NET	4	28th Jul 2005 09:53 AM