Welcome Guest
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

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

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?

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)

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

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

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5961 Posts
Offline
 Posted: June 12, 2009, 4:32 pm - IP Logged

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/09 06/12/09 YES 06/11/09 06/10/09 NO

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

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

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5961 Posts
Offline
 Posted: June 12, 2009, 5:17 pm - IP Logged

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.

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

 Page 1 of 1