SOLVED
Home

Value fall between dates? Looking retrospectively

%3CLINGO-SUB%20id%3D%22lingo-sub-812040%22%20slang%3D%22en-US%22%3EValue%20fall%20between%20dates%3F%20Looking%20retrospectively%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812040%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20have%20a%20sheet%20that%20includes%20in%20each%20row%2C%20a%20start%20date%20cell%20and%20a%20finish%20date%20cell.%20Third%20cell%20per%20row%20I've%20got%20happily%20set%20up%20to%20tell%20me%20the%20working%20day%20difference%20between%20the%20start%20and%20finish%20date%20(or%20TODAY%20if%20unfinished).%3C%2FP%3E%3CP%3EHow%20can%20I%20set%20up%20something%20like%20a%20COUNTIF%20to%20show%20me%20how%20many%20unfinished%20items%20there%20were%20on%20a%20particular%20day%2C%20and%20then%20how%20many%20working%20days%20each%20item%20had%20been%20open%20for%20on%20that%20particular%20day%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-812040%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-812096%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20fall%20between%20dates%3F%20Looking%20retrospectively%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394867%22%20target%3D%22_blank%22%3E%40tsaurus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20be%20able%20share%20a%20sample%20worksheet%20with%20some%20example%20data%20points%20and%20expected%20result%20%3F%26nbsp%3B%20That%20will%20help%20you%20and%20contributors%20here%20to%20get%20a%20quicker%20and%20better%20solution%20for%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-812532%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20fall%20between%20dates%3F%20Looking%20retrospectively%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394867%22%20target%3D%22_blank%22%3E%40tsaurus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20525px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127604i6F19D86E1F74E8C2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethat%20could%20be%20like%3C%2FP%3E%0A%3CP%3EUnfinished%20tasks%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(B%3AB%2C%22%26gt%3B%22%26amp%3B%24G%242%2CA%3AA%2C%22%26lt%3B%3D%22%26amp%3B%24G%242)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWorkdays%20opened%20on%20that%20date%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%20(A2%26lt%3B%3D%24G%242)*(B2%26gt%3B%3D%24G%242)%2CNETWORKDAYS(A2%2CMIN(B2%2C%24G%242))%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816326%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20fall%20between%20dates%3F%20Looking%20retrospectively%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816326%22%20slang%3D%22en-US%22%3EThanks%20Kodipady%20%3A)was%20my%20first%20post%20but%20will%20definitely%20keep%20it%20in%20mind%20for%20any%20future%20posts!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816327%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20fall%20between%20dates%3F%20Looking%20retrospectively%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816327%22%20slang%3D%22en-US%22%3EThanks%20Sergei!%20Always%20looks%20so%20simple%20once%20it's%20laid%20out.%20Works%20a%20charm!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818602%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20fall%20between%20dates%3F%20Looking%20retrospectively%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818602%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394867%22%20target%3D%22_blank%22%3E%40tsaurus%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
tsaurus
New Contributor

Hi!

I have a sheet that includes in each row, a start date cell and a finish date cell. Third cell per row I've got happily set up to tell me the working day difference between the start and finish date (or TODAY if unfinished).

How can I set up something like a COUNTIF to show me how many unfinished items there were on a particular day, and then how many working days each item had been open for on that particular day?

 

5 Replies

@tsaurus 

Would you be able share a sample worksheet with some example data points and expected result ?  That will help you and contributors here to get a quicker and better solution for you. 

Solution

@tsaurus 

For such sample

image.png

that could be like

Unfinished tasks

=COUNTIFS(B:B,">"&$G$2,A:A,"<="&$G$2)

Workdays opened on that date

=IF( (A2<=$G$2)*(B2>=$G$2),NETWORKDAYS(A2,MIN(B2,$G$2)),0)
Thanks Kodipady was my first post but will definitely keep it in mind for any future posts!
Thanks Sergei! Always looks so simple once it's laid out. Works a charm!

@tsaurus , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies