SOLVED

Month is true, when it it between a date range.

%3CLINGO-SUB%20id%3D%22lingo-sub-2464165%22%20slang%3D%22en-US%22%3EMonth%20is%20true%2C%20when%20it%20it%20between%20a%20date%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464165%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20Month%20to%20be%20true.%20If%20one%20or%20more%20dates%20in%20a%20date%20range%20is%20in%20that%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20excample%20file%20for%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2464165%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-2464280%22%20slang%3D%22en-US%22%3ERe%3A%20Month%20is%20true%2C%20when%20it%20it%20between%20a%20date%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20G8%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(AND(%24E8%26lt%3B%3DG%244%2C%24F8%26gt%3B%3DG%243)%2C%22T%22%2C%22F%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%2C%20then%20down%20or%20vice%20versa.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464696%22%20slang%3D%22en-US%22%3ERe%3A%20Month%20is%20true%2C%20when%20it%20it%20between%20a%20date%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20Excel%20365%20formula%20that%20I%20like%20is%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20latestStart%2C%20IF(Start%26gt%3BmonthBegin%2C%20Start%2C%20monthBegin)%2C%0A%20%20earliestEnd%2C%20IF(End%3CMONTHEND%3E0%2C%20overlap%2C%20%22%22)%20)%3C%2FMONTHEND%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eplaced%20in%20a%20single%20cell%20and%20allowed%20to%20spill.%26nbsp%3B%20The%20final%20line%20can%20be%20modified%20to%20give%20%22T%22%20and%20%22F%22%20if%20preferred.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hi,

 

I want the Month to be true. If one or more dates in a date range is in that month.

 

Please see excample file for details.

 

Best Regards

- Geir

4 Replies
best response confirmed by Geir Hogstad (Regular Contributor)
Solution

@Geir Hogstad 

In G8:

 

=IF(AND($E8<=G$4,$F8>=G$3),"T","F")

 

Fill to the right, then down or vice versa.

@Geir Hogstad 

An Excel 365 formula that I like is

= LET(
  latestStart, IF(Start>monthBegin, Start, monthBegin),
  earliestEnd, IF(End<monthEnd, End, monthEnd),
  overlap, 1 + earliestEnd - latestStart,
  IF(overlap>0, overlap, "") )

placed in a single cell and allowed to spill.  The final line can be modified to give "T" and "F" if preferred.

@Geir Hogstad 

For the possible benefit of Excel 365 users, the following shows a spreadsheet in which the display is controlled by a check box and conditional formatting.

image.pngimage.png

Thank you, it worked perfect.

- Geir