formula help

C

chefmike

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
 
C

Chip Pearson

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)
 
Top