WORKDAY using last non-blank date

[jwillis07]

Hi all,

I'm trying to run a workday formula to generate a list of dates by adding 1 workday onto the date before it which is easy enough...

In C3: =WORKDAY(C2,1) and fill that down to generate the list of dates.


What I want to do though is only apply that workday formula to rows where column B is equal to "Yes"

If column B is blank, then leave the date blank, then when you come to the next row where B is "Yes", use the workday formula to add 1 day to the date of the last row where B was equal to "Yes"


Hope that makes sense, and if it doesn't I've attached a sample workbook that might explain it better.

Many thanks,

Jason


Expected results...


------B---------C---------D---------
2----Yes----01/07/15--
3----Yes----02/07/15--
4-----------------------
5----Yes----03/07/15--
6-----------------------
7-----------------------
8----Yes----04/07/15

[NeedForExcel]

=IF(B2="Yes",WORKDAY(C2,1),C2)

Drag Down & Across..

[cbatrody]

Hi,

Try the following formula in C3:

=IF(B3="Yes",WORKDAY(LOOKUP(2,1/(C$2:C2<>""),C$2:C2),1),"")

See the file attached!

[jwillis07]

Hi,

Try the following formula in C3:

=IF(B3="Yes",WORKDAY(LOOKUP(2,1/(C$2:C2<>""),C$2:C2),1),"")

See the file attached!

Thanks cbatrody. That does the job!

Whilst the formula you provided generates the date based on the last non-blank cell, is there any way to make it so it looks up the date from the last "Yes"

Reason I ask is that I'm planning on making it so where the value is false (B3<>"Yes") ,do another workday formula generating the date from the last date where column b is blank.

i.e...


------B---------C---------D---------
2----Yes----01/07/15--
3----Yes----02/07/15--
4-----------02/07/15--
5----Yes----03/07/15--
6-----------03/07/15--
7-----------04/07/15--
8----Yes----04/07/15

So the formula in C3 then is somthing like this...

IF B3 = Yes , WORKDAY FROM LAST ROW WHERE COLUMN B = YES , IF B3 <> Yes , WORKDAY FROM LAST ROW WHERE COLUMN B <> Yes

[cbatrody]

5----Yes----03/07/15--
6-----------03/07/15--
7-----------04/07/15--
8----Yes----04/07/15

Why is row 7 incremented from 3/7 to 4/7 in this example? Shouldn't row 7 be 03/07/15?

[cbatrody]

I have updated the revised formula as per your requirement in the attached file (Column D):

In D3:

=IF(B3="Yes",WORKDAY(LOOKUP(2,1/(B$2:B2="Yes"),D$2:D2),1),D2)