Determine actual time between 2 Date and 2 Time fields

[laurastreng]

Hi-
I have a spreadsheet that has the following columns (in addition to others):
Request Date: 04/12/2011
Request Hr: 9:19:40 AM
Decision Date: 04/13/2011
Decision Hr: 8:03:06 AM

I have another column that determines the number of calendar days between the two date fields.

When the # of calendar days between the two date fields = 1, I need to determine whether the time difference between Request Hr and Decision Hr is <= 30 minutes past 24 hours.

I have over 280 rows in one spreadsheet where the calendar day count is 1. I have not been successful at coming up with an "If" statement to capture all of the possible scenarios. I even tried to see if converting the time values to decimals would work - not quite (for me, anyway!)

Here are some more examples of the values. I have a column that determines the time difference between the two time columns. Right now, I look at the two date fields, then look to see if the REQ_HR is > DEC_HR, then if not, what the time difference is. This is a monthly process and I have three long worksheets of data to apply this to every month. I am cross-eyed now

D_REQUEST REQ_HR D_DECISION DEC_HR Time Calendar Days
4/12/2011 9:19:40 4/13/2011 8:03:06 1:16:34 1
4/12/2011 11:35:51 4/13/2011 8:41:22 2:54:29 1
4/6/2011 15:25:35 4/7/2011 10:51:09 4:34:26 1
3/31/2011 12:20:29 4/1/2011 12:21:32 0:01:03 1
3/31/2011 6:47:37 4/1/2011 13:30:10 6:42:33 1
3/31/2011 6:49:54 4/1/2011 9:46:40 2:56:46 1
4/7/2011 8:11:36 4/8/2011 8:12:10 0:00:34 1
3/31/2011 13:36:28 4/1/2011 13:37:59 0:01:31 1
3/31/2011 11:23:48 4/1/2011 16:07:56 4:44:08 1
3/31/2011 12:11:05 4/1/2011 6:48:53 5:22:12 1
3/31/2011 12:36:14 4/1/2011 12:34:46 0:01:28 1
3/31/2011 14:31:26 4/1/2011 16:09:17 1:37:51 1
3/31/2011 13:42:57 4/1/2011 13:47:25 0:04:28 1
3/31/2011 15:04:50 4/1/2011 15:28:27 0:23:37 1
3/31/2011 15:27:38 4/1/2011 16:13:21 0:45:43 1
3/31/2011 15:55:00 4/1/2011 16:14:29 0:19:29 1
4/20/2011 14:54:48 4/21/2011 14:55:18 0:00:30 1
4/4/2011 9:13:12 4/5/2011 15:42:13 6:29:01 1
4/4/2011 11:04:18 4/5/2011 11:08:21 0:04:03 1

Thank you in advance for your help!

Laura
Richmond, VA

 

[Ruddles]

Dates and times are stored in Excel as the number of days from an arbitrary point in the past. The number can have a fractional part: the date (or number of days) is the bit to the left of the decimal point whilst the bit to the right represents a fraction of a day, so if the number is anything.5, that's midday.

I think a good starting point is to subtract (Request Date + Request Hr) from (Decision Date + Decision Hr). If your dates and times are in columns A-D of row 2 (for example), then this formula in E2 will give the number of days between the Request and the Decision:-

=C2+D2-A2-B2

The complete number of days will therefore be =INT(E2) and the fractional part of the day will be =E2-INT(E2). To convert the fractional part of the day to hours or minutes, use =(E2-INT(E2))*24 and =(E2-INT(E2))*1440 respectively.

Does this move you forwards at all?