Having trouble getting a date

jthomas3029 · Nov 21, 2020

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.

jasonb75 · Nov 23, 2020

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?

jthomas3029 · Nov 23, 2020

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?

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.

jasonb75 · Nov 23, 2020

If you don't provide the information that I asked for then I will not be able to help you further.

jasonb75 said:
a sample of A1 and C1 combinations with the expected results

jasonb75 said:
Which of the results in column O are incorrect and what should they be?

jthomas3029 · Nov 23, 2020

jasonb75 said:
If you don't provide the information that I asked for then I will not be able to help you further.

Scoping 2020 11 22.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

3

4

Name

5

Assignment

Dates/Recipient

Billing

6

Title of Job

Type of Proceeding & Service Level

Rate

Pages

Rec'd

Due

Status

Recipient

Sent

Total Charged CDN

Proposed Billing Send Date

Cycle

Status

Actual Send Date

7

correct

11/30/2020

completed

11/24/2020

8

9

incorrect

11/30/2020

completed

10

correct

11/30/2020

"pending"

11

12

incorrect

11/30/2020

FALSE

11/30/2020

13

"completed"

14

15

correct

11/30/2020

completed

12/3/2020

16

17

18

Unassigned (4)

Cell Formulas
Range	Formula
C7,C15:C18,C12	C7	=IF(B7="","",VLOOKUP(B7,Tables!$A$3:$B$8,2,FALSE))
H7,H15:H18,H12	H7	=IF(G7="","",VLOOKUP(TODAY()-G7,Tables!$A$12:$C$26,3,1))
O7,O15,O12,O9	O7	=IF(M7<=TODAY(),"Pending",IF(P7<>M7,"completed"))
L16:L18	L16	=IF((ISERROR(C16D16))," ",(C16D16))
M16	M16	=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)))))
N16	N16	=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)))))
M17	M17	=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)))))
N17	N17	=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)))))
M18	M18	=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)))))
N18	N18	=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
Name	Refers To	Cells
'Unassigned (4)'!Billing	=Date_Scheduler3436	N16:N18
Billing	=Date_Scheduler3436	N16:N18
type	=Tables!$A$3:$B$8	C15:C18, C7, C12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O7:O25	Cell Value	contains "1"	text	NO
O7:O25	Cell Value	contains "2"	text	NO
O7:O25	Cell Value	contains "3"	text	NO
O7:O25	Cell Value	contains "Today"	text	NO
O7:O25	Cell Value	contains "Pending"	text	NO
O7:O25	Cell Value	contains "completed"	text	NO
N7:N25	Cell Value	contains "Jan End"	text	NO
N7:N25	Cell Value	contains "Dec End"	text	NO
N7:N25	Cell Value	contains "Nov End"	text	NO
N7:N25	Cell Value	contains "Oct End"	text	NO
N7:N25	Cell Value	contains "Sept End"	text	NO
N7:N25	Cell Value	contains "Aug End"	text	NO
N7:N25	Cell Value	contains "July End"	text	NO
N7:N25	Cell Value	contains "May End"	text	NO
N7:N25	Cell Value	contains "Mar End"	text	NO
N7:N25	Cell Value	contains "June End"	text	NO
N7:N25	Cell Value	contains "Apr End"	text	NO
N7:N25	Cell Value	contains "Feb End"	text	NO
N7:N25	Cell Value	contains "Jan End,"	text	NO
H7:H25	Cell Value	contains "3"	text	NO
H7:H25	Cell Value	contains "2"	text	NO
H7:H25	Cell Value	contains "1"	text	NO
H7:H25	Cell Value	contains "Good"	text	NO
H7:H25	Cell Value	contains "Completed"	text	NO
H7:H25	Cell Value	contains "Today"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I7:I18	List	=Tables!$H$1:$H$4
B7:B18	List	=Tables!$A$2:$A$8

jthomas3029 · Nov 23, 2020

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.

jthomas3029 · Nov 23, 2020

jthomas3029 said:
Scoping 2020 11 22.xlsm
A B C D E F G H I J K L M N O P
3
4 Name
5 Assignment Dates/Recipient Billing
6 Title of Job Type of Proceeding & Service Level Rate Pages Rec'd Due Status Recipient Sent Total Charged CDN Proposed Billing Send Date Cycle Status Actual Send Date
7 correct 11/30/2020 completed 11/24/2020
8
9 incorrect 11/30/2020 completed
10 correct 11/30/2020 "pending"
11
12 incorrect 11/30/2020 FALSE 11/30/2020
13 "completed"
14
15 correct 11/30/2020 completed 12/3/2020
16
17
18
Unassigned (4)
Cell Formulas
Range Formula
C7,C15:C18,C12 C7 =IF(B7="","",VLOOKUP(B7,Tables!$A$3:$B$8,2,FALSE))
H7,H15:H18,H12 H7 =IF(G7="","",VLOOKUP(TODAY()-G7,Tables!$A$12:$C$26,3,1))
O7,O15,O12,O9 O7 =IF(M7<=TODAY(),"Pending",IF(P7<>M7,"completed"))
L16:L18 L16 =IF((ISERROR(C16*D16))," ",(C16*D16))
M16 M16 =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)))))
N16 N16 =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)))))
M17 M17 =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)))))
N17 N17 =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)))))
M18 M18 =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)))))
N18 N18 =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
Name Refers To Cells
'Unassigned (4)'!Billing =Date_Scheduler3436 N16:N18
Billing =Date_Scheduler3436 N16:N18
type =Tables!$A$3:$B$8 C15:C18, C7, C12
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
O7:O25 Cell Value contains "1" text NO
O7:O25 Cell Value contains "2" text NO
O7:O25 Cell Value contains "3" text NO
O7:O25 Cell Value contains "Today" text NO
O7:O25 Cell Value contains "Pending" text NO
O7:O25 Cell Value contains "completed" text NO
N7:N25 Cell Value contains "Jan End" text NO
N7:N25 Cell Value contains "Dec End" text NO
N7:N25 Cell Value contains "Nov End" text NO
N7:N25 Cell Value contains "Oct End" text NO
N7:N25 Cell Value contains "Sept End" text NO
N7:N25 Cell Value contains "Aug End" text NO
N7:N25 Cell Value contains "July End" text NO
N7:N25 Cell Value contains "May End" text NO
N7:N25 Cell Value contains "Mar End" text NO
N7:N25 Cell Value contains "June End" text NO
N7:N25 Cell Value contains "Apr End" text NO
N7:N25 Cell Value contains "Feb End" text NO
N7:N25 Cell Value contains "Jan End," text NO
H7:H25 Cell Value contains "3" text NO
H7:H25 Cell Value contains "2" text NO
H7:H25 Cell Value contains "1" text NO
H7:H25 Cell Value contains "Good" text NO
H7:H25 Cell Value contains "Completed" text NO
H7:H25 Cell Value contains "Today" text NO
Cells with Data Validation
Cell Allow Criteria
I7:I18 List =Tables!$H$1:$H$4
B7:B18 List =Tables!$A$2:$A$8

The table page should have not been included...my bad

jthomas3029 · Nov 23, 2020

Scoping 2020 11 22.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

3

4

Name

5

Assignment

Dates/Recipient

Billing

6

Title of Job

Type of Proceeding & Service Level

Rate

Pages

Rec'd

Due

Status

Recipient

Sent

Total Charged CDN

Proposed Billing Send Date

Cycle

Status

Actual Send Date

7

correct

11/30/2020

completed

11/24/2020

8

9

incorrect

11/30/2020

completed

10

correct

11/30/2020

"pending"

11

12

incorrect

11/30/2020

FALSE

11/30/2020

13

"completed"

14

15

correct

11/30/2020

completed

12/3/2020

16

17

18

19

20

21

22

23

24

25

26

Unassigned (4)

Cell Formulas
Range	Formula
C7,C15:C25,C12	C7	=IF(B7="","",VLOOKUP(B7,#REF!$A$3:$B$8,2,FALSE))
H7,H15:H25,H12	H7	=IF(G7="","",VLOOKUP(TODAY()-G7,#REF!$A$12:$C$26,3,1))
O7,O15,O12,O9	O7	=IF(M7<=TODAY(),"Pending",IF(P7<>M7,"completed"))
L21:L25,L16:L18	L16	=IF((ISERROR(C16D16))," ",(C16D16))
M16	M16	=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)))))
N16	N16	=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)))))
M17	M17	=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)))))
N17	N17	=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)))))
M18	M18	=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)))))
N18	N18	=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)))))
M21	M21	=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)))))
N21	N21	=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)))))
M22	M22	=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)))))
N22	N22	=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:O25	O22	=IF(M22="","",VLOOKUP(TODAY()-M22,#REF!$E$3:$F$17,2,1))
M23	M23	=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)))))
N23	N23	=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)))))
M24	M24	=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)))))
N24	N24	=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)))))
M25	M25	=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)))))
N25	N25	=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
Cell	Condition		Cell Format	Stop If True
O7:O25	Cell Value	contains "1"	text	NO
O7:O25	Cell Value	contains "2"	text	NO
O7:O25	Cell Value	contains "3"	text	NO
O7:O25	Cell Value	contains "Today"	text	NO
O7:O25	Cell Value	contains "Pending"	text	NO
O7:O25	Cell Value	contains "completed"	text	NO
N7:N25	Cell Value	contains "Jan End"	text	NO
N7:N25	Cell Value	contains "Dec End"	text	NO
N7:N25	Cell Value	contains "Nov End"	text	NO
N7:N25	Cell Value	contains "Oct End"	text	NO
N7:N25	Cell Value	contains "Sept End"	text	NO
N7:N25	Cell Value	contains "Aug End"	text	NO
N7:N25	Cell Value	contains "July End"	text	NO
N7:N25	Cell Value	contains "May End"	text	NO
N7:N25	Cell Value	contains "Mar End"	text	NO
N7:N25	Cell Value	contains "June End"	text	NO
N7:N25	Cell Value	contains "Apr End"	text	NO
N7:N25	Cell Value	contains "Feb End"	text	NO
N7:N25	Cell Value	contains "Jan End,"	text	NO
H7:H25	Cell Value	contains "3"	text	NO
H7:H25	Cell Value	contains "2"	text	NO
H7:H25	Cell Value	contains "1"	text	NO
H7:H25	Cell Value	contains "Good"	text	NO
H7:H25	Cell Value	contains "Completed"	text	NO
H7:H25	Cell Value	contains "Today"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I7:I25	List	=#REF!$H$1:$H$4
B7:B25	List	=#REF!$A$2:$A$8

Fluff · Nov 23, 2020

Is this what you want?

Excel Formula:

=IF(P7<>"","Complete",IF(M7<>"","Pending",""))

jthomas3029 · Nov 23, 2020

Fluff said:
Is this what you want?

Excel Formula:

=IF(P7<>"","Complete",IF(M7<>"","Pending",""))

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.

Fluff · Nov 23, 2020

Glad we could help & thanks for the feedback.

Having trouble getting a date

jthomas3029

New Member

jasonb75

Well-known Member

Excel Facts

jthomas3029

New Member

jasonb75

Well-known Member

jthomas3029

New Member

jthomas3029

New Member

jthomas3029

New Member

jthomas3029

New Member

Fluff

MrExcel MVP, Moderator

jthomas3029

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Having trouble getting a date

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

New Member

New Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock