calculate the hours spent for each status

Good day everyone!

I am seeking for your help in finding the VBA code that will satisfy the requirement in the below problem. I have attached the excel file for your reference.


I have a project data that need to show the total time spent in each status in order for us to know what contributes most in the process.

In column A, there is reference for each row (manula input)
In column B, there is a status that can be select thru drop down list selection (manual input)
In column D to G, transpose copy of the status except for the “Up”
Under the column D to G will show the total number of hours that status has been selected
Example: I select the status “On-going” yesterday at 1pm, and changed it to “Up” today at 10am, so the column E where the “on-going” has been placed will show 21 (hours).
Column H will show what status has the higher number of hours.

Note: 1. the status can be selected by the user more that once (example: 1st day: Ongoing, 2nd day: Waiting for partner, 3rd day: ongoing)
2. the end of the process is when the status becomes up. (No need to calculate the time spent)

Hope someone can help me with this problem.
Thank you all in advance

can anyone reply to this please.. thank you..