Help with Organizing University Assignment Calendar

%3CLINGO-SUB%20id%3D%22lingo-sub-2741638%22%20slang%3D%22en-US%22%3EHelp%20with%20Organizing%20University%20Assignment%20Calendar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741638%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CBR%20%2F%3EI%20am%20trying%20to%20make%20a%20calendar%20with%20all%20my%20assignment%20due%20dates.%20I%20chose%20a%20template%20called%20%22student%20assignment%20planner%22%20that%20has%20three%20tabs%20for%20assignments%2C%20month%20view%2C%20and%20week%20view.%20I%20have%20input%20all%20my%20assignment%20information%20such%20as%20description%20and%20due%20date%20which%20then%20is%20inputted%20into%20the%20calendar%20with%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(LEN(F10)%3D0%2C%22%22%2CIF(COUNTIF(Assignments%5BDUE%20DATE%5D%2CDATE(MoYear%2C%40MoMonthNum%2CF10))%26gt%3B0%2C%22Assignment%20due!%22%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20with%20this%20formula%2C%20I%20only%20see%20%22Assignment%20due!%22%20on%20the%20monthly%20calendar%20which%20is%20not%20helpful%20when%20I%20have%20multiple%20classes%20with%20possibly%20more%20than%20one%20assignment%20due%20on%20one%20day.%20So%20if%20someone%20could%20write%20a%20formula%20that%20allows%20the%20description%20to%20be%20inputted%20into%20the%20calendar%20as%20well%3F%20Or%20suggest%20a%20more%20efficient%20way%20to%20make%20a%20table%20of%20assignments%20with%20at%20least%202%20columns%20of%20information%20(possibly%204)%20fit%20into%20a%20calendar%20without%20having%20to%20input%20everything%20manually.%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EClass%3C%2FLI%3E%3CLI%3EAssignment%20Name%20(Top%20Priority)%3C%2FLI%3E%3CLI%3EDue%20Date%20(Top%20Priority)%3C%2FLI%3E%3CLI%3EDue%20Time%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2741638%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2741854%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Organizing%20University%20Assignment%20Calendar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1152700%22%20target%3D%22_blank%22%3E%40KIP344%3C%2FA%3E%26nbsp%3BThese%20kind%20of%20templates%20are%20in%20fact%20quite%20useless.%20Packed%20with%20%22hidden%20treasures%22%20that%20make%20it%20hard%20to%20follow%20through%20what's%20going%20on.%20And%20as%20you%20noticed%20already%2C%20flagging%20that%20%22something%20is%20due%20today%22%20is%20no%20good.%20One%20would%20need%20to%20know%20%3CSTRONG%3Ewhat%3C%2FSTRONG%3E%20is%20due%20today.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyhow%2C%20if%20you%20have%20have%20MS365%20you%20can%20use%20the%20FILTER%20and%20TEXTJOIN%20functions%20to%20create%20the%20daily%20input%20in%20the%20Month%20and%20Week%20views.%20Though%2C%20the%20formulae%20aren't%20very%20straight-forward.%20If%20this%20doesn't%20work%20for%20you%2C%20I'd%20suggest%20you%20scrap%20this%20template%20all-together%20and%20summarise%20your%20assignment%20list%20with%20a%20plain%20pivot%20table.%20True%2C%20not%20as%20%22fancy%22%20as%20the%20template%2C%20but%20it%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20the%20original%20formulae%20contained%20a%20part%20where%20a%20concatenated%20textual%20date%20like%20%22September%201%2C%202021%22%20was%20transformed%20to%20a%20proper%20date%20value.%20Being%20on%20a%20European%20system%2C%20my%20Excel%20didn't%20like%20that.%20I%20needed%20to%20change%20that%20particular%20part%20so%20that%20it%20would%20transform%20%221-September-2021%22%20to%20a%20date%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, 
I am trying to make a calendar with all my assignment due dates. I chose a template called "student assignment planner" that has three tabs for assignments, month view, and week view. I have input all my assignment information such as description and due date which then is inputted into the calendar with this formula:

=IF(LEN(F10)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,@MoMonthNum,F10))>0,"Assignment due!",""))

 

But with this formula, I only see "Assignment due!" on the monthly calendar which is not helpful when I have multiple classes with possibly more than one assignment due on one day. So if someone could write a formula that allows the description to be inputted into the calendar as well? Or suggest a more efficient way to make a table of assignments with at least 2 columns of information (possibly 4) fit into a calendar without having to input everything manually. 

  1. Class
  2. Assignment Name (Top Priority)
  3. Due Date (Top Priority)
  4. Due Time

 

1 Reply

@KIP344 These kind of templates are in fact quite useless. Packed with "hidden treasures" that make it hard to follow through what's going on. And as you noticed already, flagging that "something is due today" is no good. One would need to know what is due today.

 

Anyhow, if you have have MS365 you can use the FILTER and TEXTJOIN functions to create the daily input in the Month and Week views. Though, the formulae aren't very straight-forward. If this doesn't work for you, I'd suggest you scrap this template all-together and summarise your assignment list with a plain pivot table. True, not as "fancy" as the template, but it works.

 

Note that the original formulae contained a part where a concatenated textual date like "September 1, 2021" was transformed to a proper date value. Being on a European system, my Excel didn't like that. I needed to change that particular part so that it would transform "1-September-2021" to a date value.

 

File attached.