Hmmmm,

I agree about yours being easier to read but this now seems to do what
the OP wants albeit in a very long winded way!!

=IF(TEXT(FLOOR(A2,1/ (24*2)),"HH:MM")TEXT(TIME(12,0,0),"HH:MM"),TEXT
(FLOOR(A2-TIME(12,0,0),1/ (24*2)),"HH:MM"),TEXT(FLOOR(A2,1/
(24*2)),"HH:MM"))&" - "&TEXT(FLOOR(A2+TIME(0,30,0),1/ (24*2)),"HH:MM
am/pm")

Mike



On Dec 28, 7:39*am, "T. Valko" wrote:
Note the previous formula fails for 9:00 am --- it returns 9:00-9:00 am..

Yours also fails:

9:34 AM = 09:30-09:30 AM
9:54 AM = 09:30-09:30 AM
10:30 AM = 10:30-10:30 AM
11:30 PM = 23:30-11:30 PM

Not incorrect, but.....

11:10 PM = 23:00-11:30 PM

Try this one:

=TEXT(FLOOR(A1,1/48),"h:mm am/pm")&" - "&TEXT(FLOOR(A1,1/48)+1/48,"h:mm
am/pm")

Seems easier to read without the leading 0s and a space between times.

11:00 PM - 11:30 PM
9:00 AM - 9:30 AM

09:30-09:30 AM

--
Biff
Microsoft Excel MVP

"Shane Devenshire" wrote in message

...



Hi,

Here is one solution

=TEXT(FLOOR(A1,1/48),"hh:mm")&"-"&TEXT(FLOOR(A1,1/24)+1/48,"hh:mm am/pm")

Note the previous formula fails for 9:00 am --- it returns 9:00-9:00 am.

And both formulas return results like

13:00-01:30 PM

for times after 1:00 PM. *If this is a problem you could modify the
formula to read

=TEXT(FLOOR(A1,1/48),"hh:mm am/pm")&"-"&TEXT(FLOOR(A1,1/24)+1/48,"hh:mm
am/pm")

If neither of these solutions are acceptable:

One solution is a lookup table. *For example in E1 enter 9:00 AM or
whatever starting time is your earliest and in E2 enter 9:30 AM. *Select
them both and drag the fill handle down as far as necessary. *In F1 enter
the message you want to display for example, 9:00-9:30 am you will need to
enter all the messages in column F. *Then suppose your calls are in
A1:A100 in C1 enter

=VLOOKUP(A1,E$1:F$48,2)

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

wrote in message
...
Hi Team,
* * * * * * I work in a call centre and trying to write a formula
which allow me to classify calls received / abandoned between 30
minute *intervals .

Time abandoned *No of calls * * * * Time

9:00:00 am * * * * * * *1 * * This is where I need the formula (output
should say 9:00-9:30 am
9:08:00 am * * * * * * *1 * * output should say 9:00-9:30 am
9:10:00 am * * * * * * *1 * * *output should say 9:00-9:30 am
9:34:00 am * * * * * * *1 * * *output should say 9:30-10:00 am
9:54:00 am * * * * * * *1 * * *output should say 9:30-10:00 am
10:00:00 am * * * * * *1 * * * output should say 10:00-10:30 am
10:15:00 am * * * * * *1 * * *output should say 10:00-10:30 am
10:30:00 am * * * * * * 1 * * output should say 10:30-11:00 am- Hide quoted text -

- Show quoted text -