Subract Time

[aliaslamy2k]

Dear Experts,

How can I subract time ? below is an example i am trying but the result is just #######
I want to know how much time was consumed.

Formula used =B1-A1

A1	B1	Result
22:10	02:39	##############

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

Rgds,
AB

 

[VoG]

Try

=MOD(B1-A1,1)

 

[aliaslamy2k]

Hello Mr Vog,

Nice to see you !

I am getting incorrect results, the problem is little confusing. I am analyzing the total delay time of the flights ( there are 100's of data)
the Actual time of Arrival minus Standard time of departure of other flight.

So the data will be fluctuating between yesterday and today, as some flights arrive on 21 dec and some on 22dec and same with departures
some will depart on 21st dec and some will depart on 22nd Dec

Actual Time of Arrival	Standard time of departure	RESULTS	STATUS
02:39	23:35	20:56	Incorrect result
02:39	23:50	21:11	Incorrect result
02:39	00:40	22:01	Incorrect result
01:06	01:20	0:14	ok
01:06	01:20	0:14	ok
01:06	01:25	0:19	ok
The time includes yesterday and today i.e 21dec12 & 22 Dec12

<tbody>
</tbody>

 

[norulen]

I am a little confused over pattern of your Actual time of arrival and Standard time of departure
Can you post an input sample includind additional columns of Dates for ATA and STD, for me to understand and help you out

Also include the expected result

 

[aliaslamy2k]

Hi Norulen,

Please find below data. the result is in STD-ATA ( Schedule time of Departure - Actual Time of Arrival )

In Flt no	In Flt Date	In Sector	Schedule Time Of Arrival	Actual Time of Arrival	Out Flt no	Out Flt Date	Out Sector	Schedule Time of Departure	Actual Time of Departure	STD-ATA
XX123	12-21-2012	TUN	22:10	02:39	XX172	12-20-2012	MCT	23:25	23:25	3:14
XX123	12-21-2012	TUN	22:10	02:39	XX722	12-20-2012	RUH	23:25	23:42	3:14
XX123	12-21-2012	TUN	22:10	02:39	XX148	12-20-2012	KWI	23:35	23:29	3:04
XX123	12-21-2012	TUN	22:10	02:39	XX142	12-20-2012	DXB	23:50	23:57	2:49
XX123	12-21-2012	TUN	22:10	02:39	XX454	12-21-2012	SAH	00:40	00:40	1:59
XX123	12-21-2012	TUN	22:10	02:39	XX584	12-21-2012	JNB	01:05	01:37	1:34
XX123	12-21-2012	TUN	22:10	02:39	XX102	12-21-2012	DXB	01:10	01:21	1:29
XX123	12-21-2012	TUN	22:10	02:39	XX888	12-21-2012	PVG	01:10	01:24	1:29
XX123	12-21-2012	TUN	22:10	02:39	XX614	12-21-2012	BKK	01:20	01:49	1:19
XX123	12-21-2012	TUN	22:10	02:39	XX614	12-21-2012	HAN	01:20	01:49	1:19
XX123	12-21-2012	TUN	22:10	02:39	XX234	12-21-2012	DEL	01:25	01:29	1:14
XX123	12-21-2012	TUN	22:10	02:39	XX150	12-21-2012	BAH	01:30	01:49	1:09
XX123	12-21-2012	TUN	22:10	02:39	XX624	12-21-2012	HKT	01:30	02:22	1:09
XX123	12-21-2012	TUN	22:10	02:39	XX624	12-21-2012	KUL	01:30	02:22	1:09
XX123	12-21-2012	TUN	22:10	02:39	XX138	12-21-2012	KWI	01:35	01:36	1:04
XX123	12-21-2012	TUN	22:10	02:39	XX718	12-21-2012	DMM	01:35	01:39	1:04
XX123	12-21-2012	TUN	22:10	02:39	XX812	12-21-2012	HKG	01:50	01:53	0:49
XX123	12-21-2012	TUN	22:10	02:39	XX898	12-21-2012	PEK	02:05	02:36	0:34
XX123	12-21-2012	TUN	22:10	02:39	XX546	12-21-2012	JRO	02:10	02:10	0:29
XX123	12-21-2012	TUN	22:10	02:39	XX646	12-21-2012	MNL	02:10	02:15	0:29
XX123	12-21-2012	TUN	22:10	02:39	XX120	12-21-2012	AUH	02:15	02:13	0:24
XX123	12-21-2012	TUN	22:10	02:39	XX672	12-21-2012	CGK	02:20	02:19	0:19
XX123	12-21-2012	TUN	22:10	02:39	XX104	12-21-2012	DXB	02:35	02:34	0:04
XX456	12-21-2012	GRU	22:50	01:06	XX142	12-20-2012	DXB	23:50	23:57	1:16
XX456	12-21-2012	GRU	22:50	01:06	XX420	12-20-2012	BEY	23:55	00:02	1:11
XX456	12-21-2012	GRU	22:50	01:06	XX030	12-21-2012	MEL	00:55	01:23	0:11
XX456	12-21-2012	GRU	22:50	01:06	XX380	12-21-2012	MLE	01:00	01:15	0:06
XX456	12-21-2012	GRU	22:50	01:06	XX484	12-21-2012	IKA	01:05	01:13	0:01
XX456	12-21-2012	GRU	22:50	01:06	XX584	12-21-2012	JNB	01:05	01:37	0:01
XX456	12-21-2012	GRU	22:50	01:06	XX848	12-21-2012	CKG	01:05	01:11	0:01
XX456	12-21-2012	GRU	22:50	01:06	XX102	12-21-2012	DXB	01:10	01:21	23:56
XX456	12-21-2012	GRU	22:50	01:06	XX888	12-21-2012	PVG	01:10	01:24	23:56
XX456	12-21-2012	GRU	22:50	01:06	XX802	12-21-2012	KIX	01:15	01:20	23:51
XX456	12-21-2012	GRU	22:50	01:06	XX614	12-21-2012	BKK	01:20	01:49	23:46
XX456	12-21-2012	GRU	22:50	01:06	XX638	12-21-2012	SIN	01:20	02:13	23:46

<tbody>
</tbody>

Current formula is =MOD(E2-I2,1), but if you see the below results it shows 23:56.... which is wrong. it should be somewhere around 2hrs 30min


Rgds,
AB

 

[norulen]

Try this formula
=IF(G2=B2,E2-I2,E2+24-I2)
B2=In Flt Date
G2=Out Flt Date
E2=Actual Time of Arrival
I2=Schedule Time of Departure

 

[norulen]

This seems to give the correct result. But there seems to be some error in the last five rows where Actual Time of Arrival is Before Scheduled Time of Departure. I wonder how the STD-ATA is arrived at. Do you mind explaining. Shouldn delay in this case be Zero?? If it has to be Zero then formula would be
=IF(G32=B32,IF(I32>E32,0,E32-I32),E32+24-I32)

 

[aliaslamy2k]

Hi Norulen,

The logic behind this is if Inbound flight XX456 is delayed it will misconnect the outbound flight which is XX102 because of the STD.
so I want to know the time difference between those. ( Positive & Negative both )

Many thanks, I got the correct answer for positive time range. but still I am getting ###### error on negative time

XX456	12-21-2012	GRU	22:50	01:06	XX102	12-21-2012	DXB	01:10	01:21	############
XX456	12-21-2012	GRU	22:50	01:06	XX888	12-21-2012	PVG	01:10	01:24	############
XX456	12-21-2012	GRU	22:50	01:06	XX802	12-21-2012	KIX	01:15	01:20	############
XX456	12-21-2012	GRU	22:50	01:06	XX614	12-21-2012	BKK	01:20	01:49	############
XX456	12-21-2012	GRU	22:50	01:06	XX638	12-21-2012	SIN	01:20	02:13	############

<tbody>
</tbody>

Many thanks in advance.


Rgds,
AB

 

[norulen]

Can you tell me what is the result that you expect for these five rows and which formula are you using out of the two formulas i gave

Please explain the logic also for arriving the expected result

 

[norulen]

I have completely understood you but just shooting in dark try this
=IF(G36=B36,IF(I36>E36,I36-E36,E36-I36),E36+24-I36)