my brain is melting !

  • Thread starter Thread starter Hawksy
  • Start date Start date
H

Hawksy

Hello,

Have a bit of a conundrum here and was wondering if anyone could help
I'm trying to put together a series of formulas and having littl
success.

I have a weeks worth of data relating to a task. For each task I hav
the date, task submitted time and task started time.

What I want to do is find the 'highest wait time by day and by shift'

So far I have managed to get the wait time:

A1) is SubmitDate/time = 24/11/2003 15:15
B2) is Start Date/time = 24/11/2003 15:45
C3) is Wait time = B2- A2 (with the cell format as [h]:mm)
D4) Submit time = A1 (with the cell formatt as hh:mm)

To get the shift I tried the following
Shift =IF($D2>=0.666666666666667,"Evening"
IF($D2>=0.333333333333333,"Day",IF($D2>=0,"Graveyard")))

The first problem is that D4 still contains the date as well as th
time. Is there any way to use a formula to ensure that only the time i
shown in this cell. This should make the shift formula work.

After that I have no idea where to begin with getting the highest o
these wait times by shift and by day.

Help
 
One way:

E2: =IF(MOD($D2,1)>=TIME(16,0,0), "Evening",
IF(MOD($D2,1)>=TIME(8,0,0),"Day","Graveyard"))

another:

E2: =CHOOSE(MOD($D2,1)*3+1,"Graveyard","Day","Evening")

The MOD($D2,1) strips out any integers and leaves only the time.

For highest wait time, one way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=MAX((E1:E1000="Day")*C1:C1000)
 
Use this formula in D4"

=A1-INT(A1)

which strips out the date (whole number) part, leaving the time
(fraction) part of the date-time.

I assume you really mean the data is in A2, B2, C2, and D2, so each case
is in the same unique row, leaving Row 1 for headers. You can put your
Shift formula into E2. Then you can use a Pivot Table to spit out the
maximum waits per day and shift.

Here's a sample data set:

Submit Date-Time Start Date-Time Wait Submit Shift
11/1/2003 5:51 AM 11/1/2003 11:15 AM 5:24 5:51 AM Graveyard
11/1/2003 4:11 PM 11/1/2003 7:23 PM 3:11 4:11 PM Evening
11/4/2003 9:35 PM 11/4/2003 10:37 PM 1:02 9:35 PM Evening
11/6/2003 12:52 PM 11/6/2003 2:15 PM 1:23 12:52 PM Day
11/8/2003 4:28 AM 11/8/2003 4:59 AM 0:31 4:28 AM Graveyard
11/10/2003 1:44 AM 11/10/2003 1:47 AM 0:02 1:44 AM Graveyard
11/11/2003 12:53 AM 11/11/2003 2:00 AM 1:06 12:53 AM Graveyard
11/11/2003 7:29 PM 11/11/2003 9:26 PM 1:57 7:29 PM Evening
11/12/2003 9:36 AM 11/12/2003 10:30 AM 0:53 9:36 AM Day
11/12/2003 9:41 AM 11/12/2003 11:08 AM 1:26 9:41 AM Day
11/12/2003 12:37 PM 11/12/2003 4:36 PM 3:58 12:37 PM Day
11/16/2003 2:13 AM 11/16/2003 3:10 AM 0:57 2:13 AM Graveyard
11/16/2003 4:07 AM 11/16/2003 9:04 AM 4:56 4:07 AM Graveyard
11/17/2003 5:51 AM 11/17/2003 11:21 AM 5:30 5:51 AM Graveyard
11/17/2003 9:57 PM 11/17/2003 11:35 PM 1:38 9:57 PM Evening
11/18/2003 3:16 AM 11/18/2003 8:03 AM 4:47 3:16 AM Graveyard
11/20/2003 12:45 PM 11/20/2003 3:34 PM 2:48 12:45 PM Day
11/20/2003 3:30 PM 11/20/2003 4:10 PM 0:40 3:30 PM Day
11/21/2003 2:09 PM 11/21/2003 7:59 PM 5:50 2:09 PM Day
11/24/2003 9:42 AM 11/24/2003 10:53 AM 1:11 9:42 AM Day
11/25/2003 9:22 PM 11/26/2003 2:05 AM 4:43 9:22 PM Evening
11/26/2003 4:19 AM 11/26/2003 8:48 AM 4:29 4:19 AM Graveyard
11/26/2003 9:13 AM 11/26/2003 1:04 PM 3:51 9:13 AM Day
11/26/2003 12:15 PM 11/26/2003 12:25 PM 0:10 12:15 PM Day

I selected the range and made a pivot chart. I put Wait in the Data
area, double clicking and selecting Max. I put Submit Date-Time and
Shift in the Rows area. I got rid of all subtotals for these fields,
and right clicked on the Submit Date-Time header button, picked Group
and Outline from the popup menu, then clicked on Group. I selected By
Days, and unselected By Months. The result looks like this:

Max of Wait
Submit Date-Time Shift Total
1-Nov Evening 3:11
Graveyard 5:24
4-Nov Evening 1:02
6-Nov Day 1:23
8-Nov Graveyard 0:31
10-Nov Graveyard 0:02
11-Nov Evening 1:57
Graveyard 1:06
12-Nov Day 3:58
16-Nov Graveyard 4:56
17-Nov Evening 1:38
Graveyard 5:30
18-Nov Graveyard 4:47
20-Nov Day 2:48
21-Nov Day 5:50
24-Nov Day 1:11
25-Nov Evening 4:43
26-Nov Day 3:51
Graveyard 4:29

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
Hello,

Have a bit of a conundrum here and was wondering if anyone could help.
I'm trying to put together a series of formulas and having little
success.

I have a weeks worth of data relating to a task. For each task I have
the date, task submitted time and task started time.

What I want to do is find the 'highest wait time by day and by shift'

So far I have managed to get the wait time:

A1) is SubmitDate/time = 24/11/2003 15:15
B2) is Start Date/time = 24/11/2003 15:45
C3) is Wait time = B2- A2 (with the cell format as [h]:mm)
D4) Submit time = A1 (with the cell formatt as hh:mm)

To get the shift I tried the following
Shift =IF($D2>=0.666666666666667,"Evening",
IF($D2>=0.333333333333333,"Day",IF($D2>=0,"Graveyard")))

The first problem is that D4 still contains the date as well as the
time. Is there any way to use a formula to ensure that only the time is
shown in this cell. This should make the shift formula work.

After that I have no idea where to begin with getting the highest of
these wait times by shift and by day.

Help !
Click to expand...
 
Back
Top