Help with time interval

[tailor.me]

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

 

[Mike H]

Hi,

With your time in A1 try this, drag down as required.

=TEXT(FLOOR(A1,1/ (24*2)),"HH:MM")&"-"&TEXT(CEILING(A1,1/
(24*2)),"HH:MM AM/PM")

Mike

 

[Shane Devenshire]

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

 

[T. Valko]

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

 

[Mike H]

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

 

[Mike H]

Grrrrrrr,

No it doesn't, but this one does

=IF(TEXT(FLOOR(A2,1/ (24*2)),"HH:MM")>TEXT(TIME(12,30,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

 

[Mike H]

hi,

Neat if you get rid of the couple of - signs that seem to have crept
in.

Mike

 

[Mike H]

I think there's something going in in posting, The way I "see" your
formula it has additional minus signs you obviously didn't type
yourself. The first is after the first "hh:mm" format which i see as
"hh:mm-" and the second is after +"3:00" which I see as +"3:00-" and
while we both know they shouldn't be there the OP might not. Once
again, nice formula.

Mike

 

[T. Valko]

Google Groups sometimes adds "formatting junk" to posts.

I'm using Outlook Express and I can see the first "-" in: "hh:mm-"

I think that's intended to be the time separator:

time-time

The other one you're seeing was put there by Google: +"3:00-"

I don't see that other "-" using Outlook Express.

--
Biff
Microsoft Excel MVP


I think there's something going in in posting, The way I "see" your
formula it has additional minus signs you obviously didn't type
yourself. The first is after the first "hh:mm" format which i see as
"hh:mm-" and the second is after +"3:00" which I see as +"3:00-" and
while we both know they shouldn't be there the OP might not. Once
again, nice formula.

Mike