Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
detecting idle time | Excel Discussion (Misc queries) | |||
Time calculation | Excel Worksheet Functions | |||
Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
time calculation with military time | Excel Worksheet Functions | |||
Time Calculation | Excel Worksheet Functions |