Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 43
Default Time calculation (Subraction of Idle Time)

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Time calculation (Subraction of Idle Time)

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.

"Ajay" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 43
Default Time calculation (Subraction of Idle Time)

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--

"Joel" wrote:

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.

"Ajay" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Time calculation (Subraction of Idle Time)

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

"Ajay" wrote in message
...
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--

"Joel" wrote:

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.

"Ajay" wrote:

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.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 43
Default Time calculation (Subraction of Idle Time)

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" wrote:

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

"Ajay" wrote in message
...
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--

"Joel" wrote:

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.

"Ajay" wrote:

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.






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Time calculation (Subraction of Idle Time)

What happened when you tried it?
--
David Biddulph

"Ajay" wrote in message
...
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" wrote:

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


"Ajay" wrote in message
...
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?.


"Joel" wrote:

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.


"Ajay" wrote:

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.



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Time calculation (Subraction of Idle Time)

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

"Ajay" wrote:

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--

"Joel" wrote:

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.

"Ajay" wrote:

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
detecting idle time salmosalar Excel Discussion (Misc queries) 0 February 2nd 07 06:25 PM
Time calculation rkemper Excel Worksheet Functions 1 December 22nd 05 07:36 PM
Ignoring Time in a Date Time Calculation nmp Excel Worksheet Functions 3 November 23rd 05 09:32 PM
time calculation with military time Ron Thetford Excel Worksheet Functions 8 July 29th 05 07:24 PM
Time Calculation poly2 Excel Worksheet Functions 4 February 15th 05 01:05 AM


All times are GMT +1. The time now is 02:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"