| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text Time Coversion Arithmatic | Excel Discussion (Misc queries) | |||
| Text Time Conversion Calculation | Excel Worksheet Functions | |||
| Get External Data - not editable using Query Wizard | Excel Discussion (Misc queries) | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
