| Home |
| Search |
| Today's Posts |
|
#1
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
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
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to calculate # of times text string is displayed | Excel Worksheet Functions | |||
| count the amount of times a two letters appears in text string | Excel Discussion (Misc queries) | |||
| Find the same text multiple times in a string seperated by commas | Excel Discussion (Misc queries) | |||
| cannot convert text string into value | Excel Worksheet Functions | |||
| count no of times a letter occurs in a text string | Excel Discussion (Misc queries) | |||
Linear Mode
