Welcome Guest
Log In | Register )
You last visited January 16, 2017, 11:54 pm
All times shown are
Eastern Time (GMT-5:00)

Date Compare Formula

Topic closed. 7 replies. Last post 8 years ago by KnuckleHead.

Page 1 of 1
PrintE-mailLink
KnuckleHead's avatar - box

United States
Member #73037
April 3, 2009
147 Posts
Offline
Posted: June 12, 2009, 3:09 pm - IP Logged

Good afternoon,

 

I'm trying to write a date compare formula. Here's my setup:

Cell B:3 = Today's Date     "6/12/2009"

Cell B:7 = Next Draw Date     "6/12/2009"

 

This is what I've tried:

@IF(B:3=B:7,"YES","NO")

@IF(@DATEVALUE(B:3)=@DATEVALUE(B:7),"YES","NO")

@IF(@TODAY=B:3#AND#@TODAY=B:7,"YES","NO")

All of these are giving me "ERR".

Once I get the "basic" formula to work, then I can build on to it. Has anyone done this before?

          The only DUMB question is the one question you DID NOT ask...

    CARBOB's avatar - FL LOTTERY_LOGO.png
    ORLANDO, FLORIDA
    United States
    Member #4924
    June 3, 2004
    5961 Posts
    Offline
    Posted: June 12, 2009, 3:55 pm - IP Logged

    Good afternoon,

     

    I'm trying to write a date compare formula. Here's my setup:

    Cell B:3 = Today's Date     "6/12/2009"

    Cell B:7 = Next Draw Date     "6/12/2009"

     

    This is what I've tried:

    @IF(B:3=B:7,"YES","NO")

    @IF(@DATEVALUE(B:3)=@DATEVALUE(B:7),"YES","NO")

    @IF(@TODAY=B:3#AND#@TODAY=B:7,"YES","NO")

    All of these are giving me "ERR".

    Once I get the "basic" formula to work, then I can build on to it. Has anyone done this before?

    are you looking for something like this? =IF(A49=A50,"YES","NO")

    What is the @ symbol?

      Avatar
      New Mexico
      United States
      Member #58526
      February 18, 2008
      683 Posts
      Offline
      Posted: June 12, 2009, 4:03 pm - IP Logged

      Also, if this is for microsoft excel you don't need the ":" in your cell definitions.....":" is used when specificing ranges ie. A1:C10

       

      B:7 (incorrect)

      B7 (correct)

        KnuckleHead's avatar - box

        United States
        Member #73037
        April 3, 2009
        147 Posts
        Offline
        Posted: June 12, 2009, 4:20 pm - IP Logged

        Hello CARBOB and Texas Madman,

        Sorry about that, I use Lotus123 ("@" symbol) instead of Excel ("=" symbol). Most formulas and VBA work in both speadsheets. In Lotus123 the ":" is needed for a proper formula format.

        Yes CARBOB that's what I'm looking for, but I haven't been able to figure out how to get the 2 "date" cells to compare for the formula to work properly. In my examples, imagine that the "@" is "=".

        Do you need more info as to what I'm trying to do?

                  The only DUMB question is the one question you DID NOT ask...

          CARBOB's avatar - FL LOTTERY_LOGO.png
          ORLANDO, FLORIDA
          United States
          Member #4924
          June 3, 2004
          5961 Posts
          Offline
          Posted: June 12, 2009, 4:32 pm - IP Logged

          Hello CARBOB and Texas Madman,

          Sorry about that, I use Lotus123 ("@" symbol) instead of Excel ("=" symbol). Most formulas and VBA work in both speadsheets. In Lotus123 the ":" is needed for a proper formula format.

          Yes CARBOB that's what I'm looking for, but I haven't been able to figure out how to get the 2 "date" cells to compare for the formula to work properly. In my examples, imagine that the "@" is "=".

          Do you need more info as to what I'm trying to do?

          Yes, the formula I posted works. Show an example.

           

          06/12/0906/12/09YES
          06/11/0906/10/09NO
            KnuckleHead's avatar - box

            United States
            Member #73037
            April 3, 2009
            147 Posts
            Offline
            Posted: June 12, 2009, 4:56 pm - IP Logged

            CARBOB,

            Call me "BONEHEAD".

            I've been working on several diffuclt (for me) "scripts" (VBA in Excel) for several days now. I've have had so much on my mind trying to get my "automation" working correctly, that I placed the ":" in the wrong place. I didn't realise it until I saw your example. I worked on that formula for 3 hours and didn't realize what I was doing wrong... Placed your example in the cell and made the modifications from Excel to Lotus and it worked.

            Sorry to waste your time on "dumb" mistakes, and thanks for the interest.

                      The only DUMB question is the one question you DID NOT ask...

              CARBOB's avatar - FL LOTTERY_LOGO.png
              ORLANDO, FLORIDA
              United States
              Member #4924
              June 3, 2004
              5961 Posts
              Offline
              Posted: June 12, 2009, 5:17 pm - IP Logged

              CARBOB,

              Call me "BONEHEAD".

              I've been working on several diffuclt (for me) "scripts" (VBA in Excel) for several days now. I've have had so much on my mind trying to get my "automation" working correctly, that I placed the ":" in the wrong place. I didn't realise it until I saw your example. I worked on that formula for 3 hours and didn't realize what I was doing wrong... Placed your example in the cell and made the modifications from Excel to Lotus and it worked.

              Sorry to waste your time on "dumb" mistakes, and thanks for the interest.

              It wasn't a waste of time, neither was it a dumb mistake. Sometimes, you can't see clearly. It happens to me, too.

                KnuckleHead's avatar - box

                United States
                Member #73037
                April 3, 2009
                147 Posts
                Offline
                Posted: June 13, 2009, 9:45 am - IP Logged

                Morning all,

                For anyone who has to much on their minds (or is a BONEHEAD like me) and generally don't pay attention to dates, below is the formula I was trying to create. But, for those of you that are computer literate (which I'm not), please, don't laugh to loud or long...

                If anyone can use it "as is" or needs to modify it for their purposes, go to it... I posted the complete file for anyone's need or use.

                ________________________________________________

                Draw Date Formula


                How the formula works.

                This formula can be placed on each sheet.
                If you keep track of several games, then when you enter the sheet that contains this formula, at a glance, you'll know if you need to get recent drawn numbers or not.


                The Formula.

                @IF(TDN=NDDN,">>> NEW NUMBERS <<< Will Be DRAWN TODAY",@IF(TDN<NDDN,"It's Not Draw Day YET",@IF(TDN=(NDDN+1),"Get The NEW

                Drawn Numbers TODAY","YOU MISSED THE DRAW !!! - GET THE NUMBERS !!!")))


                NOTES:

                Today's Date Number ("TDN") is a cell that contains "Today's" date number. (ie. If today is 6/13/2009 = 39977)
                Next Draw Date Number ("NDDN") is a cell that contains the "Next Draw Date" number.  (ie. If the draw date is 6/12/2009 = 39976)



                INSTRUCTIONS FOR SET-UP (In Lotus 123 "@" is the same as "=" in Excel)

                1. Create a cell that contains Today's Date (@TODAY) formated as "Month,Day,Year".
                2. Create a cell that cotains the "Next Drawn Date" formated as "Month,Day,Year".
                3. At the bottom of the sheet (in an empty place) enter into a cell "@Today" (or the cell address of #1) formated as "General". Label the next cell over as "Today's Date Number". This cell is "TDN"
                4. In the next cell down, enter into the cell "@Datevalue(cell address of #2)" formated as "General". Label the next cell over as "Next Draw Date Number". This cell is "NDDN"
                5. Place the "Draw Date Formula" directly above or next to the "Today's Date" (#1) cell.


                Formula construction notes:

                1 - @IF(TDN=NDDN,">>> NEW NUMBERS <<< Will Be DRAWN TODAY",???)

                2 - @IF(TDN=(NDDN+1),"Get NEW Drawn Numbers TODAY",???)

                3 - @IF(TDN>(NDDN+1),"YOU MISSED THE DRAW !!! - GET THE NUMBERS !!!",???)

                4 - @IF(TDN<NDDN,"It's Not Draw Day YET",???)

                          The only DUMB question is the one question you DID NOT ask...