Having trouble getting a date

J

jthomas3029

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 2010
Platform
  1. Windows
I have a proposed billing date (A1) and a status (B1) and (C1) actual send date. My formula for B1 is: =IF(AND(A1>=TODAY(),"Pending"),IF(AND(C1<>A1),"completed")). To be sure it is "volatile". The concept is to alert the user to the upcoming send date, A1, hence "pending", but once it is sent ,C1, to change the pending to "completed" in B1 I had a vlookup that worked for me based on a countdown model to TODAY which then reported completed but the user wants "completed" when the send date meets or exceeds the proposed billing date. Each of the formulas work well independently, it is when they are combined there a volatile explosion.
 
Sort by date Sort by votes
Based on the data sample, the formula that I suggested (adjusted to fit your sheet) would be
Excel Formula: 
=IF(M7>=TODAY(),"Pending",IF(P7<>"","completed"))
although I think that I may have misinterpreted it.

Which of the results in column O are incorrect and what should they be?
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
jasonb75 said:
Based on the data sample, the formula that I suggested (adjusted to fit your sheet) would be
Excel Formula: 
=IF(M7>=TODAY(),"Pending",IF(P7<>"","completed"))
although I think that I may have misinterpreted it.

Which of the results in column O are incorrect and what should they be?
Click to expand...
The send by date in M generates a status of Pending in O unless and until it is overridden with a Complete triggered by an entry in P that predates, equals or post dates the date in M. I tried using an IF formula that placed the word "Pending" in O unrelated to TODAY with P in relationship to M overriding the word Pending.
 
Upvote 0
jasonb75 said:
If you don't provide the information that I asked for then I will not be able to help you further.
Click to expand...
Scoping 2020 11 22.xlsm
ABCDEFGHIJKLMNOP
3
4Name
5AssignmentDates/RecipientBilling
6Title of JobType of Proceeding & Service LevelRatePagesRec'dDueStatusRecipientSentTotal Charged CDNProposed Billing Send DateCycleStatusActual Send Date
7  correct11/30/2020completed11/24/2020
8
9incorrect11/30/2020completed
10correct11/30/2020"pending"
11
12  incorrect11/30/2020FALSE11/30/2020
13"completed"
14
15  correct11/30/2020completed12/3/2020
16     
17     
18     
Unassigned (4)
Cell Formulas
RangeFormula
C7,C15:C18,C12C7=IF(B7="","",VLOOKUP(B7,Tables!$A$3:$B$8,2,FALSE))
H7,H15:H18,H12H7=IF(G7="","",VLOOKUP(TODAY()-G7,Tables!$A$12:$C$26,3,1))
O7,O15,O12,O9O7=IF(M7<=TODAY(),"Pending",IF(P7<>M7,"completed"))
L16:L18L16=IF((ISERROR(C16*D16))," ",(C16*D16))
M16M16=IF(J16="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$16>=Tables!$L$6:$L$30)*($J$16<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N16N16=IF(M16="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$16>=Tables!$L$6:$L$32)*($M$16<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M17M17=IF(J17="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$17>=Tables!$L$6:$L$30)*($J$17<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N17N17=IF(M17="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$17>=Tables!$L$6:$L$32)*($M$17<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M18M18=IF(J18="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$18>=Tables!$L$6:$L$30)*($J$18<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N18N18=IF(M18="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$18>=Tables!$L$6:$L$32)*($M$18<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
Named Ranges
NameRefers ToCells
'Unassigned (4)'!Billing=Date_Scheduler3436N16:N18
Billing=Date_Scheduler3436N16:N18
type=Tables!$A$3:$B$8C15:C18, C7, C12
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O7:O25Cell Valuecontains "1"textNO
O7:O25Cell Valuecontains "2"textNO
O7:O25Cell Valuecontains "3"textNO
O7:O25Cell Valuecontains "Today"textNO
O7:O25Cell Valuecontains "Pending"textNO
O7:O25Cell Valuecontains "completed"textNO
N7:N25Cell Valuecontains "Jan End"textNO
N7:N25Cell Valuecontains "Dec End"textNO
N7:N25Cell Valuecontains "Nov End"textNO
N7:N25Cell Valuecontains "Oct End"textNO
N7:N25Cell Valuecontains "Sept End"textNO
N7:N25Cell Valuecontains "Aug End"textNO
N7:N25Cell Valuecontains "July End"textNO
N7:N25Cell Valuecontains "May End"textNO
N7:N25Cell Valuecontains "Mar End"textNO
N7:N25Cell Valuecontains "June End"textNO
N7:N25Cell Valuecontains "Apr End"textNO
N7:N25Cell Valuecontains "Feb End"textNO
N7:N25Cell Valuecontains "Jan End,"textNO
H7:H25Cell Valuecontains "3"textNO
H7:H25Cell Valuecontains "2"textNO
H7:H25Cell Valuecontains "1"textNO
H7:H25Cell Valuecontains "Good"textNO
H7:H25Cell Valuecontains "Completed"textNO
H7:H25Cell Valuecontains "Today"textNO
Cells with Data Validation
CellAllowCriteria
I7:I18List=Tables!$H$1:$H$4
B7:B18List=Tables!$A$2:$A$8
 
Upvote 0
Thank you for your help and patience Jacob. I am trying to explain it as best as I can. My wife wants to know how long she has to meet her deadline in sending the document, hence the Pending but she wants the pending alert to change to completed when she sends it either before the deadline, on the deadline, or even after it has passed.
 
Upvote 0
jthomas3029 said:
Scoping 2020 11 22.xlsm
ABCDEFGHIJKLMNOP
3
4Name
5AssignmentDates/RecipientBilling
6Title of JobType of Proceeding & Service LevelRatePagesRec'dDueStatusRecipientSentTotal Charged CDNProposed Billing Send DateCycleStatusActual Send Date
7  correct11/30/2020completed11/24/2020
8
9incorrect11/30/2020completed
10correct11/30/2020"pending"
11
12  incorrect11/30/2020FALSE11/30/2020
13"completed"
14
15  correct11/30/2020completed12/3/2020
16     
17     
18     
Unassigned (4)
Cell Formulas
RangeFormula
C7,C15:C18,C12C7=IF(B7="","",VLOOKUP(B7,Tables!$A$3:$B$8,2,FALSE))
H7,H15:H18,H12H7=IF(G7="","",VLOOKUP(TODAY()-G7,Tables!$A$12:$C$26,3,1))
O7,O15,O12,O9O7=IF(M7<=TODAY(),"Pending",IF(P7<>M7,"completed"))
L16:L18L16=IF((ISERROR(C16*D16))," ",(C16*D16))
M16M16=IF(J16="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$16>=Tables!$L$6:$L$30)*($J$16<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N16N16=IF(M16="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$16>=Tables!$L$6:$L$32)*($M$16<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M17M17=IF(J17="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$17>=Tables!$L$6:$L$30)*($J$17<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N17N17=IF(M17="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$17>=Tables!$L$6:$L$32)*($M$17<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M18M18=IF(J18="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$18>=Tables!$L$6:$L$30)*($J$18<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N18N18=IF(M18="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$18>=Tables!$L$6:$L$32)*($M$18<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
Named Ranges
NameRefers ToCells
'Unassigned (4)'!Billing=Date_Scheduler3436N16:N18
Billing=Date_Scheduler3436N16:N18
type=Tables!$A$3:$B$8C15:C18, C7, C12
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O7:O25Cell Valuecontains "1"textNO
O7:O25Cell Valuecontains "2"textNO
O7:O25Cell Valuecontains "3"textNO
O7:O25Cell Valuecontains "Today"textNO
O7:O25Cell Valuecontains "Pending"textNO
O7:O25Cell Valuecontains "completed"textNO
N7:N25Cell Valuecontains "Jan End"textNO
N7:N25Cell Valuecontains "Dec End"textNO
N7:N25Cell Valuecontains "Nov End"textNO
N7:N25Cell Valuecontains "Oct End"textNO
N7:N25Cell Valuecontains "Sept End"textNO
N7:N25Cell Valuecontains "Aug End"textNO
N7:N25Cell Valuecontains "July End"textNO
N7:N25Cell Valuecontains "May End"textNO
N7:N25Cell Valuecontains "Mar End"textNO
N7:N25Cell Valuecontains "June End"textNO
N7:N25Cell Valuecontains "Apr End"textNO
N7:N25Cell Valuecontains "Feb End"textNO
N7:N25Cell Valuecontains "Jan End,"textNO
H7:H25Cell Valuecontains "3"textNO
H7:H25Cell Valuecontains "2"textNO
H7:H25Cell Valuecontains "1"textNO
H7:H25Cell Valuecontains "Good"textNO
H7:H25Cell Valuecontains "Completed"textNO
H7:H25Cell Valuecontains "Today"textNO
Cells with Data Validation
CellAllowCriteria
I7:I18List=Tables!$H$1:$H$4
B7:B18List=Tables!$A$2:$A$8
Click to expand...
The table page should have not been included...my bad
 
Upvote 0
Scoping 2020 11 22.xlsm
ABCDEFGHIJKLMNOP
3
4Name
5AssignmentDates/RecipientBilling
6Title of JobType of Proceeding & Service LevelRatePagesRec'dDueStatusRecipientSentTotal Charged CDNProposed Billing Send DateCycleStatusActual Send Date
7  correct11/30/2020completed11/24/2020
8
9incorrect11/30/2020completed
10correct11/30/2020"pending"
11
12  incorrect11/30/2020FALSE11/30/2020
13"completed"
14
15  correct11/30/2020completed12/3/2020
16     
17     
18     
19  
20  
21     
22      
23      
24      
25      
26
Unassigned (4)
Cell Formulas
RangeFormula
C7,C15:C25,C12C7=IF(B7="","",VLOOKUP(B7,#REF!$A$3:$B$8,2,FALSE))
H7,H15:H25,H12H7=IF(G7="","",VLOOKUP(TODAY()-G7,#REF!$A$12:$C$26,3,1))
O7,O15,O12,O9O7=IF(M7<=TODAY(),"Pending",IF(P7<>M7,"completed"))
L21:L25,L16:L18L16=IF((ISERROR(C16*D16))," ",(C16*D16))
M16M16=IF(J16="","",INDEX(#REF!$K$6:$K$30,SUMPRODUCT(($J$16>=#REF!$L$6:$L$30)*($J$16<=#REF!$M$6:$M$30)*MATCH(ROW(#REF!$K$6:$K$30),ROW(#REF!$K$6:$K$30)))))
N16N16=IF(M16="","",INDEX(#REF!$J$6:$J$32,SUMPRODUCT(($M$16>=#REF!$L$6:$L$32)*($M$16<=#REF!$M$6:$M$32)*MATCH(ROW(#REF!$K$6:$K$32),ROW(#REF!$K$6:$K$32)))))
M17M17=IF(J17="","",INDEX(#REF!$K$6:$K$30,SUMPRODUCT(($J$17>=#REF!$L$6:$L$30)*($J$17<=#REF!$M$6:$M$30)*MATCH(ROW(#REF!$K$6:$K$30),ROW(#REF!$K$6:$K$30)))))
N17N17=IF(M17="","",INDEX(#REF!$J$6:$J$32,SUMPRODUCT(($M$17>=#REF!$L$6:$L$32)*($M$17<=#REF!$M$6:$M$32)*MATCH(ROW(#REF!$K$6:$K$32),ROW(#REF!$K$6:$K$32)))))
M18M18=IF(J18="","",INDEX(#REF!$K$6:$K$30,SUMPRODUCT(($J$18>=#REF!$L$6:$L$30)*($J$18<=#REF!$M$6:$M$30)*MATCH(ROW(#REF!$K$6:$K$30),ROW(#REF!$K$6:$K$30)))))
N18N18=IF(M18="","",INDEX(#REF!$J$6:$J$32,SUMPRODUCT(($M$18>=#REF!$L$6:$L$32)*($M$18<=#REF!$M$6:$M$32)*MATCH(ROW(#REF!$K$6:$K$32),ROW(#REF!$K$6:$K$32)))))
M21M21=IF(J21="","",INDEX(#REF!$K$6:$K$30,SUMPRODUCT(($J$21>=#REF!$L$6:$L$30)*($J$21<=#REF!$M$6:$M$30)*MATCH(ROW(#REF!$K$6:$K$30),ROW(#REF!$K$6:$K$30)))))
N21N21=IF(M21="","",INDEX(#REF!$J$6:$J$32,SUMPRODUCT(($M$21>=#REF!$L$6:$L$32)*($M$21<=#REF!$M$6:$M$32)*MATCH(ROW(#REF!$K$6:$K$32),ROW(#REF!$K$6:$K$32)))))
M22M22=IF(J22="","",INDEX(#REF!$K$6:$K$30,SUMPRODUCT(($J$22>=#REF!$L$6:$L$30)*($J$22<=#REF!$M$6:$M$30)*MATCH(ROW(#REF!$K$6:$K$30),ROW(#REF!$K$6:$K$30)))))
N22N22=IF(M22="","",INDEX(#REF!$J$6:$J$32,SUMPRODUCT(($M$22>=#REF!$L$6:$L$32)*($M$22<=#REF!$M$6:$M$32)*MATCH(ROW(#REF!$K$6:$K$32),ROW(#REF!$K$6:$K$32)))))
O22:O25O22=IF(M22="","",VLOOKUP(TODAY()-M22,#REF!$E$3:$F$17,2,1))
M23M23=IF(J23="","",INDEX(#REF!$K$6:$K$30,SUMPRODUCT(($J$23>=#REF!$L$6:$L$30)*($J$23<=#REF!$M$6:$M$30)*MATCH(ROW(#REF!$K$6:$K$30),ROW(#REF!$K$6:$K$30)))))
N23N23=IF(M23="","",INDEX(#REF!$J$6:$J$32,SUMPRODUCT(($M$23>=#REF!$L$6:$L$32)*($M$23<=#REF!$M$6:$M$32)*MATCH(ROW(#REF!$K$6:$K$32),ROW(#REF!$K$6:$K$32)))))
M24M24=IF(J24="","",INDEX(#REF!$K$6:$K$30,SUMPRODUCT(($J$24>=#REF!$L$6:$L$30)*($J$24<=#REF!$M$6:$M$30)*MATCH(ROW(#REF!$K$6:$K$30),ROW(#REF!$K$6:$K$30)))))
N24N24=IF(M24="","",INDEX(#REF!$J$6:$J$32,SUMPRODUCT(($M$24>=#REF!$L$6:$L$32)*($M$24<=#REF!$M$6:$M$32)*MATCH(ROW(#REF!$K$6:$K$32),ROW(#REF!$K$6:$K$32)))))
M25M25=IF(J25="","",INDEX(#REF!$K$6:$K$30,SUMPRODUCT(($J$25>=#REF!$L$6:$L$30)*($J$25<=#REF!$M$6:$M$30)*MATCH(ROW(#REF!$K$6:$K$30),ROW(#REF!$K$6:$K$30)))))
N25N25=IF(M25="","",INDEX(#REF!$J$6:$J$32,SUMPRODUCT(($M$25>=#REF!$L$6:$L$32)*($M$25<=#REF!$M$6:$M$32)*MATCH(ROW(#REF!$K$6:$K$32),ROW(#REF!$K$6:$K$32)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O7:O25Cell Valuecontains "1"textNO
O7:O25Cell Valuecontains "2"textNO
O7:O25Cell Valuecontains "3"textNO
O7:O25Cell Valuecontains "Today"textNO
O7:O25Cell Valuecontains "Pending"textNO
O7:O25Cell Valuecontains "completed"textNO
N7:N25Cell Valuecontains "Jan End"textNO
N7:N25Cell Valuecontains "Dec End"textNO
N7:N25Cell Valuecontains "Nov End"textNO
N7:N25Cell Valuecontains "Oct End"textNO
N7:N25Cell Valuecontains "Sept End"textNO
N7:N25Cell Valuecontains "Aug End"textNO
N7:N25Cell Valuecontains "July End"textNO
N7:N25Cell Valuecontains "May End"textNO
N7:N25Cell Valuecontains "Mar End"textNO
N7:N25Cell Valuecontains "June End"textNO
N7:N25Cell Valuecontains "Apr End"textNO
N7:N25Cell Valuecontains "Feb End"textNO
N7:N25Cell Valuecontains "Jan End,"textNO
H7:H25Cell Valuecontains "3"textNO
H7:H25Cell Valuecontains "2"textNO
H7:H25Cell Valuecontains "1"textNO
H7:H25Cell Valuecontains "Good"textNO
H7:H25Cell Valuecontains "Completed"textNO
H7:H25Cell Valuecontains "Today"textNO
Cells with Data Validation
CellAllowCriteria
I7:I25List=#REF!$H$1:$H$4
B7:B25List=#REF!$A$2:$A$8
 
Upvote 0
Is this what you want?
Excel Formula: 
=IF(P7<>"","Complete",IF(M7<>"","Pending",""))
 
Upvote 0
Solution
Fluff said:
Is this what you want?
Excel Formula: 
=IF(P7<>"","Complete",IF(M7<>"","Pending",""))
Click to expand...
Yes, OMG, yes....sorry, I could not stop "playing" with the formula. It looks like I was backasswards in my thoughts in placing the pending before the complete.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
330
keranali
K
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
L
  • Solved
If Formula with multiple criteria to have return value depending on dates
Replies
3
Views
408
Habtest
Habtest
V
  • Question Question
Date formula with multiple conditions and nested ifs
Replies
6
Views
663
Vbanoob98
V
C
  • Question Question
IF Function -- Based on two cells
Replies
2
Views
682
Fluff
Fluff

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top