Formula Query - Using Time & Text
 

I am trying to create a conditional formula where if a certain time period is
not met, the next cell to the right comes up 'Due'. To the right of the Due
column is a 'Sent' column for users to enter that a reminder email that their
task is overdue. When Sent is entered into the cell, I need the Due to be
removed.

Currently, I have;

IF(AND(G3<NOW(),I3="Sent"),"Due","")

where column G contains the time and date and column I is the Sent column.
If I remove the ,I3="Sent" section, 'Due' works but is not removed when Sent
is entered (obviously because I haven't told it too!) but when I include the
extra section, it doesn't work at all!

Please can someone advise?

Formula Query - Using Time & Text
 

Try this:

IF(I3="Sent","",IF(G3<NOW(),"Due",""))

Hope this helps.

Pete

JDB wrote:
I am trying to create a conditional formula where if a certain time period is
not met, the next cell to the right comes up 'Due'. To the right of the Due
column is a 'Sent' column for users to enter that a reminder email that their
task is overdue. When Sent is entered into the cell, I need the Due to be
removed.

Currently, I have;

IF(AND(G3<NOW(),I3="Sent"),"Due","")

where column G contains the time and date and column I is the Sent column.
If I remove the ,I3="Sent" section, 'Due' works but is not removed when Sent
is entered (obviously because I haven't told it too!) but when I include the
extra section, it doesn't work at all!

Please can someone advise?

Formula Query - Using Time & Text
 

Just a small logic error. Try this:

IF(AND(G3<NOW(),I3<"Sent"),"Due","")

--
Carlos

"JDB" wrote in message
...
I am trying to create a conditional formula where if a certain time period
is
not met, the next cell to the right comes up 'Due'. To the right of the
Due
column is a 'Sent' column for users to enter that a reminder email that
their
task is overdue. When Sent is entered into the cell, I need the Due to be
removed.

Currently, I have;

IF(AND(G3<NOW(),I3="Sent"),"Due","")

where column G contains the time and date and column I is the Sent column.
If I remove the ,I3="Sent" section, 'Due' works but is not removed when
Sent
is entered (obviously because I haven't told it too!) but when I include
the
extra section, it doesn't work at all!

Please can someone advise?

Formula Query - Using Time & Text
 

I have tried both of the above approaches. Whilst they work in making the
task overdue, when Sent is entered into the I column, the 'Due' is not
removed.

"CarlosAntenna" wrote:

Just a small logic error. Try this:

IF(AND(G3<NOW(),I3<"Sent"),"Due","")

--
Carlos

"JDB" wrote in message
...
I am trying to create a conditional formula where if a certain time period
is
not met, the next cell to the right comes up 'Due'. To the right of the
Due
column is a 'Sent' column for users to enter that a reminder email that
their
task is overdue. When Sent is entered into the cell, I need the Due to be
removed.

Currently, I have;

IF(AND(G3<NOW(),I3="Sent"),"Due","")

where column G contains the time and date and column I is the Sent column.
If I remove the ,I3="Sent" section, 'Due' works but is not removed when
Sent
is entered (obviously because I haven't told it too!) but when I include
the
extra section, it doesn't work at all!

Please can someone advise?

Formula Query - Using Time & Text
 

Perhaps your workbook is set for manual calculation - press F9 to see
if it changes. To set for automatic calculation, go to Tools | Options
| Calculation and ensure that Automatic is checked, then click OK.

Hope this helps.

Pete

JDB wrote:
I have tried both of the above approaches. Whilst they work in making the
task overdue, when Sent is entered into the I column, the 'Due' is not
removed.

"CarlosAntenna" wrote:

Just a small logic error. Try this:

IF(AND(G3<NOW(),I3<"Sent"),"Due","")

--
Carlos

"JDB" wrote in message
...
I am trying to create a conditional formula where if a certain time period
is
not met, the next cell to the right comes up 'Due'. To the right of the
Due
column is a 'Sent' column for users to enter that a reminder email that
their
task is overdue. When Sent is entered into the cell, I need the Due to be
removed.

Currently, I have;

IF(AND(G3<NOW(),I3="Sent"),"Due","")

where column G contains the time and date and column I is the Sent column.
If I remove the ,I3="Sent" section, 'Due' works but is not removed when
Sent
is entered (obviously because I haven't told it too!) but when I include
the
extra section, it doesn't work at all!

Please can someone advise?

Formula Query - Using Time & Text
 

Checked that already. Is set to Automatic calculation! I'm having to create
these sheets due to a new processes. This formula was in place on the
original sheet and works on that one. I'm really confused!!

I really appreciate the responses!

"Pete_UK" wrote:

Perhaps your workbook is set for manual calculation - press F9 to see
if it changes. To set for automatic calculation, go to Tools | Options
| Calculation and ensure that Automatic is checked, then click OK.

Hope this helps.

Pete

Formula Query - Using Time & Text
 

Forget the below! Had a Doh! moment. Users were entering a space after the
word Sent and therefore excel didn't recognise it as a condition for the
formula!

"JDB" wrote:

I am trying to create a conditional formula where if a certain time period is
not met, the next cell to the right comes up 'Due'. To the right of the Due
column is a 'Sent' column for users to enter that a reminder email that their
task is overdue. When Sent is entered into the cell, I need the Due to be
removed.

Currently, I have;

IF(AND(G3<NOW(),I3="Sent"),"Due","")

where column G contains the time and date and column I is the Sent column.
If I remove the ,I3="Sent" section, 'Due' works but is not removed when Sent
is entered (obviously because I haven't told it too!) but when I include the
extra section, it doesn't work at all!

Please can someone advise?

Formula Query - Using Time & Text
 

Trim their input:

=IF(AND(G3<NOW(),TRIM(I3)<"Sent"),"Due","")

--
Carlos

"JDB" wrote in message
...
Forget the below! Had a Doh! moment. Users were entering a space after the
word Sent and therefore excel didn't recognise it as a condition for the
formula!

"JDB" wrote:

I am trying to create a conditional formula where if a certain time
period is
not met, the next cell to the right comes up 'Due'. To the right of the
Due
column is a 'Sent' column for users to enter that a reminder email that
their
task is overdue. When Sent is entered into the cell, I need the Due to be
removed.

Currently, I have;

IF(AND(G3<NOW(),I3="Sent"),"Due","")

where column G contains the time and date and column I is the Sent
column.
If I remove the ,I3="Sent" section, 'Due' works but is not removed when
Sent
is entered (obviously because I haven't told it too!) but when I include
the
extra section, it doesn't work at all!

Please can someone advise?