Populating Calendar From Call Log

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
2010
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'):
call log.JPG


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


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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
2010
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,455
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

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
2010
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,455
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)
 
Last edited:

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
2010
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.
 

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
2010
could you post an example of the result from your source (post#1)
calendar.JPG

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

Watch MrExcel Video

Forum statistics

Threads
1,100,037
Messages
5,472,112
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top