Time Calculation

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
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
BCDEFGHIJ
5DateAgent NameAbsShift CodeActivity 10:30
6Schd StartActualSchd EndActualNon Adh
701.11.05Agent 11E1:000:571:301:290:04
801.11.05Agent 21D0:300:281:001:010:03
901.11.05Agent 32B2:303:000:30
1001.11.05Agent 41D0:300:311:001:010:01
1101.11.05Agent 51C0:301:000:30
1201.11.05Agent 61E1:001:511:302:151:36
1301.11.05Agent 71D0:300:311:000:410:20
1401.11.05Agent 81A23:300:000:30
1501.11.05Agent 91A23:3023:300:000:0323:57
1601.11.05Agent 101D0:300:291:001:010:02
Data


Help Appreciated...
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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 :
Code:
(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

Active Member
Joined
Jan 4, 2004
Messages
359
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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 ...
Code:
(I15-H15)+IF(15<H15,1,0)
do the trick? You'll have to have a think and decide what to do.
 

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
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
BCDEFGHIJ
5DateAgent NameAbsShift CodeActivity 10:30
6Schd StartActualSchd EndActualNon Adh
701.11.05Agent 11E1:00:000:571:30:001:2923:56
801.11.05Agent 21D0:30:000:281:00:001:0123:59
901.11.05Agent 32B2:30:003:00:000:30
1001.11.05Agent 41D0:30:000:311:00:001:010:02
1101.11.05Agent 51C0:30:001:00:000:30
1201.11.05Agent 61E1:00:001:511:30:002:151:36
1301.11.05Agent 71D0:30:000:311:00:000:4123:42
1401.11.05Agent 81A23:30:0024:00:001:151:15
1501.11.05Agent 91A23:30:0023:3024:00:000:030:03
1601.11.05Agent 101D0:30:000:291:00:001:010:00
Data


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

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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 :-

Code:
'  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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,954
Members
412,300
Latest member
Chaneycr
Top