Automatic entries in cells based on a date range

%3CLINGO-SUB%20id%3D%22lingo-sub-2722449%22%20slang%3D%22en-US%22%3EAutomatic%20entries%20in%20cells%20based%20on%20a%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722449%22%20slang%3D%22en-US%22%3E%3CP%3ELooking%20for%20help%20creating%20a%20formula%20that%20will%20do%20the%20following%20-%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20is%20start%20date%3C%2FP%3E%3CP%3EColumn%20B%20is%20an%20end%20date%3C%2FP%3E%3CP%3EColumn%20C%20is%20a%20number%3C%2FP%3E%3CP%3EThe%20rest%20of%20the%20columns%20are%20dates%20that%20I%20want%20the%20number%20in%20column%20C%20to%20be%20automatically%20placed%20if%20the%20date%20falls%20in%20the%20range%20of%20columns%20A%20and%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EStan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2722449%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-2722466%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20entries%20in%20cells%20based%20on%20a%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1147326%22%20target%3D%22_blank%22%3E%40StantonSmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20D2%3A%3C%2FP%3E%0A%3CP%3E%3DIF(AND(D%241%26gt%3B%3D%24A2%2CD%241%26lt%3B%3D%24B2)%2C%24C2%2C%22%22)%3C%2FP%3E%0A%3CP%3EFill%20down%20and%20to%20the%20right%20(or%20vice%20versa).%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2722578%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20entries%20in%20cells%20based%20on%20a%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1147326%22%20target%3D%22_blank%22%3E%40StantonSmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20Excel%20365%2C%20a%20single%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF((day%26gt%3B%3DstartDate)*(day%26lt%3B%3DendDate)%2Cnumber%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eentered%20into%20cell%20D2%20will%20generate%20the%20entire%20389x352%20array.%26nbsp%3B%20What%20use%20it%20is%2C%20I%20have%20no%20idea.%3C%2FP%3E%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%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308216i28181DD6CDC77A62%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%3C%2FLINGO-BODY%3E
New Contributor

Looking for help creating a formula that will do the following - 

Column A is start date

Column B is an end date

Column C is a number

The rest of the columns are dates that I want the number in column C to be automatically placed if the date falls in the range of columns A and B.

 

Thanks,

Stan

4 Replies

@StantonSmith 

In D2:

=IF(AND(D$1>=$A2,D$1<=$B2),$C2,"")

Fill down and to the right (or vice versa).

See the attached workbook.

@StantonSmith 

Using Excel 365, a single formula

=IF((day>=startDate)*(day<=endDate),number,"")

entered into cell D2 will generate the entire 389x352 array.  What use it is, I have no idea.

image.png

@Hans Vogelaar

Thanks so much! saved me a lot of time

thanks! its for showing the amount of people processing over a date range and related to capacities. I know it is "analog" but I like to see the array before summing and making it into a chart.