# Nested If Functions that create dates but if dates falls on weekend change date to monday

1. ## Nested If Functions that create dates but if dates falls on weekend change date to monday

I am using a function that is estimating Dates that an event will occur..

Columns
A = Order date
B = Process Order Date
C = Payment process date
D = Ship Date
E = Delivery Date

These cells contain dates that have or will occur (Actual Dates filled in when they occur)
A2, B2, C2, D2, E2

Columns for Etimate
AA = Order date
AB = Process Order Date
AC = Payment process date
AD = Ship Date
AE = Delivery Date
These cells contain the Estimated date that an even will occur (Estimated Dates of occurrence Formula filled in)
AA2, AB2, AC2, AD2, AE2

Rows 2 - 1000 are each order

There is other calculations taking place doing Averages, Trends based on type of order and items ordered but I have that working so no need to
include that complex sheet. and thats the output on \$Ax\$2 that I add to the Future Process Order Date

``Please Login or Register  to view this content.``

2. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

You almost had it with this
=WORKDAY(IF(\$B2="",\$A2+\$AQ\$1,\$L2),1)

Try
=WORKDAY(IF(\$B2="",\$A2+\$AQ\$1,\$L2)-1,1)

3. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

Originally Posted by Jonmo1
You almost had it with this
=WORKDAY(IF(\$B2="",\$A2+\$AQ\$1,\$L2),1)

Try
=WORKDAY(IF(\$B2="",\$A2+\$AQ\$1,\$L2)-1,1)
So close but yet so far...

It's always the little things that get in the way...
Thanks for your help, you don't know how I kept going right around the -1 but know I understand what it's for.

4. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

You're welcome.

5. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

I have anoth quick question about the problem...

My Code looks like this,
``Please Login or Register  to view this content.``
Is there a way to qualify the result before it's inserted into the Cell

My Problem now is that the date generated could have already passed due.
to help with the future date estimation if the date has not occured yet, so I was thinking about something like this

``Please Login or Register  to view this content.``
Would this work,

6. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

It works as in there is no error. Not sure I understand what you want it to do though..

Here's what it does.
If D238 = "", then it returns ""
Else if D238 Does Not = ""
If P238 = ""
If Z238 < today, it uses Today+1 for the workday function
If Z238 is NOT < today, it uses Z238+1 for the workday function
Else If P238 Does Not = ""
It uses P238 for the workday function

7. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

Originally Posted by Jonmo1
It works as in there is no error. Not sure I understand what you want it to do though..

Here's what it does.
If D238 = "", then it returns ""
Else if D238 Does Not = ""
If P238 = ""
If Z238 < today, it uses Today+1 for the workday function
If Z238 is NOT < today, it uses Z238+1 for the workday function
Else If P238 Does Not = ""
It uses P238 for the workday function

Here's what I am looking for. I made a mistake in the formula that made it work but not what I want.
in AA2
If D2 = "", then it returns "" (row is blank do nothing)
Else if D2 Does Not = "" (row is active)
If P2 = "" (Order not received - No Date)
Else If P2 Does Not = "" It uses P2 for the workday function
If AA2 < today, it uses Today+1 for the workday function (if the Estimated Receive Date is = to or less than today then make it (Order not received - No Date)tomorrow or Monday if not workday)
If AA2 is NOT < today, it uses Z2+1 for the workday function (Z2 Is the Estimated Shipped Date should be received by next day)

So in the creation of the date I need to check to see if it is = to or less than todays date, if it is use todays date in AA2 instead of Z2+1

IF(\$D2="","",WORKDAY(IF(\$P2="",\$Z2+1 IF(Z2<=TODAY(), TODAY()+1, \$P2), \$P2)-1,1)

This creates a unknown date error 1/1/1900

8. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

Keep your references correct, what you just wrote is quite confusing.
You reference D238, then D2. Is D2 supposed to be D238??
What about AA2, is that supposed to be AA238 ?
The first line says the formula is in AA2, so the formula cannot reference AA2, that's a circular reference.

perhaps you can post a sample book, and show your expected results?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. 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 shown, mock them up manually if necessary.

Remember to desensitize the data.

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

View Pic

9. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

Attachment 325257

I had to cut out or sanitize the sheet and it took some time because the sheer amount of data and calculations going on, in the attached sheet it only includes one product
in actuallity there are 50 products. and othe rcalculations that are not part of this problem

The Formulas are alot larger with them. So this is the shortend version.

The References are different in the sheet due to columns I removed to sanitisze the sheet, and to make it smaller.

Columns A - P are filled in as the events occur so nothing is needed to be done with them

Columns Q - U are the Estimated dates that the event might occur
Based on Averages of past orders that are complete This is the area I am trying to work with.

Cells V1 - AB1 are the Averages Data Feilds for the formulas

*** each event occurs and it filled in (A - P) the Formulas in Q - U try to estimate whe the next event will occure based on past
averages.

Sometimes Products being made will hit snags alond the way and they are delayed as time goes on the orders take longer
to go out the door. This process can take longer and longer pushing the future dates out farther. An Orders life span can be 120 -150 days
so the member would like to know when to expect the next stage in the Manufacture / Order Process.

10. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

You have circular references in column T
This formula is entered into T2
=IF(\$C2="","",WORKDAY(IF(\$M2="",\$S2+\$Z\$1 IF(T2<=TODAY(), TODAY()+1, \$M2), \$M2)-1,1))
That's a circular reference, you can't do that.
A formula can't refer to the same cell that contains the formula.

11. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

Ok, so if I want to check if the date is a old or past date I have to first do the workday formula then once the workday formula is created check to see if it's an old date
in seperate cells.

12. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

Sorry but I'm having a hard time following you..

Perhaps you need to do

=IF(WORKDAY(...)<=TODAY(),"Do something else",WORKDAY())

You basically have to do the workday function twice.
Once to check if it's <=TODAY, if it IS, then do something else, if it's not, do the workday function again.

Or
Put the workday functino by itself in one cell.
Then use another cell to test if that is <=today

T2: =WORKDAY(....)
S2: =IF(T2<=TODAY(),"Do something else",T2)

13. ## Re: Nested If Functions that create dates but if dates falls on weekend change date to mon

Originally Posted by Jonmo1
Sorry but I'm having a hard time following you..

Perhaps you need to do

=IF(WORKDAY(...)<=TODAY(),"Do something else",WORKDAY())

You basically have to do the workday function twice.
Once to check if it's <=TODAY, if it IS, then do something else, if it's not, do the workday function again.

Or
Put the workday functino by itself in one cell.
Then use another cell to test if that is <=today

T2: =WORKDAY(....)
S2: =IF(T2<=TODAY(),"Do something else",T2)
Don't be Sorry, I have been agonising on how to even do this,

At first I tried the TRENDS function then I tried the FORCASTING function with no luck
so this is the best convoluted way I could figure out hoe to do this in a formula style since I can't Use VB in this environment,

If there is a better way to take a column of dates and average it with another range of dates to forcast or predict when the next date will occur
based on the past dates I would love to know how to do it.

I am going to try the second Option

##### Users Browsing this Thread

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

#### 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