+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    01-06-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    53

    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.
    Last edited by 2k05gt; 06-06-2014 at 06:52 PM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #3
    Registered User
    Join Date
    01-06-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    53

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

    Quote Originally Posted by Jonmo1 View Post
    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. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

    You're welcome.

  5. #5
    Registered User
    Join Date
    01-06-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    53

    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. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #7
    Registered User
    Join Date
    01-06-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    53

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

    Quote Originally Posted by Jonmo1 View Post
    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
    Last edited by 2k05gt; 06-13-2014 at 11:09 AM.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #9
    Registered User
    Join Date
    01-06-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    53

    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.
    Attached Files Attached Files
    Last edited by 2k05gt; 06-13-2014 at 04:07 PM.

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #11
    Registered User
    Join Date
    01-06-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    53

    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. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #13
    Registered User
    Join Date
    01-06-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    53

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

    Quote Originally Posted by Jonmo1 View Post
    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
    Last edited by 2k05gt; 06-13-2014 at 05:10 PM.

+ 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. [SOLVED] How to compute if a date falls between two specified dates??
    By Swagata in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-12-2012, 07:48 AM
  2. [SOLVED] Value with Corresponding Date if Falls Within Range of Dates
    By ebdmbfan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-26-2012, 09:14 AM
  3. Change the row background color by weekend dates
    By kammariarun in forum Excel General
    Replies: 4
    Last Post: 11-15-2011, 09:49 AM
  4. returning a value if a date falls between two dates
    By chillangella in forum Excel General
    Replies: 3
    Last Post: 09-28-2009, 06:51 AM
  5. [SOLVED] How to find if a date falls between 2 dates
    By JHL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2005, 01:50 PM

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