SOLVED

How to create a Dynamic list of dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2631066%22%20slang%3D%22en-US%22%3EHow%20to%20create%20a%20Dynamic%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631066%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20I%20thought%20this%20would%20be%20easy%20but%20once%20again%20I'm%20struggling.%20I%20just%20need%20to%20create%20a%20list%20of%20dates%20starting%20from%20H4%20that%20correspond%20to%20the%20%3F%20in%20red%2C%20and%2C%20have%20a%20single%20cell%20that%20keeps%20track%20of%20the%20number%20of%20dates%20that%20this%20list%20has%20as%20more%20appear%20and%20older%20ones%20are%20resolved....%3C%2FP%3E%3CP%3EAs%20always%20my%20thanks%20and%20gratitude%20in%20advance%20to%20the%20amazing%20Excel%20community%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2631066%22%20slang%3D%22en-US%22%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-2631269%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20Dynamic%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421224%22%20target%3D%22_blank%22%3E%40Cambosity100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTA(D4%3AD34)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIn%20case%20you%20want%20to%20count%20the%20number%20of%20dates.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EPlease%20have%20a%20look%20at%20the%20inserted%20file.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631391%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20Dynamic%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631391%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3BThankyou%20for%20your%20response%20Nikolino.%20Unfortunately%20this%20is%20not%20what%20I%20need.%20It%20is%20not%20how%20many%20%3F%20marks%20per%20month%20but%20a%20list%20of%20the%20dates%20next%20to%20them.%20Also%2C%20as%20there%20will%20be%20other%20letters%20in%20Column%20D%20it%20does%20really%20need%20to%20then%2C%20I%20believe%2C%20count%20the%20dates%20that%20appear%20in%20that%20list.%20What%20you%20have%20seen%20is%20just%20a%20small%20sample%20of%20much%20larger%20spreadsheet%20and%20I%20need%20to%20access%20the%20dates%20to%20resolve%20the%20problems%20associated%20with%20them.%20But%20thankyou%20for%20your%201st%20reply.%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Peter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631438%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20Dynamic%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631438%22%20slang%3D%22en-US%22%3EYou%20write%3A%20Also%2C%20as%20there%20will%20be%20other%20letters%20in%20Column%20D%20it%20does%20really%20need%20to%20then%2C%20I%20believe%2C%20count%20the%20dates%20that%20appear%20in%20that%20list.%3CBR%20%2F%3E%3CBR%20%2F%3Eif%20I%20may%20ask%2C%20what%20letters%3F%3CBR%20%2F%3EI%20only%20see%20a%20question%20mark%3CBR%20%2F%3EPlease%20explain%20exactly%20the%20function%20you%20want%20to%20create%20and%20use%20or%20add%20real%20data%20as%20far%20as%20possible%20(please%2C%20no%20sensitive%20data).%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20patience%20and%20time.%3CBR%20%2F%3E%3CBR%20%2F%3ENikolino%3CBR%20%2F%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631471%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20Dynamic%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631471%22%20slang%3D%22en-US%22%3EAt%20the%20same%20time%20I%20have%20just%20used%20the%20Counta%20function%20after%20manually%20creating%20dates%20in%20H4%20an%20it%20counts%20them%20beautifully.%20That%20does%20solve%20that%20half%20of%20the%20problem%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Thank%20you%20Nikolino%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631476%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20Dynamic%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631476%22%20slang%3D%22en-US%22%3ENo%20probs%20give%20me%2010%20mins%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631609%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20Dynamic%20list%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631609%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421224%22%20target%3D%22_blank%22%3E%40Cambosity100%3C%2FA%3E%26nbsp%3BSo%2C%20I%20work%20for%20a%20NDIS%20Provider%20supplying%20supports%20to%20people%20with%20disability.%20There%20are%20about%2060%20clients%20and%2040%20odd%20staff.%20I%20need%20to%20do%20a%20self%20audit%20of%20the%20year%20past%20of%20shift%20notes%20in%20hard%20copy%20submitted%20by%20staff%20over%20the%20year.%20I%20will%20be%20cross%20referencing%20from%20the%20years%20roster%20but%20need%20to%20identify%20if%20there%20are%20missing%20shift%20notes%20and%20why.%20There%20can%20be%20a%20whole%20host%20of%20reasons.%20This%20spreadsheet%20will%20identify%20where%20there%20are%26nbsp%3B%3CU%3Epotential%20issues%26nbsp%3B%3C%2FU%3E%20to%20be%20looked%20at%20so%20it%20is%20essential%26nbsp%3Bto%20know%26nbsp%3Bfor%20each%26nbsp%3B%3CSPAN%3Eclient%26nbsp%3B%20what%20dates%20the%20issues%20occur%20and%20how%20many%20are%20still%20unresolved.%20I%20have%20expanded%20the%20work%20book%20to%204%20clients%20to%20give%20a%20better%20glimpse%20of%20what%20I%20am%20doing.%20I%20hope%20this%20clears%20it%20up%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20And%20once%20again%20I%20thank%20you%20for%20your%20interest%20in%20this%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi all I thought this would be easy but once again I'm struggling. I just need to create a list of dates starting from H4 that correspond to the ? in red, and, have a single cell that keeps track of the number of dates that this list has as more appear and older ones are resolved....

As always my thanks and gratitude in advance to the amazing Excel community

13 Replies

@Cambosity100 

 

=COUNTA(D4:D34)

In case you want to count the number of dates.

 

Please have a look at the inserted file.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE Thankyou for your response Nikolino. Unfortunately this is not what I need. It is not how many ? marks per month but a list of the dates next to them. Also, as there will be other letters in Column D it does really need to then, I believe, count the dates that appear in that list. What you have seen is just a small sample of much larger spreadsheet and I need to access the dates to resolve the problems associated with them. But thankyou for your 1st reply.  Peter

 

You write: Also, as there will be other letters in Column D it does really need to then, I believe, count the dates that appear in that list.

if I may ask, what letters?
I only see a question mark
Please explain exactly the function you want to create and use or add real data as far as possible (please, no sensitive data).

Thank you for your patience and time.

Nikolino
I know I don't know anything (Socrates)
At the same time I have just used the Counta function after manually creating dates in H4 an it counts them beautifully. That does solve that half of the problem Thank you Nikolino
No probs give me 10 mins

@Cambosity100 So, I work for a NDIS Provider supplying supports to people with disability. There are about 60 clients and 40 odd staff. I need to do a self audit of the year past of shift notes in hard copy submitted by staff over the year. I will be cross referencing from the years roster but need to identify if there are missing shift notes and why. There can be a whole host of reasons. This spreadsheet will identify where there are potential issues  to be looked at so it is essential to know for each client  what dates the issues occur and how many are still unresolved. I have expanded the work book to 4 clients to give a better glimpse of what I am doing. I hope this clears it up And once again I thank you for your interest in this

@Cambosity100 

Here you can see a small example with a pivot table in client 1.

 

Thank you for your patience and time.

 

Nikolino

I know I don't know anything (Socrates)

@Cambosity100 

I see you have received some suggestions.

Here is how I would have solved, it.

 

/Geir

@Geir Hogstad Thank you for your formula. I see the ranges go to C and D 383 but it only returns 2 results and the spill is in a row not a column. Not sure why....But it is much closer to what I am seeking. I have attached an expanded version of the sample workbook with manually inputted (random) dates to show what it should visually look like. I found that the simple COUNTA function will count the dates when like this (but do not know whether this works with a spill function, I assume it will ) I have also given in a post more detail of the functionality of the workbook if it would help to read this. 

     Thankyou again Geir but still not working.

@Cambosity100 

Only returns 2, because there is only 2 for Februar: Whu would you the filtered results in rows? And not columns, so you can have one row for each month?
=TRANSPOSE(FILTER(C4:C383;(MONTH(C4:C383)=1)*(D4:D383<>"");"")) where 1 is january
=TRANSPOSE(FILTER(C4:C383;(MONTH(C4:C383)=2)*(D4:D383<>"");"")) where 2 is february, one for each month

 

And you can use Count (To count if numbers) with spill (#)
/Geir

best response confirmed by Cambosity100 (Contributor)
Solution

@Cambosity100 

Perhaps you mean something like in attached.

Thankyou so much ! That is exactly what I was looking for. I knew it would be something simple just couldn't find the right function. Its perfect Thankyou to all have contributed to getting to this point. Nikolino and Geir your time is appreciated and valued

@Cambosity100 , glad it helped