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

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

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

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