|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 21st, 2010, 01:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert times to text string
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(H OUR(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. 
|
|
#2
April 21st, 2010, 02:32 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert times to text string
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(H OUR(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.
|
|
#3
April 21st, 2010, 03:19 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert times to text string
=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(H OUR(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 | |
| Display Modes | |
|
|
Linear Mode
