Calculation when starting mid-month

Hi all

Workbook attached

I have made a workbook that my learners use to keep track of their Off-The-Job training. They are expected to complete 20% of their weekly/monthly time as OTJ Training.
The issue is, if a Learner joins me on the 20th of the month, the workbook is expecting the learner to still complete 100% of the months' training time, rather than as a % of the month that remains.
I would also like to be able to do the same for their final month - I am happy to add a cell where if "yes" is entered it is confirmed as their final month, and so calculates the OTJ Hours as a % of this final month. This will not be needed on the first month, as the first will ALWAYS be Month 1, whereas the final month could be anywhere from 12 - 24 months.

I am more than happy for the calculations to be placed in a hidden cell

darryl,

I apologize, but your workbook is kind of confusing, to me anyway, regarding ""what goes where"". for instance, in cell D7 on the sheet front page you have this formula:

Please Login or Register  to view this content.

but yet, in cell c7 there is the value of january 1900, and cell b87 on sheet month 1 is labelled as ""activity"". so you are wanting a date on the first sheet to equal a value for "activity" on the 2nd tab. that doesn't make sense.

furthermore, based on the description of what you're looking for, the layout of the sheets don't really make much sense either. this is just my opinion. can you clarify any of this so folks here can help out?

Originally Posted by vba_php

in cell c7 there is the value of january 1900,

January 1900 is entered just o there is a default month to fill in the months for Column C

Originally Posted by vba_php

cell b87 on sheet month 1 is labelled as ""activity"". so you are wanting a date on the first sheet to equal a value for "activity" on the 2nd tab

I have/will upload another version of the spreadsheet with data entered as a Learner would see it. The "Activities" on Sheet "Month 1" are there (B82:B86) for Data Validation to make a drop-down list, and also to be used to calculate the Pie Chart (in case management want to change the name of each activity)


They might not make much sense as the Original version has 18 x "Month" Worksheets, so that all of the Front Page self-completes.

As you can see from my version, I have started on 20/08/20, and so I should only be needing 30% of the Monthly Hours, but because it works on 100% of the whole month it is still expecting me to give 30 hours training, not just 10.

I'm terribly sorry Darryl, but I'm not really following anything you are saying. That doesn't happen too often for me, so I feel kind of bad. I'm going to have to step out and hope that someone else here can be able to assist you. good luck.

Originally Posted by vba_php

I'm not really following anything you are saying.

No worries. Because I have written the workbook I know exactly where everything is looking to etc.

I have sorted it, but it is quite messy.


F33 =G5 (date entered by Learner as their start date)
H33 =DAY(EOMONTH(F33,0)) (Number of days in given month)
I33 =EOMONTH(F33,0)-F33 (Number of days remaining in given month)
J33 =O10 (Number of hours expected in a "full" month)
K33 =J33*I33/H33 (Expected Hours*Remaining Days/Days in Month)
Therefore, August 10th Start would give 30*21/31=20.05

Now I just have to work out the same, but for the final month - the joys of Excel