Formula not working/updating.

[doumamar]

Hi,

I have built a reminder function which evaluated whether TODAY() is inbetween 2 different dates. Using the below function:

=IF(J6>=H6,IF(J6<=I6,"TRUE","FALSE"),"FALSE")

However the formula comes up as TRUE when:

Today = 29/07/2010
Lower bound = 14/07/2010
Upper bound = 23/07/2010

Any ideas what is going wrong? I even re wrote the formula and it still appeared TRUE. Yet it works for most of the other dates?

Thanks
David

 

[WaterGypsy]

Works OK for me .... are you sure that H6 & I6 are date format fields or are they text fields that look like dates?

 

[Andrew Poulsom]

With this data in H6:I6:

<TABLE style="WIDTH: 159pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=213 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=3 width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=71 align=right x:num="40373">14/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=71 align=right x:num="40382">23/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=71 align=right x:num="40388" x:fmla="=TODAY()">29/07/2010</TD></TR></TBODY></TABLE>

your formula returned FALSE, as did:

=AND(J6>=H6,J6<=I6)

 

[doumamar]

Yes they are all in the exact same date format. It is ridiculous, it works perfectly for the rest, just 2 entries have problems.

 

[Andrew Poulsom]

Use the ISNUMBER function to check that each cell contains a serial date. For example:

=ISNUMBER(H6)

will return TRUE if it's a serial date. If it returns FALSE format the cell as other than Text and press F2 then Enter.

 

[doumamar]

I used AND(J6>=H6,J6<=I6) and got TRUE still ... I don't get it, I really don't!

 

[WaterGypsy]

When you say two entries .... are you working with a number of dates in a column ?

 

[doumamar]

Poulson, you are good! The middle date was not a number on my new entries. I don't know why it isn't will have to find out. Any idea how to make sure it is? The data comes from a userform.


Thanks!!!!!

Edit: I found the input problem! Thanks for all your help!

 

[doumamar]

Right, I used the following to populate the exact cell where the date is not recognised as a date until i enter the cell and then exit it.


ActiveCell.Offset(0, 2).Value = txtRDate2.Value

Why is it that the cell is not populated with a date in the first place?

Thanks
David

 

[Andrew Poulsom]

A TextBox contains text. Try:

ActiveCell.Offset(0, 2).Value = DateValue(txtRDate2.Value)