Date Compare FormulaPrevious TopicNext Topic

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?

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)

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.

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.

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",???)