# Complex Formula for Date and time calculation

#### mazher

##### Active Member
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

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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)

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

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.

Regards

Mazher

Still waiting for somene to help me on this issue

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

1) Define the following...

Code:
``````Select/click cell E2

Insert > Name > Define

Name:  DateOut

Refers to:

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

Name:  TimeOut

Refers to:

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

Click Ok``````

2) Try the following...

Code:
``````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!

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))

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

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

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

Regards

Mazher

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?

Replies
3
Views
498
Replies
2
Views
219
Replies
2
Views
159
Replies
3
Views
544
Replies
17
Views
2K

1,221,523
Messages
6,160,322
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back