Time calculation (Subraction of Idle Time)

[Guest]

Hi,
Can you please advice me how to calculate the below.., A
B
1 01/03/2007 06:00:00 02/03/2007 07:30:00

The time (hours, mins & sec) taken between B1 to A1 is 25hours & 30 mins.
Now here imagine that i need to exclude the time taken from 02/03/2007
02:00:00 to 02/03/2007 03:30 and arrive the final hourly value as 24hrs and
00 mins!.

In Simple.,
The value of (B1-A1), to be again subtracted by the time between 02/03/2007
02:00:00 to 02/03/2007 03:30 (idle time) and make the final value to be 24hrs.

Can you please adivce me how to arrive to this formula.

 

[Guest]

This is easy. Just subtract 1:30 from the final results. You may need to
format the cell in a different format to get the results your are looking for.

=B1-A1-1:30

The format Menu Number - Time has lots of formats.
You can enter time with hours a minutes into excel. Sunch as
1/3/07 22:30 and excel will automatically convert this time to excel time
format.

 

[Guest]

Hi Joel,
Thanks for your reply ,
Well, the problem is not yet solved, i dont like to formulate / insert 01:30
inbetween the formula and subtract it, instead can we assign or mention that
02/03/2007 02:00:00 to 02/03/2007 03:30 has to be subtracted all the time for
what ever value instered in cell B1 & A1?.
Any possibilities?.
--Ajay--

 

[David Biddulph]

In that case why not put your two extra times in C1 and D1, and change your
formula to =B1-A1-(D1-C1) ?

 

[Guest]

It works only if i mention the below..,
=(B1-A1-(D1-C1)) Equals to 24:00:00 (with the format {hh:mm:ss})
A B
C D
1 01-03-2007 06:00:00 02-03-2007 07:30:00 01/03/2007 02:00:00
01/03/2007 03:30:00

As suggested by you i tried the above, But i would like to imply the time
alone in C1 & D1 as 02:00:00 & 03:30:00 instead of 01/03/2007 02:00:00 in C1
& 01/03/2007 03:30:00.

Can you pls revert on the above as whether we could mention the Idle time
alone instead of dd/mm/yyyy hh:mm:ss?.

Hope you are clear on my above message.
--Ajay--

 

[David Biddulph]

What happened when you tried it?

 

[Guest]

You can add a Name for the 1:30 hours. Go to Insert Menu - Name - Define
Add to the top box Lunchtime
On the bottom box replace the = with 1:30 asnd press k

then if you go to your worksheet add =LunchTime. You may have to format the
cell with Time to get the 1:30 to show in the box. Lunchtime can be add to
any formula.

Simply go to