Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
I need some help, I am using Excel 2003 and need help with a formula. Cell
"B4" has a time entered into it, lets say 7:00 AM; Cell "B5" has a time entered also, lets say "15:30 PM. In cell "F6" I would to combine these two times in the cell to look like "7:00-3:30". Hope this is possible and if it is how would I do it. If any more information is needed let me know. Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
Try the following formula (all on one line)
=LEFT(TEXT(B4,"h:mm am/pm"),4+(OR(AND(HOUR(B4)=10,HOUR(B4)<=12),AND(HOUR (B4)=22,HOUR(B4)<=23))))&"-"&LEFT(TEXT(F6,"h:mm am/pm"),4+(OR(AND(HOUR(F6)=10,HOUR(F6)<=12),AND(HOUR (F6)=22,HOUR(F6)<=23)))) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "chefmike" wrote in message ... I need some help, I am using Excel 2003 and need help with a formula. Cell "B4" has a time entered into it, lets say 7:00 AM; Cell "B5" has a time entered also, lets say "15:30 PM. In cell "F6" I would to combine these two times in the cell to look like "7:00-3:30". Hope this is possible and if it is how would I do it. If any more information is needed let me know. Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
Thanks Chip
That worked like a charm. Thanks again "Chip Pearson" wrote: Try the following formula (all on one line) =LEFT(TEXT(B4,"h:mm am/pm"),4+(OR(AND(HOUR(B4)=10,HOUR(B4)<=12),AND(HOUR (B4)=22,HOUR(B4)<=23))))&"-"&LEFT(TEXT(F6,"h:mm am/pm"),4+(OR(AND(HOUR(F6)=10,HOUR(F6)<=12),AND(HOUR (F6)=22,HOUR(F6)<=23)))) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "chefmike" wrote in message ... I need some help, I am using Excel 2003 and need help with a formula. Cell "B4" has a time entered into it, lets say 7:00 AM; Cell "B5" has a time entered also, lets say "15:30 PM. In cell "F6" I would to combine these two times in the cell to look like "7:00-3:30". Hope this is possible and if it is how would I do it. If any more information is needed let me know. Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
Hi Mike,
Let's suppose all your second times are in the afternoon, a shorter formula would be: =TEXT(B5,"h:mm")&"-"&TEXT(C5-0.5,"h:mm") If they are not then =TEXT(B5,"H:MM")&"-"&TEXT(IF(C50.5,C5-0.5,C5),"h:mm") -- Cheers, Shane Devenshire "chefmike" wrote: Thanks Chip That worked like a charm. Thanks again "Chip Pearson" wrote: Try the following formula (all on one line) =LEFT(TEXT(B4,"h:mm am/pm"),4+(OR(AND(HOUR(B4)=10,HOUR(B4)<=12),AND(HOUR (B4)=22,HOUR(B4)<=23))))&"-"&LEFT(TEXT(F6,"h:mm am/pm"),4+(OR(AND(HOUR(F6)=10,HOUR(F6)<=12),AND(HOUR (F6)=22,HOUR(F6)<=23)))) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "chefmike" wrote in message ... I need some help, I am using Excel 2003 and need help with a formula. Cell "B4" has a time entered into it, lets say 7:00 AM; Cell "B5" has a time entered also, lets say "15:30 PM. In cell "F6" I would to combine these two times in the cell to look like "7:00-3:30". Hope this is possible and if it is how would I do it. If any more information is needed let me know. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |