Formulas for adding and subtracting time

[Guest]

I have a job where I track TIME

It multiple agencies to accomplish a job

If the job was to be completed at 2pm then each agency completes its portion of the job "X" hours and minutes prior to
2p

with that said consider this: cell c-20 I enter 2pm, the 5 agencys' jobs are completed in series 1hr15min prior to the las
like thi

5 07:45
4 09:00
3 10:15
2 11:30
1 12:45
complete 14:00

in the next collum I will enter the "Actual" tome everything was complete

5 07:45 06:50
4 09:00 08:45
3 10:15 10:10
2 11:30 11:30
1 12:45 13:00
complete 14:00 14:20

In this example some are early, late and on time .... HERE is where i need help! the next collum (presumably "E") I want
D collumn to subtract C collum and give the results in hours and minutes early or late

MY problem is i cannot get excel to ignore the date (even when formatted as time) Also i have difficulty when time backs up past midnight (say the job needed to be completed by 2am) so here is what i am hoping to find out how to make it look

5 07:45 06:50 -00:5
4 09:00 08:45 -00:1
3 10:15 10:10 -00:05 or at least something very clos
2 11:30 11:30 00:00
1 12:45 13:00 +00:15 Thank
complete 14:00 14:20 +00:20 RUS

 

[Biff]

Hi Russ,

To get the format that you want, you would need to change
to the 1904 Date Sys. With this option, you can display
negative times, otherwise you would get ######## as the
result. Goto Tools>Options>Calculation>1904 Date System.

Format the results as [h]:mm.

To account for times that roll past midnight use a formula
like this:

=B4-A4+(A4>B4)

Where B4 is the actual time completed.

Biff

-----Original Message-----
I have a job where I track TIME:

It multiple agencies to accomplish a job.

If the job was to be completed at 2pm then each agency

completes its portion of the job "X" hours and minutes
prior to

2pm

with that said consider this: cell c-20 I enter 2pm,

the 5 agencys' jobs are completed in series 1hr15min prior
to the last

like this

5 07:45
4 09:00
3 10:15
2 11:30
1 12:45
complete 14:00


in the next collum I will enter the "Actual" tome everything was completed

5 07:45 06:50
4 09:00 08:45
3 10:15 10:10
2 11:30 11:30
1 12:45 13:00
complete 14:00 14:20


In this example some are early, late and on time ....

HERE is where i need help! the next collum
(presumably "E") I want

D collumn to subtract C collum and give the results in

hours and minutes early or late.

MY problem is i cannot get excel to ignore the date (even

when formatted as time) Also i have difficulty when time
backs up past midnight (say the job needed to be completed
by 2am) so here is what i am hoping to find out how to
make it look.

5 07:45 06:50 -00:55
4 09:00 08:45 -00:15
3 10:15 10:10 -00:05 or at least something very close
2 11:30 11:30 00:00
1 12:45 13:00

+00:15 Thanks

complete 14:00 14:20

+00:20 RUSS