Complex Formula for Date and time calculation

[mazher]

Date Out Time Out Date In Time In Time Difference
01-03-09 9:00 01-03-09 NIL
02-03-09 NIL 02-03-09 NIL
03-03-09 NIL 03-03-09 10:00 2 day(s) 1 hours 00 mins
03-03-09 11:15 03-03-09 18:00 0 day(s) 6 hours 45 mins
04-03-09 9:00 NIL NIL
05-03-09 NIL NIL NIL
06-03-09 NIL NIL NIL
07-03-09 NIL 07-03-09 14:45 3 day(s) 5 hours 45 mi

In Column E in want the time difference to be calculated as shown above.

Some Excel Guru can help me in doing this either through the formula or through macro.

I will be extremely thankful.

Regards

Mazher

 

[PaddyD]

I haven't examined your table in detail, 7 from a cursory inspection I don't see how, e.g. the 2 day(s) 1 hours 00 mins result is derived given the data, but in general:

time difference = (end date + end time) - (start date + start time)

 

[mazher]

Sir PaddyD

I have calculted the time difference as you have mentioned.

But the problem is I want to calculate the time difference in only those cells where the Time In is is written in Column D and with the previous timeout and date out values

Time difference is as as follows

E2= No Display
E3= No Display
E4 =(C4+D4)-(A2+B2)
E5 =(C5+D5)-(A5+B5)
E6= No Display
E7= No Display
E8= No Display
E9= (C9+D9)- (A6+B6)


Hope that I am able to explain my problem


Regards


Mazher

 

[mazher]

Hi Excel Gurus,

I think Sir PaddyD is offline.

In the mean time can some one help me either with the formula or the VBA code.

Thanks in advance.

Regards

Mazher

 

[mazher]

Still waiting for somene to help me on this issue

 

[Domenic]

Assuming that A1:D1 contains the column headers, and A2:D9 contains the data...

1) Define the following...

Select/click cell E2

Insert > Name > Define

Name:  DateOut

Refers to:

=INDEX($A$2:A2,MATCH(9.99999999999999E+307,$B$2:B2))

Click Add

Name:  TimeOut

Refers to:

=INDEX($B$2:B2,MATCH(9.99999999999999E+307,$B$2:B2))

Click Ok

2) Try the following...

E2, copied down:

=IF(ISNUMBER(D2),INT((C2+D2)-(DateOut+TimeOut))&" days, "&HOUR(MOD((C2+D2)-(DateOut+TimeOut),1))&" hours, and "&MINUTE(MOD((C2+D2)-(DateOut+TimeOut),1))&" minutes","")

Hope this helps!

 

[mazher]

Domenic Rocks,
MrExcel Forum Rocks

My problem solved.

Domenic Bro or Some Other Excel Guru please explain me what these formulas are doing

Name: DateOut

Refers to:

=INDEX($A$2:A2,MATCH(9.99999999999999E+307,$B$2:B2))

Click Add

Name: TimeOut

Refers to:

=INDEX($B$2:B2,MATCH(9.99999999999999E+307,$B$2:B2))


Why we have not defined DateIn and TimeIn like DateOut and TimeOut.


Also what this formula is doing
=IF(ISNUMBER(D2),INT((C2+D2)-(DateOut+TimeOut))&" days, "&HOUR(MOD((C2+D2)-(DateOut+TimeOut),1))&" hours, and "&MINUTE(MOD((C2+D2)-(DateOut+TimeOut),1))&" minutes","")


Once again thanks Domenic for the help.

Regards

Mazher

 

[mazher]

Hi Excel Gurus,

The Formula Started giving me strange results as follows

Date Out|Time Out|Date In|Time In|Time Difference
01-03-09|9:00|01-03-09|18:00|0 days, 17 hours, and 0 minutes
02-03-09|1:00|02-03-09|23:00|0 days, 22 hours, and 0 minutes


The first one should be 0 days, 9 Hours and 0 Minutes

This happens when I input the second entry in the TimeOut and the moment I delete it calculates rightly.

Regards

Mazher

 

[mazher]

Might Be Sir Aladin ( Formula Guru) can help me sort this?


Regards

Mazher

 

[Domenic]

In each case, whether it's your original data or subsequent one, the formula returns the desired result. Did you select/click cell E2 prior to defining DateOut and TimeOut?