Auto-populating calendar?

etarsha

New Member
Joined
Mar 20, 2017
Messages
2
Hello all,

I am trying to build a calendar that, if the calendar date falls within the class dates, located to the right, it will display a value.

I have the Calendar days easy and displaying the day of the week above them.
I have the class start date and the predicted end date
I need a formula that populates beneath the date, when we will be required to schedule an instructor
MondayTuesdayWednesdayThursdayFriday
02jan03jan04jan05jan06jan

<tbody>
</tbody>

That pulls from this table, which states what dates the classes will actually be in attendance

Class nameClass moduleStart date end date
#1class module 101 jan18 jan
#2class module 201 jan05 jan

<tbody>
</tbody>

End goal: I will know that Monday-Thursday I need two instructors a day, but on Friday, I only need one.

MondayTuesdayWednesdayThursdayFriday
02jan03jan04jan05jan06jan
#1#1#1#1#1
#2#2#2#2-

<tbody>
</tbody>

Any help would be greatly appreciated, even if it's to say that it cannot be done; any suggestions for improvement are very welcome!

-Shawn
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Yes it is possible .. but for my solution you will need to make a slight mod to your summary table ( insert column A and copy the class names into A as in my shot below)

I also moved the list of classes and their start and end dates to another worksheet (I used sheet4 but you can use any sheetname you like , just modify my formula)

formula in B3 =IF(AND(B$2>=VLOOKUP($A3,Sheet4!$A:$D,3,FALSE),B$2<=VLOOKUP($A3,Sheet4!$A:$D,4,FALSE)),$A3,"") .. Copy this formula across to columns C to F and down as many rows as needed

MondayTuesdayWednesdayThursdayFriday
Class name2-Jan3-Jan4-Jan5-Jan6-Jan
#1#1#1#1#1#1
#2#2#2#2#2
#3#3#3#3#3
#4#N/A#N/A#N/A#N/A#N/A

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>
 
Upvote 0
Awesome thank you! I've been messing with IF/AND formulas when I needed to be checking out VLOOKUP. Appreciate the assist!
 
Upvote 0
just one last thing ... remember you will need to add new classes to both the VLOOKUP sheet and column A of your summary sheet ; if you like you could also delete old courses from both
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top