Calculating hours per month given a start and end date

[Roman_Frontini]

Hi team! I’m new to Coda so I’m testing out the tool. I’ve read a couple post similar to this topic but I can’t find exactly what I need so here I am.

I have an input table with the columns
Person-Project-ProjectStartDate-ProjectEndDate-TotalHours.

Based on that, I need to calculate in a new, separate table how many hours per month on average the Person is expected to be working on said project, given the ProjectStartDate and ProjectEndDate and TotalHours.

For example, I have 150 hours for “John Doe” on “Project X”, that will start in 1/1/2024 and ends in 10/30/2024. I need Coda to give me the following output:

John Doe - Project X - Jan24 - 15
John Doe - Project X - Feb24 - 15
John Doe - Project X - Mar24 - 15

And so on. Preferrably repeating the name and project code on whatever amount of rows are needed (based on difference between start date and end date).
Any help or tips are super welcome.
Thank you!

[Quentin_Morel]

Hello @Roman_Frontini
Here is my proposal

I created a table A as you mentionned

Then, I will create a column with all month from the Start Date to End Date ! (be careful, on my example I consider that you will always work full month)

This will give me the list of all month (with format MM/01/YYYY)

Then, I have your table “Table B” when you will send your data, with this button >
For each month in the list Month, you create a row in table B with relevant data, the current month, when you divide the total hours by the count of month

Here you go :

Please find embed to try it, and I remain available for any question you may have

Quentin

[Roman_Frontini]

Hi Quentin! This is exactly what I needed, as we will work under the assumption people will work “full month”. I see that Coda is more powerful than I had thought, since I had managed to do this on excel but with more steps, haha.
I really appreciate the effort and thank you for your time.

[Quentin_Morel]

You have no idea how powerful coda is

Keep in mind that there is almost nothing you can’t do with this tool ! Do not hesitate to ask question to champions & experte