calculating time

[Guest]

I have 2 questions. First, I'm looking for help in calculating the amount of
time (in hours) for a transaction. The user enters the start date, start
time, end date, and end time. Using these four variables, I need to
calculate the amount of hours.

Secondly, I would also like calculate a utilization rate. For example, a
transaction begins on the 11/15/05 and ends on 11/19/05. The total amount of
time is for 80 hours (or 4 days). Anything over 4 hours is credited as a
full day. Therefore, this unit gets credit for 11/15, 11/16, 11/17, and
11/18. I'm having trouble determining how to do this since transactions can
cross months as well. I only have to figure out the utilization for the
previous month and the current month.

And I might have 100 units at each location so it gets a little more complex
to keep track of.

Any help with these two questions would be greatly appreciated. Thanks
again for all of the help....

 

[Guest]

Although dates seem 'really cool' when displayed in Excel, they are actually
incredibly simple. ALL dates are stored as 'real' numbers behind the scenes.

Try typing in '11/30/2005' and 12/1/2005 in two cells. then change their
formats to comma. you'll see the actual values are 38686 and 38687
respectively. So you can just subtract one from the other.

Time works the same way, it's just a percentage though.
38686.5 = 11/30/2005 12:00pm
38686.25 = 11/30/2005 6 am.

Knowing that, you can use simple math to figure out dates..

*** if you type in 11/30/2005 in A1 and 12/01/2005 in B1
In cell C1 type in =B1-A1... Format that cell to be in comma and the answer
will be 1.

 

[Guest]

Thanks for the info but I really need to calculate the number of hours for
each transaction. 4 - 24 hours constitues a whole day. 1 - 3 does not get
counted as a day; which I think would happen if the start date / time was
11/15 11:00 PM and the end date / time was 11/16 2:00 AM. Thanks for help.