1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Formula for future date

Discussion in 'Business Applications' started by scsisys, Mar 23, 2010.

Thread Status:
Not open for further replies.
  1. scsisys

    scsisys Thread Starter

    Joined:
    Oct 29, 2003
    Messages:
    92
    Using OOCalc 3.1.1:

    Was looking for a formula to show a future (monthly) date for an event to take place and found the following:

    cell A1: =DATE(YEAR(A4);MONTH(A4)+1*(DAY(A4)>=1);1)
    cell A4: =TODAY()

    where, when the 1st of the month happens, A1's date would increment to the next month.

    For example: On 03-31-10 (cell A4), 04-01-10 would be showing in (cell A1). When A4 goes to 04-01-10,
    A1 goes to 05-01-10 ( MM/DD/YY format)

    In trying to figure out how the formula worked, I tried the Function Help files but that didn't provide any useful
    info. What I don't understand about the formula in A1 is the * in front of *(DAY(A4)>=1);1) . Normally, the
    * is used as a multiplication operator; however, it doesn't make sense (to me) in this situation.

    What I've been trying to do is modify the DATE formula to do basically the same thing but incrementing
    either weekly or bi-weekly ( 04-01-10 to 04-08-10 weekly; 04-01-10 to 04-15-10 bi-weekly). Just don't know
    enough .

    Was also wanting a yearly incrementation and after making untold number of modifications, finally got it
    to work. =DATE(YEAR(A4)+1;MONTH(A4);DAY(A4)>=1) . But the *(DAY(A4)>=1);1) got modified to
    DAY(A4)>=1) with the *( getting deleted as well as the ;1 .

    Can anyone enlighten me as to how/what purpose the * serves , as well as the ;1 in the original
    formula ??

    thanks
    ray
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    You can try this, it displays the first of the next month

    =DATE(YEAR(A4),MONTH(A4)+1,(DAY(A4)>=1))

    When you see the * in your formula it can also be used as an "AND" condition, a PLUS + would be an "OR" condition but it depends how the formula is constructed as to whether it will calculate or interpret

    eg...

    This does not work..

    =IF(B1=1 * B4=3,"True","False")

    put brackets around...

    =IF((B1=1)*(B4=3),"True","False")
     
  3. scsisys

    scsisys Thread Starter

    Joined:
    Oct 29, 2003
    Messages:
    92
    Thanks Ziggy1......appreciate your explanation.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/912187

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice