Time Calculation

[xl_psychic]

All:

I had initially started off discussion on this topic in the following thread..

http://www.mrexcel.com/board2/viewtopic.php?t=177283

In the attached HTML Screenshot I am having difficulties in getting the Non Adherence timing for Agent 9. Ideally the scheduled start timings are being calculated on the basis of entries made in another sheet. I am changing the original timings to reflect PST timings by subtracting 13:30 hours from the original. Scheduled End Timings are calculated by adding Activity time (Cell J5) to the Start Timings.

Now in the example for Agent 9, the Non Adherence should be 3 minutes and not 23:57 hours.

BMT - Mario Caldeira.xls
	B	C	D	E	F	G	H	I	J
5	Date	Agent Name	Abs	Shift Code	Activity 1				0:30
6					Schd Start	Actual	Schd End	Actual	Non Adh
7	01.11.05	Agent 1		1E	1:00	0:57	1:30	1:29	0:04
8	01.11.05	Agent 2		1D	0:30	0:28	1:00	1:01	0:03
9	01.11.05	Agent 3		2B	2:30		3:00		0:30
10	01.11.05	Agent 4		1D	0:30	0:31	1:00	1:01	0:01
11	01.11.05	Agent 5		1C	0:30		1:00		0:30
12	01.11.05	Agent 6		1E	1:00	1:51	1:30	2:15	1:36
13	01.11.05	Agent 7		1D	0:30	0:31	1:00	0:41	0:20
14	01.11.05	Agent 8		1A	23:30		0:00		0:30
15	01.11.05	Agent 9		1A	23:30	23:30	0:00	0:03	23:57
16	01.11.05	Agent 10		1D	0:30	0:29	1:00	1:01	0:02
Data

Help Appreciated...

 

[GlennUK]

You really need to think a little more about your calculations when they span midnight. The reason you are getting 23:57 for Agent 9 is that the formula is doing :

(ABS(G15-F15)+ABS(I15-H15))
which is
(ABS(0-0)+ABS(00:03-24:00))
which is
(0+ABS(-23:57))
which is
23:57

in other words, the calculation is doing what you requested.

 

[xl_psychic]

Glen:

Thanks for pointing that out...

How would I force excel to recognize 24:00 as 00:00 so that I get the difference of 3 minutes..

Help Appreciated..

 

[GlennUK]

What if you had to do 00:03 minus 23:45? Have you considered that? Fixing things for one particular case is not the way to go. You need to decide how to process calculations spanning midnight. For example would ...

(I15-H15)+IF(15<H15,1,0)

do the trick? You'll have to have a think and decide what to do.

 

[xl_psychic]

Glen:

I got what you were saying... I tried using the formula that you gave me and given below is the outcome for the same scenario as posted earlier..

BMT - Mario Caldeira.xls
	B	C	D	E	F	G	H	I	J
5	Date	Agent Name	Abs	Shift Code	Activity 1				0:30
6					Schd Start	Actual	Schd End	Actual	Non Adh
7	01.11.05	Agent 1		1E	1:00:00	0:57	1:30:00	1:29	23:56
8	01.11.05	Agent 2		1D	0:30:00	0:28	1:00:00	1:01	23:59
9	01.11.05	Agent 3		2B	2:30:00		3:00:00		0:30
10	01.11.05	Agent 4		1D	0:30:00	0:31	1:00:00	1:01	0:02
11	01.11.05	Agent 5		1C	0:30:00		1:00:00		0:30
12	01.11.05	Agent 6		1E	1:00:00	1:51	1:30:00	2:15	1:36
13	01.11.05	Agent 7		1D	0:30:00	0:31	1:00:00	0:41	23:42
14	01.11.05	Agent 8		1A	23:30:00		24:00:00	1:15	1:15
15	01.11.05	Agent 9		1A	23:30:00	23:30	24:00:00	0:03	0:03
16	01.11.05	Agent 10		1D	0:30:00	0:29	1:00:00	1:01	0:00
Data

Looks like I really need to do some thinking...

 

[BrianB]

Normally I keep away from discussions on date/time because I too find this difficult to handle. I suppose it is good to know I am not alone.

The thing to remember is that hidden underneath everything is a set of simple numbers. Whole numbers are days counting from January 1st. 1900 (day 1), and decimals (to 9 places) are times. I find it helpful to remember that I am never using Time alone. When using hours and minutes Excel is still counting from the base 1/1/1900 (or more correctly 31/12/1899 24:00:00 as 'seconds zero').

As as simple experiment. With cell B1 containing 23:30, Cell C1 containing 0:40 and Cell D1 containing formula =B1 + C1. Copy these cells twice into rows2 and 3, and reformat - we get this :-

'  A       B                      C                   D = B1+C1
'1.Format "hh:mm"               23:30               00:40               00:10
'2.Format "0.000000000"         0.979166667         0.027777778         1.006944444
'3.Format "dd/mm/yy hh:mm:ss"   00/01/00 23:30:00   00/01/00 00:40:00   01/01/00 00:10:00

So what we actually see in Excel is not necessarily what we have got. The number underlying the result of 10 minutes could relate to any day in 2 centuries. You have supplied us with a view of a worksheet, but we cannot see what is really there. Not only that, you tell us that there are things happening in other worksheets first.

In a case such as this I would go back to the starting numbers and reformat them as simple numbers. That way it would be easier to follow through and check that the formulas are producing correct results.

Hope this helps.