Auto populating a calendar based on list

Hi,

I am trying to put an agenda spreadsheet together which will populate based on a list.

In cells B10:I35 I have a table which users will populate. Within this list they put in the following:
• Activity Detail
• Work stream
• Owner
• Day
• Start Time
• Duration (minutes)

Based on this list I would like it to populate the 3 day calendar in columns K:O. Can this be achieved?

I know it might be easier to populate the calendar directly but I want to prevent this if I can find a workaround.

Where there is a conflict can this be flagged in anyway? E.g. if there is a time overlap?

And the other problem I have is if I try and add the break minutes (100mins) how do I display this as 1 Hour 40 Mins in the summary table, cell D44

Hi Paul

Is this to be done with Excel formulas, or would a VBA solution work?

As for your last problem, you need to add a '/24' to your formula to make it a time. Then you can use a custom format of '[h] "h" m "m"'.

in L11

Please Login or Register  to view this content.

in M11

Please Login or Register  to view this content.

Any good?

Hi,

thanks for the "/24" formula, that worked!

In terms of L11 and M11 - this doesnt do anything other than circular references? Should these be in L11 and M11 or N11 and O11?

My preference is always formula but it could be VBA if need be. I understand formulas better.

My apologies, yes, columns N & O!

Hi,

I wasn’t notified of your reply so sorry for the delay.

I have moved them over to columns N and O and it works for day 1.

Is there any way it can be modified to work for day 2 (cells: K42:O73) and 3 (cells K75:O106) also?

Nothing wrong with the formula! Just me copying it down wrong! Thanks for the help!

Great to hear - of course, you do need to change the $K$9 reference for Day 2 and 3, but that should be that.