I need to summate the hours that jobs went for, per year, so that I can produce a graph (and table) of the Number of hours affected per year affected by jobs.
As you will see in the data, there was a job that started on the 5th Dec and went to 16th Dec. There were other jobs that started and ended during this job, and I need to not double count these hours.
As another complex example: in addition to the data below, if a job started 16th Dec 07:00 and went to 16th Dec 15:00, the last two hours of it would need to be counted. But as there is a job starting 16th Dec 13:50, only 50 minutes of the job would really need to be counted.
Data looks like:
Original data is columns B and C, (column A is a unique identifier and sensitive information).
Column D is me calculating the month of the start time
Column E is me calculating the year of the start time
Column F is the round up to an integer of C - B
Column G is the hours between C and B, formatted to [h] so that I can so more than 24 hours.
I effectively need an output like
2018 xxxx hrs (summation for the year) 8760 (hours in the year) YYYY% (percentage of the year affected)
2019 xxxx hrs (summation for the year) 8760 (hours in the year) YYYY% (percentage of the year affected)
2020 xxxx hrs (summation for the year) 8784 (hours in the year) YYYY% (percentage of the year affected)
2021 xxxx hrs (summation for the year) 8760 (hours in the year) YYYY% (percentage of the year affected)
2022 xxxx hrs (summation for the year) 8760 (hours in the year) YYYY% (percentage of the year affected)
As you will see in the data, there was a job that started on the 5th Dec and went to 16th Dec. There were other jobs that started and ended during this job, and I need to not double count these hours.
As another complex example: in addition to the data below, if a job started 16th Dec 07:00 and went to 16th Dec 15:00, the last two hours of it would need to be counted. But as there is a job starting 16th Dec 13:50, only 50 minutes of the job would really need to be counted.
Data looks like:
Original data is columns B and C, (column A is a unique identifier and sensitive information).
Column D is me calculating the month of the start time
Column E is me calculating the year of the start time
Column F is the round up to an integer of C - B
Column G is the hours between C and B, formatted to [h] so that I can so more than 24 hours.
I effectively need an output like
2018 xxxx hrs (summation for the year) 8760 (hours in the year) YYYY% (percentage of the year affected)
2019 xxxx hrs (summation for the year) 8760 (hours in the year) YYYY% (percentage of the year affected)
2020 xxxx hrs (summation for the year) 8784 (hours in the year) YYYY% (percentage of the year affected)
2021 xxxx hrs (summation for the year) 8760 (hours in the year) YYYY% (percentage of the year affected)
2022 xxxx hrs (summation for the year) 8760 (hours in the year) YYYY% (percentage of the year affected)
