Convert calendar formula from individual dates to date range & show overlap

%3CLINGO-SUB%20id%3D%22lingo-sub-1232613%22%20slang%3D%22en-US%22%3EConvert%20calendar%20formula%20from%20individual%20dates%20to%20date%20range%20%26amp%3B%20show%20overlap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20modified%20a%20calendar%20template%20to%20accomodate%20my%20team's%20projects.%20The%20current%20template%20we're%20using%20only%20auto-populates%20the%20calendar%20with%20individual%20dates%2C%20and%20we're%20looking%20to%20have%20it%20show%20the%20entire%20date%20range.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E(Example%3A%20Instead%20of%20having%20to%20write%20Project%20A%20start%20date%2C%20Project%20A%20end%20date%20as%20two%20separate%20instances%2C%20we%20want%20to%20convert%20the%20table%20to%20include%20project%20start%2Fend%20date%2C%20and%20have%20the%20project%20name%20listed%20for%20all%20dates%20in%20that%20range%20on%20the%20subsequent%20calendar%20tabs)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20current%20formula%20showing%20on%20on%20the%20calendar%20dates%20is%3A%20%3DIF(ISNA(MATCH(E4%26amp%3B%22_%22%26amp%3B%24A6%2CProjects!%24A%3A%24A%2C0))%2C%22%22%2CINDEX(Projects!%24C%3A%24C%2CMATCH(E4%26amp%3B%22_%22%26amp%3B%24A6%2CProjects!%24A%3A%24A%2C0)))%3C%2FSPAN%3E%3C%2FP%3E%3CUL%3E%3CLI%3EE4%20is%20the%20calendar%20date%3C%2FLI%3E%3CLI%3EA6%20is%20the%20row%3C%2FLI%3E%3CLI%3EProjects!%24A%3A%24A%20is%20the%20reference%20number%20associate%20to%20each%20project%20record%3C%2FLI%3E%3CLI%3EProjects!%24C%3A%24C%20is%20the%20Project%20Name%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20formula%20for%20the%20reference%20number%20associated%20to%20each%20project%20record%20is%20probably%20what%20needs%20to%20be%20adjusted%20(I%20think).%20It%20currently%20reads%3A%20%3DIF(E6%3D%22%22%2C%22-%22%2CE6%26amp%3B%22_%22%26amp%3BCOUNTIF(E%246%3AE6%2CE6))%20*E6%20is%20the%20date%20associated%20to%20the%20record%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIn%20a%20nutshell%2C%20how%20do%20I%20turn%20that%20reference%20formula%20(from%20Projects%20Tab%2C%20column%20A)%20to%20count%20a%20date%20range%3F%20So%20If%20I%20were%20to%20add%20a%20second%20column%20for%20each%20project%20to%20show%20start%20date%20and%20end%20date%2C%20how%20can%20the%20reference%20formula%20capture%20both%20dates%20and%20show%20the%20project%20listed%20for%20all%20included%20days%20on%20the%20calendar%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3E%3CI%3EBonus%20Points%3A%3C%2FI%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3EI'm%20also%20trying%20to%20create%20a%20seperate%20view%20of%20the%20data%20given%20in%20this%20project%20list%20that%20will%20show%20overlapping%20channels%20and%20their%20corresponding%20dates%20in%20a%20chart%20style%20(see%20Annual%20Calendar%20tab)%2C%20where%20the%20columns%20are%20dates%20and%20rows%20are%20project%20channels%20(that%20were%20written%20on%20the%20project%20list).%20If%20anyone%20has%20pointers%20for%20how%20to%20pull%20this%20data%20to%20auto-populate%20these%20bar%20charts%2C%20that%20would%20also%20be%20very%20helpful.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThank%20you%20in%20advance%20for%20saving%20me%20from%20future%20headaches!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1232613%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1233687%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20calendar%20formula%20from%20individual%20dates%20to%20date%20range%20%26amp%3B%20show%20overlap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1233687%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584365%22%20target%3D%22_blank%22%3E%40agb02917%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20such%20logic%20doesn't%20work.%20If%2C%20for%20example%2C%20you%20have%20two%20projects%20P1%20and%20P2%20both%20having%20the%20reference%20ended%20by%20_1%2C%20they%20both%20are%20to%20be%20placed%20into%20the%20first%20row%20of%20the%20day%20if%20start%2Fend%20day%20overlapping.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20Excel%20support%20dynamic%20arrays%20you%20may%20use%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Ffilter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EFILTER%20function%3C%2FA%3E%26nbsp%3Blike%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(FILTER(Projects!%24C%3A%24C%2C(D4%26lt%3B%3DProjects!%24F%3A%24F)*(D4%26gt%3B%3DProjects!%24E%3A%24E))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3Bassuming%20each%20project%20has%20start%20and%20end%20dates.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hello!

I have modified a calendar template to accomodate my team's projects. The current template we're using only auto-populates the calendar with individual dates, and we're looking to have it show the entire date range.

(Example: Instead of having to write Project A start date, Project A end date as two separate instances, we want to convert the table to include project start/end date, and have the project name listed for all dates in that range on the subsequent calendar tabs)

The current formula showing on on the calendar dates is: =IF(ISNA(MATCH(E4&"_"&$A6,Projects!$A:$A,0)),"",INDEX(Projects!$C:$C,MATCH(E4&"_"&$A6,Projects!$A:$A,0)))

  • E4 is the calendar date
  • A6 is the row
  • Projects!$A:$A is the reference number associate to each project record
  • Projects!$C:$C is the Project Name


The formula for the reference number associated to each project record is probably what needs to be adjusted (I think). It currently reads: =IF(E6="","-",E6&"_"&COUNTIF(E$6:E6,E6)) *E6 is the date associated to the record

In a nutshell, how do I turn that reference formula (from Projects Tab, column A) to count a date range? So If I were to add a second column for each project to show start date and end date, how can the reference formula capture both dates and show the project listed for all included days on the calendar?

Bonus Points:
I'm also trying to create a seperate view of the data given in this project list that will show overlapping channels and their corresponding dates in a chart style (see Annual Calendar tab), where the columns are dates and rows are project channels (that were written on the project list). If anyone has pointers for how to pull this data to auto-populate these bar charts, that would also be very helpful.


Thank you in advance for saving me from future headaches!

1 Reply

@agb02917 

IMHO, such logic doesn't work. If, for example, you have two projects P1 and P2 both having the reference ended by _1, they both are to be placed into the first row of the day if start/end day overlapping.

 

If your Excel support dynamic arrays you may use FILTER function like

 

=IFERROR(FILTER(Projects!$C:$C,(D4<=Projects!$F:$F)*(D4>=Projects!$E:$E)),"")

 

 assuming each project has start and end dates.