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

 

[mazher]

Yes, I have selected Cell E2 before defining the DateOut and DateIn.

Regards

Mazher

 

[Domenic]

Yes, I have selected Cell E2 before defining the DateOut and DateIn.

Regards

Mazher

I'm assuming that you meant TimeOut, not DateIn, correct? If you'd like, I can send you a sample file. If so, you can send me you email address via Private Message.

 

[Domenic]

Sample file sent...

 

[mazher]

I have received the file and its working like a charm. I deleted all the Defined names and re-created them


Basically I have the fleet of trucks and I want to mange their times

One part is cleared. The Time spent by the vehicle outside the factory, I also want to calculate the idle time of the vehicle in the factory before its loaded again.

Date Out|Time Out|Date In|Time In|Time Consume|Idle Time
01-03-09|9:00|01-03-09|23:00|0 days, 14 hours, and 0 minutes|Blank
02-03-09|12:00|03-03-09|15:00|1 days, 3 hours, and 0 minutes|0 day(s) 13 hours 00 mins
03-03-09|NIL|NIL|NIL|Blank
05-03-09|10:00|05-03-09|20:00|0 days, 10 hours, and 0 minutes|1 day(s) 19 hours 00 mins
05-03-09|NIL|NIL|NIL|Blank
06-03-09|NIL|NIL|NIL|Blank
07-03-09|8:00|09-03-09|12:00|2 days, 4 hours, and 0 minutes|1 day(s) 12 hours 00 mins
08-0908|NIL|NIL|NIL|Blank


Regards

Mazher

 

[Domenic]

I have received the file and its working like a charm. I deleted all the Defined names and re-created them


Basically I have the fleet of trucks and I want to mange their times

One part is cleared. The Time spent by the vehicle outside the factory, I also want to calculate the idle time of the vehicle in the factory before its loaded again.

Date Out|Time Out|Date In|Time In|Time Consume|Idle Time
01-03-09|9:00|01-03-09|23:00|0 days, 14 hours, and 0 minutes|Blank
02-03-09|12:00|03-03-09|15:00|1 days, 3 hours, and 0 minutes|0 day(s) 13 hours 00 mins
03-03-09|NIL|NIL|NIL|Blank
05-03-09|10:00|05-03-09|20:00|0 days, 10 hours, and 0 minutes|1 day(s) 19 hours 00 mins
05-03-09|NIL|NIL|NIL|Blank
06-03-09|NIL|NIL|NIL|Blank
07-03-09|8:00|09-03-09|12:00|2 days, 4 hours, and 0 minutes|1 day(s) 12 hours 00 mins
08-0908|NIL|NIL|NIL|Blank


Regards

Mazher

1) Select/click cell F2

2) Define (Insert > Name > Define) the following...

Name:  DateIn

Refers to:

=INDEX($C$1:C1,MATCH(9.99999999999999E+307,$D$1:D1))

Click Add

Name:  TimeIn

Refers to:

=INDEX($D$1:D1,MATCH(9.99999999999999E+307,$D$1:D1))

Click Ok

3) Try...

F2, copied down:

=IF(ISNUMBER(B2),INT((A2+B2)-(DateIn+TimeIn))&" days, "&HOUR((A2+B2)-(DateIn+TimeIn))&" hours and "&MINUTE((A2+B2)-(DateIn+TimeIn))&" minutes","")

Note that the formula will return #N/A for the first row since there's no prior 'Time In'. If you prefer, you can simply remove/clear the formula from F2, leaving the remaining formulas below as is.

Hope this helps!

 

[mazher]

Extremely thankfull,

This is awesome.

Sir Domenic Rocks,
Mr Excel Forum Rocks

Regards


Mazher

 

[mazher]

Please can someone explain me that when defining the named ranges why its necessary to be in the cell E when defining DateOut and TimeOut and in Cell F2 when defining DateIn and TimeIn?

Regards


Mazher

 

[Domenic]

Please can someone explain me that when defining the named ranges why its necessary to be in the cell E when defining DateOut and TimeOut and in Cell F2 when defining DateIn and TimeIn?

Regards


Mazher

That's because each defined name has a relative reference. This means that the reference changes according to where the formula is located. If the reference for a defined name is absolute, then it doesn't matter where it's defined.