Calculate time

[gustaf111]

Hello,

I wounder if someone could help me ..

I have this problem:

I have two dates, eg:
Start: 15 Sep 2011
Finished: 4 Mar 2012

I would like to spread out 200 h over the period, so I get how many hours per/month 200 will be.

quick tricky ....

Please Help! Gustaf

 

[niceguy_jay]

is this an excel related question or a generic question ??

the difference between the dates is 6 months...

200 hours spread over 6 months comes to 33.33 Hours per month

 

[gustaf111]

I want to use the "WORKDAY" function so you know how many worksdays there are in the month. In my example, there will be less hours in the first and last month because it is not the whole month ....

 

[niceguy_jay]

Its NETWORKDAYS that will give you the working days between two dates...WORKDAY returns the next or previous working day in the form of date that you specify in the formula..

in your case you can use this formula

=(200/NETWORKDAYS(A1,A2))

which will return the hours that you can allocate to each day

 

[gustaf111]

ohh Thanks!

Are there any good fuctions that will display whole hours/per month ?

 

[niceguy_jay]

well...in your case the workdays needs to be calculated into 3 parts

1. For SEPTEMBER 2011 (after 15th Sept)
2. From 1st OCTOBER 2011 to 29th FEBRUARY 2010
3. FOR MARCH 2012 (before 4 March 2012)


suppose your 2 dates are in cell A1 and A2 then

1. =NETWORKDAYS(A1,EOMONTH(A1,0)) will give you workdays for the first case
2. =NETWORKDAYS(EOMONTH(A1,0)+1,EOMONTH(A2,-1)) will give you workdays for the second case
3. =NETWORKDAYS(EOMONTH(A2,-1)+1,A2) will give you workdays for the third case

now you can multiply each of the above cases with =(200/NETWORKDAYS(A1,A2)) and will give you the hours for the three cases

summing up the three will give 200 hours...

 

[gustaf111]

Thanks you very much!!!

 

[barry houdini]

You could automate this a little in order to use a single formula.....

Assume you have the following:

A2 = 15-Sep-2011
B2 = 4-Mar-2012
C2 = 200

Now in D1 across you need to put the first of each month, e.g. in D1 1-Sep-2011 and then this formula in E1 copied across to get each subsequent month

=EDATE(D1,1)

Format row 1 as mmm-yy

Now in D2 use this formula copied across

=$C2*MAX(0,NETWORKDAYS(MAX($A2,D$1),MIN($B2,EOMONTH(D$1,0))))/NETWORKDAYS($A2,$B2)

format as number with 2 decimal places