+ Reply to Thread
Results 1 to 8 of 8

Counting available blank cells to calculate an end date

  1. #1
    Registered User
    Join Date
    03-11-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    15

    Counting available blank cells to calculate an end date

    I use an Excel planning sheet as a resource planner.
    I have dates listed and people. Against the people tasks are allocated.

    What I want to be able to do is from 'todays date' calculate when a project would end based upon how many blank cells are available and how many cells (days) I know would be needed to complete the project.


    An example of my sheet is attached with a scenario included. Hope someone can help please.
    Attached Files Attached Files

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Counting available blank cells to calculate an end date

    If a blank cell is populated, it is no longer a blank cell. Also, you do not specify the range you want the cells counted in, with beginning and ending cell. Please be specific about your parameters for what you want to measure. Based on the scenario in the attach file, you could simply add 100 to the starting date.
    Last edited by JLGWhiz; 01-18-2016 at 01:41 PM.

  3. #3
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Counting available blank cells to calculate an end date

    I agree, the wording is odd:
    I need to know what the date would be when 200 blank cells are populated (starting from 08/05/2015)
    Since, once a cell is populated, it is no longer blank. In an attempt to read between the lines, I think you want to know the date when the 200th empty cell occurs within your table. This equation will do it, but you need to enter it as an array formula by hitting CTRL-SHIFT-ENTER when inputting it.
    =INDEX(B2:B31,1+MAX(ROW(A1:A30)*(COUNTBLANK(OFFSET(D2,0,0,ROW(A1:A30),10))<200)*(COUNTBLANK(OFFSET(D2,0,0,ROW(A2:A31),10))>=200)))
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  4. #4
    Registered User
    Join Date
    03-11-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Counting available blank cells to calculate an end date

    Thank you so much. You managed to decipher what I was after and solved it.

  5. #5
    Registered User
    Join Date
    03-11-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Counting available blank cells to calculate an end date

    Quote Originally Posted by JLGWhiz View Post
    If a blank cell is populated, it is no longer a blank cell. Also, you do not specify the range you want the cells counted in, with beginning and ending cell. Please be specific about your parameters for what you want to measure. Based on the scenario in the attach file, you could simply add 100 to the starting date.
    yeah, hands up, I chose my words badly. Sorry.

  6. #6
    Registered User
    Join Date
    03-11-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Counting available blank cells to calculate an end date

    is it possible to do the same but rather than telling it which cell to use as the start point for the index (start date), instead have it work from the cell containing todays date??
    Last edited by itchy20; 01-18-2016 at 06:50 PM.

  7. #7
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Counting available blank cells to calculate an end date

    This may be able to be optimized, but it seems to do the trick:
    =INDEX(B2:B31,MATCH(Q2,B2:B31,0)+MAX(ROW(A1:A30)*(COUNTBLANK(OFFSET(D1,MATCH(Q2,B2:B31,0),0,ROW(A1:A30),10))<O1)*(COUNTBLANK(OFFSET(D1,MATCH(Q2,B2:B31,0),0,ROW(A2:A31),10))>=O1)))

    Note, I made it a bit more generic:
    Cell O1 holds the quantity you are looking for (e.g. 200). Just replace each instance with 200 if you always want 200.
    Cell Q2 holds the start date.
    Again, enter with Ctrl-Shift-Enter

  8. #8
    Registered User
    Join Date
    03-11-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Counting available blank cells to calculate an end date

    Hi Folks,

    I need help. I'm stuck! I've tried to implement the formula but I cannot make it work on my current data.
    I have attached my file.

    The formula is in 'Major Project Stats' B16

    What I want to do is use the date specified in B3 to count a specific number (specified in B15) of blank cells on the 'Planner' sheet. So starting from the date in B3 count blank cells to reach the number of cells in B15 and return the associated rows date to B16.

    Can anybody make it work please? I just don't know what's wrong!!

    Thanks

    Rich
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Counting blank cells up to first non-blank cell
    By DLG3 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-08-2015, 07:36 AM
  2. counting non blank cells in a date range
    By g4tso in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2014, 05:55 PM
  3. [SOLVED] If formula, if two cells are blank, return blank, if one has a date, subtract todays date
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2014, 12:47 PM
  4. Find a blank cell and calculate the average of all cells above till the next blank
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2012, 11:51 AM
  5. Excel 2007 : Counting a Date Range + blank cells
    By Rino468 in forum Excel General
    Replies: 3
    Last Post: 11-01-2010, 02:40 PM
  6. Date formula counting blank cells
    By kerndogg in forum Excel General
    Replies: 2
    Last Post: 12-09-2009, 02:13 PM
  7. Replies: 0
    Last Post: 08-23-2005, 03:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1