Time Calculation

[moorep]

Hello all,

I hope you can assit me with my problem.

I have a column that returns 6/17/2011 8:30 and the next colum returns 6/17/2011 17:00.

I need the hours returned for the 3rd column.

I am trying to calculate the amount of hours between the first column and the second.

Can anyone give me some insight?

Thanks very much.

Regards,

Pamela

 

[Luke M]

=(B2-A2)*24

will give the hrs as an integer.

 

[NARAYANK991]

Hi Pamela ,


If you just subtract one from the other , and format the result cell as TIME ( 13:30:55 ) , you should have 8:30:00 as the result , which is 8 hours and 30 minutes.


Narayan

 

[prasaddn]

how about also custom formating 3rd column with [h] or [h]:mm:ss

 

[moorep]

Thank you all very much for your replies. The subracting worked but here is another question, if I may.

I have two columns of dates. I am trying to figure Training Hours for classes that run over multiple days.

How then would I build a formula to calculate the hours for multiple days.

For example:

8/24/2011 9:00 8/26/2011 16:00

Again I am grateful for your kind assistance.

Regards,

Pamela

 

[vijaySharma]

Pam,


Assuming the daily training duration is for 8 hours.


Cell A1 Contains : 8/24/2011 9:00

Cell B1 Contains : 8/26/2011 16:00


Use this formula in - Cell C1

=NETWORKDAYS(A1,B1)*"08:00"

I have used the Networkdays to calculate the number of working days, this formula also accepts a range where you can define a list of holidays to be excluded from the calculation.


For more details on this funtion refer to the online help of excel.


~VijaySharma

 

[Luke M]

I think vijay's on the right track. One problem with NETWORKDAYS however, is that it counts the current days as 1. Also, it's only going to find whole days, and you need a way to detect actual hours. I think this will work better:

=(B2-A2)-(NETWORKDAYS(A2,B2)-1)*16/24


What it's doing?

First, we find the total difference/time between the two dates. Next, we need to get rid of the extra 16 hrs (assuming an 8 hr working/training time) from each "whole" working day. Thus, we use NETWORKDAYS to find the whole working days, subtract 1 to negate the "count current day" effect. Then, we multiply that by 16/24 (the amount of hrs we want to not count), and subtract that from the difference we found at beginning.