# Count Blank Cells which Today falls within a Date Range based on Adjacent cell

1. ## Count Blank Cells which Today falls within a Date Range based on Adjacent cell

I've been trying to figure out this formula out for weeks but I can't find a solution in the forums that cover what I need!

I need to count the number of blank cells in B1:B170 if the date in A1:A170 is +/- 3 weeks from today(). I need to count the number of cells that are empty only if there is a date listed in Column A as new rows will be added by a few different users on a daily bases. In a dream world, the cells which meet this criteria would be highlighted (I assume this would be easy to accomplish using conditional formatting once the right cells are identified?)

(I'm tracking pregnant women who enroll in a program and need to have a quick way of seeing how many patients are full term (+/- 3 weeks from due date) and have yet to deliver. As patients enroll, I will add their expected due date in Column A, and will update Column B when they deliver.)

Hope that is clear enough. Please let me know if you need any clarification or would like a sample set of data. I'd say my excel wrangling skills are moderately intermediate but I am totally stuck here.

2. ## Re: Count Blank Cells which Today falls within a Date Range based on Adjacent cell

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

Here you go:
Example.xlsx

4. ## Re: Count Blank Cells which Today falls within a Date Range based on Adjacent cell

In H19 Cell

=COUNTIFS(G2:G13,"<>"&"",H2:H13,">="&TODAY(),H2:H13,"<="&SUM(TODAY(),21,(7-WEEKDAY(TODAY(),2))))

In I19 Cell
Formula:
`Please Login or Register  to view this content.`

5. ## Re: Count Blank Cells which Today falls within a Date Range based on Adjacent cell

That is so close! Works perfectly when I enter it into the sample worksheet but when I enter it into my actual one, it returns the total number of patients with a due date within the right date range, including the patients that have already delivered. The sample worksheet includes a patient that has a due date within range and has already delivered (Patient G), so I'm not sure why it's giving me something different

I reattached my sample worksheet with a more accurate representation, yet completely desensitized, data on my actual worksheet. (ID numbers where randomly generated by excel and totally arbitrary and dates changed)

Example.xlsx

There are currently 1 users browsing this thread. (0 members and 1 guests)