I am attempting to help out a coworker with at least an idea of how to approach this problem.

Shown below is the data that was given to me. It contains the start and end dates of a job, hours worked, and if the job was over 1 month of work. I am trying to pull the hours worked per month for further calculations not related to this question. However, issues arise when jobs last over a month as I need to appropriately account for the hours in the correct month. For example the job in A2 and B2: it began in January and finished in May. How could I take the hours worked and evenly spread them across Jan, Feb, Mar, Apr, and May.

excelHoursWorked.png

I know to check the "Over 1 Month?" column for "YES" and also how to divide the hours for the correct number of months worked but I am stuck on how to get VBA to decipher which months to distribute the hours across with only MM/DD/YYYY start and end dates.

I am looking for any help at all, even just a rough idea on how to decipher which months to spread the hours across. Just trying to figure out how to approach the problem, thank you.