Calculating Hours worked per month in VBA Excel

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.

I would find the earliest date in the labor begins column and the most recent date in the work ends column and create a new column for each month between that time.

Then I would go line by line in your date and average the time for each row... for example, your first column is Jan - May 2019, 168.5/5 = 33.7 a month... if there are overlaps, simply add to the running total.

I could write you a code pretty quickly if you want what I just described.

Okay that does make sense. I appreciate the example as I get the idea of the process required too.

I would greatly appreciate a code snippet if possible but no problem if not.

Thank you.

Thank you, that really helped to point me in the right direction. Much appreciated.

Originally Posted by bsalv

hello
like this
=MAX(0;MIN(G$1;$B3+1)-MAX(F$1;$A3))/$D3*$C3

I was just wondering if you could explain how this formula works? I've seen that it is flawless but I would like to learn how it actually works to garner a better understanding.