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 that what you want for the future?

 

[Dan5977]

Yes. That looks great!

 

[sandy666]

ok, so let me know if you will be able to use this attendance list with PQ add-in (some bugs) or (preferred) XL2016 version minimum

 

[Dan5977]

So we can't use 2016+ because there will be various users inputting to this sheet at different times and we don't have later versions of excel until we upgrade to Office365.

Not sure what you mean by add-in....I can download PowerQuery on my machine but I will have trouble getting others to if it needs to be on all machines. I need to be able to set up a sheet that people can input into without having to do anything else. Anything that can be incorporated into the file though such as VBA will be fine.

Thanks for your time.

 

[sandy666]

as I said before: PQ add-in allows you to use Power Query with XL2010/2013
in your case will be better to wait for XL365
hope it won't take a long

 

[Dan5977]

Well I need a solution by tomorrow so I'll have to figure out another way. I'll come up with something

 

[Dan5977]

So this actually wasn't difficult at all. I was overthinking it. Thought I would update the thread for completeness in case anyone stumbles across it in the future. No need for PowerQuery and easy enough to do in Excel 2010.

I simply put a calendar next to the call log and for each day and row determined whether the date was in the range. For example:
=IF(AND(H$3>=$B4,H$3<=$C4),"In","")
Where H3 is the header with the date in it, B4 is date commencing and C4 is date ending.

This works for each row but there could be multiple rows for each staff member and I needed to consolidate them into one row on a calendar.
So I created a calendar on another tab with each staff member and referred back to the calendar I have just created.
=IF(COUNTIFS('Call log'!$A$4:$A$250,Calendar!$A3,'Call log'!H$4:H$250,"In")>0,"In","")
Using countifs I could check for both the staff name (A3) and whether they are "In" for any given date.

I then hid the calendar on the call log as there is no need for people to see it.

 

[sandy666]