Need help in IF and WORKDAY formula!

[johnko]

Hi, I need help in these two functions which I want to compute it into a formula for my data.


Cell L Cell M Cell N
First Day Last Day Comments

02/09/2016 07/09/2016 Dealers Please Take Note

07/09/2016 Liquidate

09/09/2016 To liquidate or waiting for approval




I want to do a IF WORKDAY or VBA codes to compute the workday of Last day/First day

-First day on Cell "L"
-Last day on Cell "M"
-Comments on Cell "N"

Dates on "First day" cell are the first priority.
Note that some of my data doesn't have dates on "First Day", if that so, take the dates on "Last Day"

I want to have an IF function that if it is:
-1 to 2 days from the today(eg. 07/09/2016), I want to have a "To liquidate" comment on cell N
-3 to 4 days, "To liquidate or waiting for approval"
-5 to 7 days, "Dealers please take note"

As it will have a large data, I would like it to be autofill down cell "N"

I am sure that a simple IF and WORKDAY function is not enough for these to work so I think a VBA code should work.

Hope the pros and gurus here are able to help me. Thanks in advance!

PS. The dates are in dd/mm/yy format.

 

[Muhammad_Usman]

Now i past formula as image you change your formula according to this image. it will work ! sorry for inconvenience

 

[Michael M]

As mentioned by Rorya
Every time there is a Less than "<" sign in your post, you need to place a space behind it, otherwise MRExcel thinks its an HTML tag...not a less than sign !!

 

[Muhammad_Usman]

Thanks Admin Its working now!

johnko check this one !


=IF(L2<>"",IF(L2< TODAY()+3,"To liquidate",IF(L2< TODAY()+5,"To liquidate or waiting for approval",IF(L2< TODAY()+8,"Dealers please take note",""))),IF(M2< TODAY()+3,"To liquidate",IF(M2< TODAY()+5,"To liquidate or waiting for approval",IF(M2< TODAY()+8,"Dealers please take note",""))))

 

[johnko]

Thanks Admin Its working now!

johnko check this one !


=IF(L2<>"",IF(L2< TODAY()+3,"To liquidate",IF(L2< TODAY()+5,"To liquidate or waiting for approval",IF(L2< TODAY()+8,"Dealers please take note",""))),IF(M2< TODAY()+3,"To liquidate",IF(M2< TODAY()+5,"To liquidate or waiting for approval",IF(M2< TODAY()+8,"Dealers please take note",""))))

Hi Usman! Thanks for the code!

 

[Muhammad_Usman]

Hi Usman! Thanks for the code!

Welcome ! Glad to help.

 

[etaf]

the problem with formulas is this forum will convert to HTML
so if you use the Greater than or less than and = often its seen as a HTML tag
to stop that
just put a space between

so IF ( L2 < = today()
IF ( L2 > = today()

it will work

you also need to add WORKDAY() into the formula if you do not want to include weekends

 

[johnko]

the problem with formulas is this forum will convert to HTML
so if you use the Greater than or less than and = often its seen as a HTML tag
to stop that
just put a space between

so IF ( L2 < = today()
IF ( L2 > = today()

it will work

you also need to add WORKDAY() into the formula if you do not want to include weekends

Hi there! Actually I do need Workday instead. And for the code, on the +7 days it stated "Dealers take note", I want it to be

=IF(L2<>"",IF(L2<TODAY()+3,"To liquidate",IF(L2<TODAY()+5,"To liquidate or waiting for approval", ELSE,"Dealers please take note",""))),IF(M2<TODAY()+3,"To liquidate",IF(M2<TODAY()+5,"To liquidate or waiting for approval", ELSE,"Dealers

After the "liquidate or waiting for approval", I want to be an ELSE statement as beyond 3 days it will be "Dealers take note"

Thank you!

 

[etaf]

if
=IF(L2< > "",IF(L2 < TODAY()+3,"To liquidate",IF(L2 < TODAY()+5,"To liquidate or waiting for approval",IF(L2 < TODAY()+8,"Dealers please take note",""))),IF(M2 < TODAY()+3,"To liquidate",IF(M2 < TODAY()+5,"To liquidate or waiting for approval",IF(M2 < TODAY()+8,"Dealers please take note",""))))

works

then change today() + 3 etc
to
workday ( today() , 3 )

that will exclude workdays

 

[johnko]

if
=IF(L2< > "",IF(L2 < TODAY()+3,"To liquidate",IF(L2 < TODAY()+5,"To liquidate or waiting for approval",IF(L2 < TODAY()+8,"Dealers please take note",""))),IF(M2 < TODAY()+3,"To liquidate",IF(M2 < TODAY()+5,"To liquidate or waiting for approval",IF(M2 < TODAY()+8,"Dealers please take note",""))))

works

then change today() + 3 etc
to
workday ( today() , 3 )

that will exclude workdays

Hi there! If I want to change to "Else" statement for Today()+8" is it possible? Because I would want to have any day after 7 days from "TODAY" to be under "Dealers please take note"

 

[etaf]

you have a nested if
the way the if works is split into 3 parts

the test
if true
if false

separated with a comma

so like an else statement

IF(M2 < TODAY()+8,"Dealers please take note","other text like an else"))

and you can nest the rules