Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i Calculate minutes between two different dates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i Calculate minutes between two different dates
=((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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i Calculate minutes between two different dates
Nel ,
Ali 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i Calculate minutes between two different dates
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 , 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i Calculate minutes between two different dates
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i Calculate minutes between two different dates
Nel ,
Ali 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate minutes from timecard | Excel Worksheet Functions | |||
how do I calculate minutes when given two times? | Excel Worksheet Functions | |||
HOW TO CALCULATE HOURS WITH MINUTES AND SECONDS? | Excel Discussion (Misc queries) | |||
how do i calculate minutes between two times? | Excel Discussion (Misc queries) | |||
calculate average hours and minutes | Excel Worksheet Functions |