Progressive calculation based on date and months Update required

[Christopherdj]

RE the attached worksheet.

The formula is in (in Column M – adjusted hours) to calculate progressive payments based on the current day/month/year entered at $O$2 Vs the start month (Column D) Vs the end month (Column E) but it won’t calculate based on a last year start date (2011) and a current year end (2012).

The formula is

Please Login or Register  to view this content.

I require the formula to calculate a pro-rata payment based on the date entered at $O$2 using the month and year start (Column D) against the end date month and year (Column E).

There also seems to be a problem when it is the same month/year calculation, see row 3 outcome. When you place a February date at O2, it calculates zero, but when you place a March date, it calculates the full 80 hours. It should calculate 80 hours with a February Date. So the formula seems to be a “1” month calculation behind.

The original post is located here: TITLED - Progressive calculation based on date and months

Hoping you can help,
regards Chris

[rylo]

Chris

OK, try this one.
M2:

Please Login or Register  to view this content.

rylo

[Christopherdj]

Hi Rylo
Formula is not calculating correctly. If you place 31/12/2011 into $O$2, the calculation in row 2 shows 20 hours.

The methondology for the outcome is October 2011 to March 2012 is 6 (active) months.
Available hours to claim is 50 (which is the maximum allowable as shown in Column L)
The formula needs to divide the 50 hours available by the 6 active months = 8.333333
When entering the Current Date in $O$2 as 31/12/2011 the total should be 3 x 8.333333 (25 - when rounded)
I also notice if I change the Current Date to 01/01/2012, the amount doesn't change - it doesn't recalculate.

This seems like a real curly one...... would it be easier to calculate by days? I have no idea, just thought I'd throw that one in...
Kindest regards
Chris

[daddylonglegs]

Does this one work for you?

=IF(OR(G2<>"ST",$O$2<D2),"",MIN(L2,L2/(DATEDIF(D2-DAY(D2)+1,E2,"m")+1)*(DATEDIF(D2-DAY(D2)+1,O$2,"m")+1)))

[Christopherdj]

Yes it does, thank you so much.

Does this one work for you?

=IF(OR(G2<>"ST",$O$2<D2),"",MIN(L2,L2/(DATEDIF(D2-DAY(D2)+1,E2,"m")+1)*(DATEDIF(D2-DAY(D2)+1,O$2,"m")+1)))