Hi,
I have a critical question..,pls check the below and advice me..,

I have a cell A1 consisting of date (Imagine its: 15th March 2007) and B1 &
B2 consisting of time (Imagine its: start time 09:15am and B2 with end time:
05:00pm)
I just need a formula to say the time taken from 09:15am to 05:00pm, and if
the time crosses 05:00pm and lies up to next day 08:59am, i should not
calculate.
Is there any possibility to create a formula for this?.

Pls advice..,
__Ajay__

this will calculate time difference (in hours/minutes) with maximum finish
time of 17:00. I wasn't clear on what you meant by "lies up to next day
08:59am"

=MIN(TIME(17,0,0),B2)-B1

"Ajay" wrote:

Hi,
I have a critical question..,pls check the below and advice me..,

I have a cell A1 consisting of date (Imagine its: 15th March 2007) and B1 &
B2 consisting of time (Imagine its: start time 09:15am and B2 with end time:
05:00pm)
I just need a formula to say the time taken from 09:15am to 05:00pm, and if
the time crosses 05:00pm and lies up to next day 08:59am, i should not
calculate.
Is there any possibility to create a formula for this?.

Pls advice..,
__Ajay__

Hi Topper,
The below is in brief..,Pls check and adivce..,
Imagine that an XL has to calculate times based on the working hours..,

Lets fix the working hours as 09:00am to 05:00pm, So if a person works from
08.30am to 04:30pm, the working hours has to flash the value as 07:30hrs (as
the sheet has to calculate from 09:00am-04:30pm)!.
And if the person has worked from 07:00am to 08.59am then the value should
be 0 hours worked.
Hope iam clear..,Can you please advice me with a formula if iam clear..,
Pls revert if you require elaborations.
__Ajay__.
"Toppers" wrote:

this will calculate time difference (in hours/minutes) with maximum finish
time of 17:00. I wasn't clear on what you meant by "lies up to next day
08:59am"

=MIN(TIME(17,0,0),B2)-B1

"Ajay" wrote:

Hi,
I have a critical question..,pls check the below and advice me..,

I have a cell A1 consisting of date (Imagine its: 15th March 2007) and B1 &
B2 consisting of time (Imagine its: start time 09:15am and B2 with end time:
05:00pm)
I just need a formula to say the time taken from 09:15am to 05:00pm, and if
the time crosses 05:00pm and lies up to next day 08:59am, i should not
calculate.
Is there any possibility to create a formula for this?.

Pls advice..,
__Ajay__

With your additional explanation, Toppers formula would become:

=MIN(TIME(17,0,0),B2)-MAX(TIME(9,0,0),B1)

--
Regards,
Fred


"Ajay" wrote in message
...
Hi Topper,
The below is in brief..,Pls check and adivce..,
Imagine that an XL has to calculate times based on the working hours..,

Lets fix the working hours as 09:00am to 05:00pm, So if a person works from
08.30am to 04:30pm, the working hours has to flash the value as 07:30hrs (as
the sheet has to calculate from 09:00am-04:30pm)!.
And if the person has worked from 07:00am to 08.59am then the value should
be 0 hours worked.
Hope iam clear..,Can you please advice me with a formula if iam clear..,
Pls revert if you require elaborations.
__Ajay__.
"Toppers" wrote:

this will calculate time difference (in hours/minutes) with maximum finish
time of 17:00. I wasn't clear on what you meant by "lies up to next day
08:59am"

=MIN(TIME(17,0,0),B2)-B1

"Ajay" wrote:

Hi,
I have a critical question..,pls check the below and advice me..,

I have a cell A1 consisting of date (Imagine its: 15th March 2007) and B1 &
B2 consisting of time (Imagine its: start time 09:15am and B2 with end
time:
05:00pm)
I just need a formula to say the time taken from 09:15am to 05:00pm, and if
the time crosses 05:00pm and lies up to next day 08:59am, i should not
calculate.
Is there any possibility to create a formula for this?.

Pls advice..,
__Ajay__

Hi Fred,
Your formula was usefull and it brought out the output sucessfully.

In a similar way, i have another query, which is below..,

Imagine the below..,
If H1 has 11-03-2007 & I1 has 14:07
and
If H2 has 12-03-2007 & I2 has 14:10

I would like to have a formula with the below conditions..,
1. If i subtract H2, I2 with H1, I1 i should get the output as 08 hrs:03 mins.
This 08hrs & 03 mins is to be calculated this way:-
On 11th March the time ran from 14:07 to 17:00 is 02hrs & 53mins
and for the next day 12th March the time consumed is from 09:00 to 14:10 is
05hrs & 10 mins
So the final time consumed is 08hrs & 03mins (between 11-03-2007 14:07 to
12-03-2007 14:10).

Pls advice whether i would be able to cut down the time consumption from
11th March 17:01 to 12th March 08:59 using single formula.

Pls advice.
__Ajay__
"Fred Smith" wrote:

With your additional explanation, Toppers formula would become:

=MIN(TIME(17,0,0),B2)-MAX(TIME(9,0,0),B1)

--
Regards,
Fred


"Ajay" wrote in message
...
Hi Topper,
The below is in brief..,Pls check and adivce..,
Imagine that an XL has to calculate times based on the working hours..,

Lets fix the working hours as 09:00am to 05:00pm, So if a person works from
08.30am to 04:30pm, the working hours has to flash the value as 07:30hrs (as
the sheet has to calculate from 09:00am-04:30pm)!.
And if the person has worked from 07:00am to 08.59am then the value should
be 0 hours worked.
Hope iam clear..,Can you please advice me with a formula if iam clear..,
Pls revert if you require elaborations.
__Ajay__.
"Toppers" wrote:

this will calculate time difference (in hours/minutes) with maximum finish
time of 17:00. I wasn't clear on what you meant by "lies up to next day
08:59am"

=MIN(TIME(17,0,0),B2)-B1

"Ajay" wrote:

Hi,
I have a critical question..,pls check the below and advice me..,

I have a cell A1 consisting of date (Imagine its: 15th March 2007) and B1 &
B2 consisting of time (Imagine its: start time 09:15am and B2 with end
time:
05:00pm)
I just need a formula to say the time taken from 09:15am to 05:00pm, and if
the time crosses 05:00pm and lies up to next day 08:59am, i should not
calculate.
Is there any possibility to create a formula for this?.

Pls advice..,
__Ajay__

Well,

Modifying Fred's modification of Toppers formula <g

try:

=MIN(H2+I2,H2+TIME(15,0,0))-MAX(H1+I1,H2+TIME(9,0,0))+IF(H2H1,TIME(17,0,0)-I1,0)

Of course it will only work for the same day or two days. If the dates are
more then that apart then it will ignore all extra intervening days.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Ajay" wrote in message
...
Hi Fred,
Your formula was usefull and it brought out the output sucessfully.

In a similar way, i have another query, which is below..,

Imagine the below..,
If H1 has 11-03-2007 & I1 has 14:07
and
If H2 has 12-03-2007 & I2 has 14:10

I would like to have a formula with the below conditions..,
1. If i subtract H2, I2 with H1, I1 i should get the output as 08 hrs:03
mins.
This 08hrs & 03 mins is to be calculated this way:-
On 11th March the time ran from 14:07 to 17:00 is 02hrs & 53mins
and for the next day 12th March the time consumed is from 09:00 to 14:10
is
05hrs & 10 mins
So the final time consumed is 08hrs & 03mins (between 11-03-2007 14:07 to
12-03-2007 14:10).

Pls advice whether i would be able to cut down the time consumption from
11th March 17:01 to 12th March 08:59 using single formula.

Pls advice.
__Ajay__
"Fred Smith" wrote:

With your additional explanation, Toppers formula would become:

=MIN(TIME(17,0,0),B2)-MAX(TIME(9,0,0),B1)

--
Regards,
Fred


"Ajay" wrote in message
...
Hi Topper,
The below is in brief..,Pls check and adivce..,
Imagine that an XL has to calculate times based on the working hours..,

Lets fix the working hours as 09:00am to 05:00pm, So if a person works
from
08.30am to 04:30pm, the working hours has to flash the value as
07:30hrs (as
the sheet has to calculate from 09:00am-04:30pm)!.
And if the person has worked from 07:00am to 08.59am then the value
should
be 0 hours worked.
Hope iam clear..,Can you please advice me with a formula if iam
clear..,
Pls revert if you require elaborations.
__Ajay__.
"Toppers" wrote:

this will calculate time difference (in hours/minutes) with maximum
finish
time of 17:00. I wasn't clear on what you meant by "lies up to next
day
08:59am"

=MIN(TIME(17,0,0),B2)-B1

"Ajay" wrote:

Hi,
I have a critical question..,pls check the below and advice me..,

I have a cell A1 consisting of date (Imagine its: 15th March 2007)
and B1 &
B2 consisting of time (Imagine its: start time 09:15am and B2 with
end
time:
05:00pm)
I just need a formula to say the time taken from 09:15am to 05:00pm,
and if
the time crosses 05:00pm and lies up to next day 08:59am, i should
not
calculate.
Is there any possibility to create a formula for this?.

Pls advice..,
__Ajay__