SOLVED

Count of Dates based on GUI, Transaction Date and Period Week

%3CLINGO-SUB%20id%3D%22lingo-sub-2276708%22%20slang%3D%22en-US%22%3ECount%20of%20Dates%20based%20on%20GUI%2C%20Transaction%20Date%20and%20Period%20Week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20All%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20looking%20to%20calculate%20Week%20Count%20based%20on%20Employee%20GUI%2C%20Week%20Ending%20date%20and%20Period%20week%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3Esee%20below%20screenshot%2C%20where%20i%20need%20a%20formula%20to%20calculate%20that%2C%20any%20help%20on%20this%20is%20highly%20appreciate.%20Column%20N%20is%20what%20i%20did%20manually%20which%20is%20basically%20the%20output%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3Emay%20be%20count%20if%26nbsp%3Bcan%20do%20this%2C%20not%20sure%2C%20appreciate%20any%20help%20on%20this%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2276708%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-2279451%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Dates%20based%20on%20GUI%2C%20Transaction%20Date%20and%20Period%20Week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F877658%22%20target%3D%22_blank%22%3E%40vjnvinod%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20482px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274126iEDDC71DC20920F91%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMIN(1%2CCOUNTIFS(%24A%3A%24A%2C%24A2%2C%24L%3A%24L%2C%24L2)%2F5)*(COUNTIFS(%24A%242%3A%24A2%2C%24A2%2C%24L%242%3A%24L2%2C%24L2)%3D1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278865%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Dates%20based%20on%20GUI%2C%20Transaction%20Date%20and%20Period%20Week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278865%22%20slang%3D%22en-US%22%3EThank%20you%20for%20this%2C%20additionally%20the%20Logic%20should%20also%20needs%20to%20be%3CBR%20%2F%3Ebased%20on%20Transaction%20date%20as%20well.%20for%20eg%20what%20i%20am%20trying%20to%20do%20is%20to%20calculate%20the%20number%20of%20weeks%20a%20person(Employee%20GUI)%20has%20worked.%20so%20in%20a%20week%20there%20could%20be%205%20or%206%20or%207%20transaction%20date%2C%20count%20that%20as%201%20and%20if%20its%20less%20thn%205%20or%206%20or%207%20transaction%20date%20(for%20Eg%20in%20a%20week%20if%20there%20is%20only%204%20transaction%20date%2C%20than%20count%20that%20as%204%2F5week%20which%20is%200.8%20Week%20and%20so%20on%20for%20anything%20less%20thn%205%20or%206%20or%207%20transaction%20date%20in%20that%20week.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20Hope%20i%20am%20not%20confusing%20you%2C%20this%20could%20be%20little%20hard%20to%20create%20not%20sure%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2276778%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Dates%20based%20on%20GUI%2C%20Transaction%20Date%20and%20Period%20Week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F877658%22%20target%3D%22_blank%22%3E%40vjnvinod%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20I%20understood%20the%20logic%20correctly%2C%20as%20variant%20that%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20323px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F273517iE5A5500B5F3F0E06%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D--(COUNTIFS(%24A%242%3A%24A2%2C%24A2%2C%24L%242%3A%24L2%2C%24L2)%3D1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Edrag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

I am looking to calculate Week Count based on Employee GUI, Week Ending date and Period week

see below screenshot, where i need a formula to calculate that, any help on this is highly appreciate. Column N is what i did manually which is basically the output

 

 



may be count if can do this, not sure, appreciate any help on this

3 Replies

@vjnvinod 

Not sure I understood the logic correctly, as variant that could be

image.png

with

=--(COUNTIFS($A$2:$A2,$A2,$L$2:$L2,$L2)=1)

drag it down

Thank you for this, additionally the Logic should also needs to be
based on Transaction date as well. for eg what i am trying to do is to calculate the number of weeks a person(Employee GUI) has worked. so in a week there could be 5 or 6 or 7 transaction date, count that as 1 and if its less thn 5 or 6 or 7 transaction date (for Eg in a week if there is only 4 transaction date, than count that as 4/5week which is 0.8 Week and so on for anything less thn 5 or 6 or 7 transaction date in that week.

I Hope i am not confusing you, this could be little hard to create not sure

best response confirmed by vjnvinod (New Contributor)
Solution

@vjnvinod 

As variant

image.png

with

=MIN(1,COUNTIFS($A:$A,$A2,$L:$L,$L2)/5)*(COUNTIFS($A$2:$A2,$A2,$L$2:$L2,$L2)=1)