Hi Every One,
Hope some one help me out to sort out said below requirements.

I need to calculate total numbers of hours and minutes from two different
dates to calculate the %age of one week, like....Qust-1

Starting Date(A1= 10-Sept-07), Starting Time(B1=9:00), Ending
Date(C1=12-Sept-07), Ending Time(D1=9:00)----result should be 72 hrs, 4320
minutes, and weekly %age should be (24*7*60=10080 mnts in a
week)--(10080-4320)*100/10080=57.14%---if i m not worng.

Qust-2:-
Starting Date(A1= 10-Sept-07), Starting Time(B1=9:00), Ending
Date(C1=12-Sept-07), Ending Time(D1=14:00)----result should be 77 hrs, 4620
minutes, and weekly %age should be--(10080-4620)*100/10080=54.16%

Qust#3:-
Starting Date(A1= 10-Sept-07), Starting Time(B1=10:00), Ending
Date(C1=12-Sept-07), Ending Time(D1=9:10)

Waiting for Prompt reply by someone.

Ali
--
Not Even The Wise Can See All Ends --- Tolkien

=((C1+D1)-(A1+B1))/7

In your examples, the first calculation is 48 hours not 72.

And the times represent USAGE, then your calculation is incorrect - why did
you subtract the usage from minutes in a week which will give "non-usage"
time?

using your figures:

% usage=4320/10080 with cell formatted as %

HTH

"Ali" wrote:

> Hi Every One,
> Hope some one help me out to sort out said below requirements.
>
> I need to calculate total numbers of hours and minutes from two different
> dates to calculate the %age of one week, like....Qust-1
>
> Starting Date(A1= 10-Sept-07), Starting Time(B1=9:00), Ending
> Date(C1=12-Sept-07), Ending Time(D1=9:00)----result should be 72 hrs, 4320
> minutes, and weekly %age should be (24*7*60=10080 mnts in a
> week)--(10080-4320)*100/10080=57.14%---if i m not worng.
>
> Qust-2:-
> Starting Date(A1= 10-Sept-07), Starting Time(B1=9:00), Ending
> Date(C1=12-Sept-07), Ending Time(D1=14:00)----result should be 77 hrs, 4620
> minutes, and weekly %age should be--(10080-4620)*100/10080=54.16%
>
> Qust#3:-
> Starting Date(A1= 10-Sept-07), Starting Time(B1=10:00), Ending
> Date(C1=12-Sept-07), Ending Time(D1=9:10)
>
> Waiting for Prompt reply by someone.
>
> Ali
> --
> Not Even The Wise Can See All Ends --- Tolkien
>

Nel post:6B133CF4-C1FD-4CE3-B40A-(E-Mail Removed),
Ali <(E-Mail Removed)> ha scritto:
> Hi Every One,
> Hope some one help me out to sort out said below requirements.
>
> I need to calculate total numbers of hours and minutes from two
> different dates to calculate the %age of one week, like....Qust-1
>
> Starting Date(A1= 10-Sept-07), Starting Time(B1=9:00), Ending
> Date(C1=12-Sept-07), Ending Time(D1=9:00)----result should be 72 hrs,
> 4320 minutes, and weekly %age should be (24*7*60=10080 mnts in a
> week)--(10080-4320)*100/10080=57.14%---if i m not worng.
>
> Qust-2:-
> Starting Date(A1= 10-Sept-07), Starting Time(B1=9:00), Ending
> Date(C1=12-Sept-07), Ending Time(D1=14:00)----result should be 77
> hrs, 4620 minutes, and weekly %age should
> be--(10080-4620)*100/10080=54.16%
>
> Qust#3:-
> Starting Date(A1= 10-Sept-07), Starting Time(B1=10:00), Ending
> Date(C1=12-Sept-07), Ending Time(D1=9:10)
>
> Waiting for Prompt reply by someone.
>
> Ali

Hi Ali,

you have to remember that Excel counts time in day, so the solution to your
problem should be:

in E1: =(1+(C1+D1)-(A1+B1))*24 to have the number of hours;
in F1: =E1*60 to have the number of minutes;
in G1: =(24*7*60-F1)*100/(24*7*60) to have the weekly percentage.




--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy

Hi Franz Verga,
Thx for ur help, its really solve my prb, but one thing which i want to ask,
why u add 1+ ?
--
Not Even The Wise Can See All Ends --- Tolkien



"Franz Verga" wrote:

> Nel post:6B133CF4-C1FD-4CE3-B40A-(E-Mail Removed),
>
> Hi Ali,
>
> you have to remember that Excel counts time in day, so the solution to your
> problem should be:
>
> in E1: =(1+(C1+D1)-(A1+B1))*24 to have the number of hours;
> in F1: =E1*60 to have the number of minutes;
> in G1: =(24*7*60-F1)*100/(24*7*60) to have the weekly percentage.
>
>
>
>
> --
> (I'm not sure of names of menus, options and commands, because
> translating from the Italian version of Excel...)
>
> Hope I helped you.
>
>
> Thanks in advance for your feedback.
>
>
> Ciao
>
>
> Franz Verga from Italy
>
>

Hi Toppers,
Thx for update regarding calculation mistake, i just want to calculate the
system availability in a week, with reference to the up/down time in a week.
--
Not Even The Wise Can See All Ends --- Tolkien



"Toppers" wrote:

> =((C1+D1)-(A1+B1))/7
>
> In your examples, the first calculation is 48 hours not 72.
>
> And the times represent USAGE, then your calculation is incorrect - why did
> you subtract the usage from minutes in a week which will give "non-usage"
> time?
>
> using your figures:
>
> % usage=4320/10080 with cell formatted as %
>
> HTH
>
> "Ali" wrote:
>
> > Hi Every One,
> > Hope some one help me out to sort out said below requirements.
> >
> > I need to calculate total numbers of hours and minutes from two different
> > dates to calculate the %age of one week, like....Qust-1
> >
> > Starting Date(A1= 10-Sept-07), Starting Time(B1=9:00), Ending
> > Date(C1=12-Sept-07), Ending Time(D1=9:00)----result should be 72 hrs, 4320
> > minutes, and weekly %age should be (24*7*60=10080 mnts in a
> > week)--(10080-4320)*100/10080=57.14%---if i m not worng.
> >
> > Qust-2:-
> > Starting Date(A1= 10-Sept-07), Starting Time(B1=9:00), Ending
> > Date(C1=12-Sept-07), Ending Time(D1=14:00)----result should be 77 hrs, 4620
> > minutes, and weekly %age should be--(10080-4620)*100/10080=54.16%
> >
> > Qust#3:-
> > Starting Date(A1= 10-Sept-07), Starting Time(B1=10:00), Ending
> > Date(C1=12-Sept-07), Ending Time(D1=9:10)
> >
> > Waiting for Prompt reply by someone.
> >
> > Ali
> > --
> > Not Even The Wise Can See All Ends --- Tolkien
> >

Nel post:A1FB4C7C-2756-4201-B1A5-(E-Mail Removed),
Ali <(E-Mail Removed)> ha scritto:
> Hi Franz Verga,
> Thx for ur help, its really solve my prb, but one thing which i want
> to ask, why u add 1+ ?
>

Because, otherwise, Excel will count only two days: Excel doesn't count the
starting day.



--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy