Time to text

[Pete]

I would like a cell to say the following:

"Between 7:30 AM and 8:00 AM"

User puts 8:00 AM in cell A1

B1 is then calculated to the above

If A1 = 8:00 AM

My formula works =(A1-TIME(0,30,0))

It gives me the correct subracted time of 30 minutes
But I want to convert this to a text string.

 

[Dave Peterson]

something like:

="Between " & TEXT(A1-TIME(0,30,0),"h:mm AM/PM") & " and "
& TEXT(A1,"h:mm AM/PM")

(all one cell)

 

[Damon Longworth]

Try:

="Between "&text(b1,"hh:mm AM/PM")&" and "&text(a1,"hh:mm AM/PM")

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com