# Formula not working/updating.

#### doumamar

##### New Member
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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### WaterGypsy

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

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

#### WaterGypsy

##### Well-known Member
When you say two entries .... are you working with a number of dates in a column ?

#### doumamar

##### New Member
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!

Last edited:

#### doumamar

##### New Member
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

##### MrExcel MVP
A TextBox contains text. Try:

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

Replies
4
Views
2K
Replies
3
Views
207
Replies
6
Views
587
Replies
3
Views
774
Replies
19
Views
247

1,171,185
Messages
5,874,219
Members
433,037
Latest member
Not Moose

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back