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.

 

[johnko]

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

Hi There, I had created this
=IF(L22<>"",IF(L22<WORKDAY(TODAY(),2,),"To liquidate",IF(L22<WORKDAY(TODAY(),4,),"To liquidate or waiting for approval","Dealers please take note"))),IF(M22<WORKDAY(TODAY(),2),"To liquidate",IF(M22<WORKDAY(TODAY(),4),"To liquidate or waiting for approval","Dealers Please Take Note"))

It works for the column L but it shows #VALUE when I use it on column M. How is that so. Help me guru! Thanks!

 

[johnko]

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

Hi There, I had created this
=IF(L22 < >" ",IF(L22 < WORKDAY(TODAY(),2,),"To liquidate",IF(L22 < WORKDAY(TODAY(),4,),"To liquidate or waiting for approval","Dealers please take note"))),IF(M22 < WORKDAY(TODAY(),2),"To liquidate",IF(M22 < WORKDAY(TODAY(),4),"To liquidate or waiting for approval","Dealers Please Take Note"))<workday(today(),2,),"to liquidate="" or="" waiting="" for="" Note?))

It works for the column L but it shows #VALUE when I use it on column M. How is that so. Help me guru! Thanks! </workday(today(),2,),"to>

 

[etaf]

try this way around

=IF(L22="",IF(M22 < WORKDAY(TODAY(),2),"To liquidate",IF(M22 < WORKDAY(TODAY(),4),"To liquidate or waiting for approval","Dealers Please Take Note")),IF(L22 < WORKDAY(TODAY(),2,),"To liquidate",IF(L22 < WORKDAY(TODAY(),4,),"To liquidate or waiting for approval","Dealers please take note")))

 

[johnko]

try this way around

=IF(L22="",IF(M22 < WORKDAY(TODAY(),2),"To liquidate",IF(M22 < WORKDAY(TODAY(),4),"To liquidate or waiting for approval","Dealers Please Take Note")),IF(L22 < WORKDAY(TODAY(),2,),"To liquidate",IF(L22 < WORKDAY(TODAY(),4,),"To liquidate or waiting for approval","Dealers please take note")))

Hi there! I have tried doing it alone and it works! Thanks! While this is completed, I have another set of data needed to be code, are you available to take a look at it? I will upload to box.com and share the link here. Thanks!

 

[etaf]

if a different question, why not start a new thread and then other members will also look at that new thread
and just add a link here to the new thread and I will also see it

 

[johnko]

if a different question, why not start a new thread and then other members will also look at that new thread
and just add a link here to the new thread and I will also see it

Hi etaf! Here's the link! Thanks!

http://www.mrexcel.com/forum/excel-questions/963588-sorting-visual-basic-applications.html

 

[etaf]

i'm afraid i do not provide VBA on forums - not that proficient