Formulas for adding and subtracting time

R

Russell

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top