Date Calculations

[Chris Pringle]

Hi - We are monitoring arrival and departure times against planned arrival
and departure times. We are using a spreadsheet to do this and want to show
the number of minutes early or late, but for some reason we cannot seem to
do this. Is there a way to do it?

Thanks

Chris.

 

[Rick Rothstein]

Do you mean early/late for arrivals and early/late for departures
separately, or are you thinking of some combination of them? Assuming you
meant separately, and assuming your entries are real Excel times, just
subtract the appropriate columns and then custom format the calculated cells
using this pattern in the Type field... [m] (or use [m].0 or [m].00 if you
want decimal minutes attached to the whole number of minutes).

 

[Socko]

ARRIVAL DEPARTURE
PLAN ACT PLAN ACT
Person0 8:12 AM 8:26 AM Late 0:14:00 7:39 AM 8:06 AM Late 0:27:00
Person1 6:43 AM 6:49 AM Late 0:06:00 5:41 AM 6:07 AM Late 0:26:00
Person2 4:51 AM 4:59 AM Late 0:08:00 3:48 AM 4:03 AM Late 0:15:00
Person3 4:45 AM 4:41 AM Early 0:04:00 5:48 AM 6:21 AM Late 0:33:00
Person4 1:13 AM 1:23 AM Late 0:10:00 4:17 AM 4:44 AM Late 0:27:00
Person5 1:43 AM 1:59 AM Late 0:16:00 5:17 AM 5:28 AM Late 0:11:00
Person6 2:17 AM 2:49 AM Late 0:32:00 12:43 PM 12:55 PM Late 0:12:00
Person7 12:11 PM 11:52 AM Early 0:19:00 2:52 AM 2:59 AM Late 0:07:00
Person8 8:04 AM 7:30 AM Early 0:34:00 4:49 AM 5:10 AM Late 0:21:00
Person9 2:33 AM 2:15 AM Early 0:18:00 2:09 AM 2:11 AM Late 0:02:00
Person10 9:43 AM 9:51 AM Late 0:08:00 1:11 AM 1:26 AM Late 0:15:00
Person11 10:58 AM 11:19 AM Late 0:21:00 1:11 AM 1:19 AM Late 0:08:00
Person12 12:12 PM 11:44 AM Early 0:28:00 2:29 AM 2:55 AM Late 0:26:00
Person13 4:53 AM 4:55 AM Late 0:02:00 7:19 AM 7:46 AM Late 0:27:00
Person14 2:11 AM 2:45 AM Late 0:34:00 4:58 AM 5:25 AM Late 0:27:00

You may use the folowing formula to find if late or early

=IF(C3>B3,"Late","Early")


and to find out how late or early, you can use the formula as follows
=IF(C3>B3,C3-B3,B3-C3)

After inserting the formula, reformat the cells to time and choose
subtype of time format as "37:30:55".

You may download the file with example...
http://www.sockofiles.350.com/monitorarrdep.xls


Selva V Pasupathy
Visit: http://socko.wordpress.com/

 

[Chris Pringle]

Thanks Rick, I did mean early/late for both but treating them spearately.

I will try it.

Do you mean early/late for arrivals and early/late for departures
separately, or are you thinking of some combination of them? Assuming you
meant separately, and assuming your entries are real Excel times, just
subtract the appropriate columns and then custom format the calculated
cells using this pattern in the Type field... [m] (or use [m].0 or [m].00
if you want decimal minutes attached to the whole number of minutes).

--
Rick (MVP - Excel)

Hi - We are monitoring arrival and departure times against planned
arrival and departure times. We are using a spreadsheet to do this and
want to show the number of minutes early or late, but for some reason we
cannot seem to do this. Is there a way to do it?

Thanks

Chris.