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

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

On Dec 28, 2:53*am, wrote:
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

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

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

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 -

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

On Dec 28, 8:37*am, Mike H wrote:
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 -- Hide quoted text -

- Show quoted text -

Perhaps just

=LOWER(TEXT(FLOOR(MOD(A2,0.5),"0:30"),"hh:mm-")&TEXT(FLOOR(A2,"0:30")+"0:30","hh:mm
AM/PM"))


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44469

hi,

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

Mike


On Dec 28, 8:56*am, barry houdini <barry.houdini.
wrote:
Perhaps just

=LOWER(TEXT(FLOOR(MOD(A2,0.5),"0:30"),"hh:mm-")&TEXT(FLOOR(A2,"0:30")+"0:30*","hh:mm
AM/PM"))

--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile:http://www.thecodecage.com/forumz/member.php?userid=72
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=44469

Mike H;160218 Wrote:
Neat if you get rid of the couple of - signs that seem to have crept in

Hello Mike, there seems to be an asterisk after the last 0:30 in the
version you quoted. That isn't in the original formula I posted,
otherwise the formula is OK, I think, and as I intended.


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44469

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

On Dec 28, 10:07*am, barry houdini <barry.houdini.
wrote:
Mike H;160218 Wrote:

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

Hello Mike, there seems to be an asterisk after the last 0:30 in the
version you quoted. That isn't in the original formula I posted,
otherwise the formula is OK, I think, and as I intended.

--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile:http://www.thecodecage.com/forumz/member.php?userid=72
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=44469

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


"Mike H" wrote in message
...
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

On Dec 28, 10:07 am, barry houdini <barry.houdini.
wrote:
Mike H;160218 Wrote:

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

Hello Mike, there seems to be an asterisk after the last 0:30 in the
version you quoted. That isn't in the original formula I posted,
otherwise the formula is OK, I think, and as I intended.

--
barry houdini
------------------------------------------------------------------------
barry houdini's
Profile:http://www.thecodecage.com/forumz/member.php?userid=72
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=44469