ExcelBanter - Time calculation

Hi Sandy & Toppers,
Thanks for your support all the time...,
I really have a feel that there's a formula that would sort out my
queries..,I would try and share the same with you as well..,
I hope that its possible with IF condition..,
Before trying out from my side, i would like to know about this site..,Is
this an Microsoft owned / aided one?., Where are you all located?. I meant
which country / province?.
Pls revert.
--Ajay--

"Sandy Mann" wrote:

Hi Toppers,

Yes I must own up to being a bit of an anorak. Still it is not as bad as
when I first started reading these NG's because back then I did not have XL
on my computer and if I wanted to try something out I had to drive into work
and test it there. More than once I was driving home and going over what I
had been doing in my head when something would occur to me. I would have to
turn around and go back to work to try it out otherwise it would be going
round and round in my head all night!

I intended no offence in jumping in - I usually feel that it is bad form to
jump all over someone else's formula and normally if I work out an answer
offline I go online to see if someone else has posted an answer. If they
have I usually only post if I feel that my solution sufficiently different
to warrant it. Consequently some of my best work is in the dustbin. <g

--
Regards,

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

with @tiscali.co.uk

"Toppers" wrote in message
...
Sandy,
What a sad lot we (or at least some of us) are !; I also find
myself on the computer at odd (wee) hours of the morning with Excel
problems
in my head.

Anyway, thanks for answering the queries.

Hope you slept well.

"Sandy Mann" wrote:

Hi Ajay,

I found it impossible to get to sleep with the problem rattling around in
my
brain.

Try:

=(H2-H1+1)*TIME(8,0,0)-(I1-TIME(9,0,0))-(TIME(17,0,0)-I2)-((WEEKDAY(H1,2)WEEKDAY(H2,2))*16/24)

This returns 16:03 for the dates and times you gave. As the answer could
possibly be greater than 24 hours custom format the cell as [hh]:mm

As Samuel Pepys said, "And so to bed"

--
HTH

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

with @tiscali.co.uk

"Sandy Mann" wrote in message
...
Hi Ajay,

The said formula was able to calculate and throw me the output, But it
was
not exact, it says 05hrs & 10mins, instead of 08hrs & 03mins

For the dates and times that you gave I get 8:03 as requested. Are you
sure that the date in H1 is a real date and not text?

As to your new request it is too late at night here, (12:30 AM). If
you
don't get any other responses I will have a look at it tomorrow.
--
Regards,

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

with @tiscali.co.uk

"Ajay" wrote in message
...
Hi Sandy,
Thanks for your response,
The said formula was able to calculate and throw me the output, But it
was
not exact, it says 05hrs & 10mins, instead of 08hrs & 03mins. I feel
that
it
has calculated for 12th March alone!!. However thanks for the same..,
I have a new complicated case like the below..,pls check and advice..,

In a similar way if H1 & H2 are replaced with dates as below..,
If H1 has 16-03-2007 & I1 has 14:07
and
If H2 has 20-03-2007 & I2 has 14:10

The final output has to be: 16:03.
The time consumption has to be calculated for 16 & 20th alone, as 17th
&
18th March are Saturday & Sunday.
Time consumed on 16th March between 09:00 to 17:00hrs is 02hrs &
53mins
(from 14:07 to 17:00)
Time consumed on 17th March between 09:00 to 17:00hrs is 0hrs as its
Saturday
Time consumed on 18th March between 09:00 to 17:00hrs is 0hrs as its
Sunday
Time consumed on 19th March between 09:00 to 17:00hrs is 08hrs &
00mins
(from 09:00 to 17:00)
Time consumed on 20th March between 09:00 to 17:00hrs is 05hrs &
10mins
(from 09:00 to 14:10)

Can you tell me a formula with 'IF' condition also (also with MAX &
MIN
function), Please assist.
__Ajay__

"Sandy Mann" wrote:

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__