Help on calculation by date

%3CLINGO-SUB%20id%3D%22lingo-sub-3132505%22%20slang%3D%22en-US%22%3EHelp%20on%20calculation%20by%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3132505%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EI%20need%20help%20on%20how%20to%20count%20number%20of%20tickets%20open%2012%20weeks%20befora%20and%2012%20weeks%20after%20a%20certain%20date%20in%20Excel.%20The%20trouble%20is%20tickets%20should%20be%20counted%20by%20device%20id%20where%20ticket%20id%20is%20unice%20value%20and%20device%20id%20can%20appear%20multiple%20times.%20I%20created%20a%20pivot%20table%20by%20weeks%20but%20as%20I%20add%20more%20tickets%20in%20time%2C%20I%20need%20to%20adjust%20the%20formula%20to%20pick%20different%2012%20weeks(cells)%20as%20I%20move%20from%20week%203%20to%20week%204%20etc...%20Id%20there%20any%20way%20to%20avoid%20pivot%20and%20create%20a%20formula%20or%20at%20least%20to%20adjust%20pivot%20table%20to%20show%20dates%20by%20days%20-%20it%20is%20a%20large%20set%20of%20data%20so%20pivot%20table%20only%20shows%20week%20level%20as%20lowest%20at%20date%20hiararchy.%20Hope%20I'm%20not%20too%20confusing%20and%20someone%20can%20help%20with%20little%20Excel%20magic%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-LABS%20id%3D%22lingo-labs-3132505%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-3133477%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20calculation%20by%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3133477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1300066%22%20target%3D%22_blank%22%3E%40Jelena27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DCOUNT(UNIQUE(FILTER(B2%3AB27%2C(C2%3AC27%26gt%3B%3DH2)%2B(C2%3AC27%26lt%3B%3DI2))))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIs%20this%20what%20you%20are%20looking%20for%3F%20The%20formula%20returns%20the%20number%20of%20unique%20device%20id%20numbers%2012%20weeks%20before%20and%2012%20weeks%20after%20a%20certain%20date%20as%20shown%20in%20the%20attached%20file.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hello,
I need help on how to count number of tickets open 12 weeks befora and 12 weeks after a certain date in Excel. The trouble is tickets should be counted by device id where ticket id is unice value and device id can appear multiple times. I created a pivot table by weeks but as I add more tickets in time, I need to adjust the formula to pick different 12 weeks(cells) as I move from week 3 to week 4 etc... Id there any way to avoid pivot and create a formula or at least to adjust pivot table to show dates by days - it is a large set of data so pivot table only shows week level as lowest at date hiararchy. Hope I'm not too confusing and someone can help with little Excel magic
2 Replies

@Jelena27 

=COUNT(UNIQUE(FILTER(B2:B27,(C2:C27>=H2)+(C2:C27<=I2))))

 

Is this what you are looking for? The formula returns the number of unique device id numbers 12 weeks before and 12 weeks after a certain date as shown in the attached file.

Thanks a lot, but I wasn't succesfull I'm afraid.. I've attached an example of the report I need to populate, the number of ticket per device before and after certain date, but to add more details, that certain date will change (entered date field) and the raw data I need to use has ticket as unique values but number of devices are duplicate meaning there are more than one ticket opened for each device and I need calculation per specific device in calc sheet hope this makes sense:-)