Populating Calendar From Call Log

[Dan5977]

Hi all.

I'm creating a call log for those staff who are on flexible furlough so we can monitor which days everyone is working and also have a centralised record of the contact.

So the call log is something like this (tab is named 'Call log'):

The tricky bit, however is populating the calendar - here's an extract of the calendar (tab is named 'Calendar':

So as you can see, I intend for us to input a date commencing and a date ending to give a range of dates that the employee is in the office. For that range I want each date that a person is in the office to have the word "In" in the relevant cell on the calendar. Other cells can remain blank. I've omitted weekends from the calendar.

That's difficult enough but please bear in mind that we could be bringing people back several times during the month. I considered using a VLOOKUP but that would only pick up the first result for each staff member. If we call them in several times during the month I need every day to be included.

I hope that makes sense and would be really grateful for any assistance.

 

[sandy666]

is this acceptable?

 

[Dan5977]

Hi Sandy.

I need to be able to have the full month calendar (except weekends) and be able to see at a glance who is in and who isn't on each invididual day so unfortunately not.

 

[sandy666]

but in particular case, eg. 01/07/2020 ==> 05/08/2020 it would be a long list of dates?
or there will be one month only?

btw. what is the logic for 09/07/2020 in the result table? it doesn't exist in the source

 

[Dan5977]

but in particular case, eg. 01/07/2020 ==> 05/08/2020 it would be a long list of dates?
or there will be one month only?

We will do it month by month, so I'll just copy the spreadsheet for August and start again. Ideally the calendar will just say "In" next to a persons name for each date they are in.

 

[sandy666]

ok, I'll try
btw. are you able to use Power Query? update your profile to show your Excel version, please

edit:
could you post an example of the result from your source (post#1)

 

[Dan5977]

ok, I'll try
btw. are you able to use Power Query? update your profile to show your Excel version, please

No, sorry I'm not and I'm stuck with excel 2010 for the moment. We were about to upgrade to office365 but lockdown has delayed it. Will update - thanks for the tip.

 

[sandy666]

you can use PowerQuery add-in for XL 2010/2013, all above has PQ built-in
Good luck and have a nice day

 

[Dan5977]

could you post an example of the result from your source (post#1)

So that's how it would look based on the data input in my original screenshot. The table extends out for the full month.

 

[sandy666]

View attachment 17360
So that's how it would look based on the data input in my original screenshot. The table extends out for the full month.

Thanks for sharing