Displaying number based on date conditions column and True/False column.

M

mike4777

Board Regular
Joined
Apr 12, 2011
Messages
124
Test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
2Pay SchedulePay Breakdown
3Triple Pay MonthsPhone StipendBi-WeeklyMonthlyI get a phone stipend once per month on an alternating two week
411/5/2024 Hours80160basis. I need that to show up on the Bi-weekly cell (I7) only during
521/19/2024 TRUEHourly Pay$1.00$1.00the two weeks after the date I receive it. Example: B5 has date
632/2/2024 Bonus1/19/24 which has a True statement in the D column next to it.
742/16/2024 TRUEStipends$50.00I need 50 dollars to show in I7 starting 1/19 and automatically
853/1/2024TRUETotal$80.00$210.00removing itself when the next pay period starts on 3/1/24.
963/15/2024TRUETRUEI started a formula already, but I'm not quite sure how to logic it out.
1073/29/2024TRUEPre-tax Deductions-$244.47-$114.47
1184/12/2024 TRUETotal After Taxes-$193.24-$90.48Formula for Phone Stipend: IF(SUMPRODUCT(--(MONTH($B$68:$B$147)=MONTH(M71)),--(YEAR($B$68:$B$147)=YEAR(M71)), --($D$68:$D$147=TRUE))>0, 50, 0)
1294/26/2024 Post-tax Deductions-$213.91-$131.82
13105/10/2024 TRUETotal Deductions-$345.14-$690.28
14115/24/2024 Total Taxes$51.23$23.99I need the same thing done for the C column when I get
15126/7/2024 TRUETithe & Offerings$19.32$13.183 checks in a month. This formula will go in J4.
16136/21/2024 Tithe-$19.32-$13.18On the months I get 3 checks, I need the formula to
17147/5/2024 TRUEOfferingsadd 40 more hours to the cell than normal.
18157/19/2024 Taxes$51.23$23.99
19168/2/2024TRUETRUEFederal Tax-$28.96-$13.560
20178/16/2024TRUESocial Security-$15.54-$7.28
21188/30/2024TRUETRUEMedicare-$3.64-$1.70Tithe Deduction:-$51.234/5/2024
22199/13/2024 Long Term Care Employee-$1.47-$0.69Tithe Deduction:-$23.99
23209/27/2024 TRUEFamily Leave Insurance-$1.35-$0.63
242110/11/2024 WA WC Tax-$0.26-$0.12
252210/25/2024 TRUEPre-tax Deductions-$324.47-$648.94
262311/8/2024 401k$196.03$392.06
272411/22/2024 TRUEHSA$57.69$115.38Tithe Deduction:$324.47
282512/6/2024 Parking$30.00$60.00Tithe Deduction:$648.94
292612/20/2024 TRUEMedical$25.75$51.50
30271/3/2025TRUEDental$15.00$30.00
31281/17/2025TRUETRUEPost-tax Deductions-$20.67-$41.34
32291/31/2025TRUEIdentity Theft Insurance$6.69$13.38
33302/14/2025 TRUESupplimental Life EE$4.55$9.10
34312/28/2025 Supplimental Life Insurance (Michael)$2.49$4.98
35323/14/2025 TRUEAccident Insurance ATax$4.26$8.52Tithe Deduction:$20.67
36333/28/2025 Supplimental ADD ATax (Dawn)$1.33$2.66Tithe Deduction:$41.34
37344/11/2025 TRUESupplimental ADD ATax (Michael)$1.18$2.36
38354/25/2025 Supplimental Life EE Adj$0.13$0.26
39365/9/2025 TRUESupplimental ADD ATax Adj$0.04$0.08
40375/23/2025 Non-Cash Deductions-$13.84-$27.68
41386/6/2025 TRUEGTL Over 50k$13.38$26.76
42396/20/2025 GTL Over 50k Adj$0.46$0.92
43407/4/2025 TRUENon-Cash Additions$13.84$27.68
44417/18/2025 GTL Over 50k$13.38$26.76
45428/1/2025TRUETRUEGTL Over 50k Adj$0.46$0.92
46438/15/2025TRUE
47448/29/2025TRUETRUE
48459/12/2025 
49469/26/2025 TRUE
504710/10/2025 
514810/24/2025 TRUE
524911/7/2025 
535011/21/2025 TRUE
545112/5/2025 
555212/19/2025 TRUE
56531/2/2026TRUE
57541/16/2026TRUETRUE
58551/30/2026TRUE
59562/13/2026 TRUE
60572/27/2026 
61583/13/2026 TRUE
62593/27/2026 
63604/10/2026 TRUE
64614/24/2026 
65625/8/2026 TRUE
66635/22/2026 
67646/5/2026 TRUE
68656/19/2026 
69667/3/2026TRUETRUE
70677/17/2026TRUE
71687/31/2026TRUETRUE
72698/14/2026 
73708/28/2026 TRUE
74719/11/2026 
75729/25/2026 TRUE
767310/9/2026 
777410/23/2026 TRUE
787511/6/2026 
797611/20/2026 TRUE
807712/4/2026 
817812/18/2026 TRUE
82791/1/2027 
83801/15/2027 TRUE
84
Sheet1
Cell Formulas
RangeFormula
C4:C83C4=IF(SUMPRODUCT(--(MONTH($B$4:$B$200)=MONTH(B4)), --(YEAR($B$4:$B$200)=YEAR(B4)))=3, TRUE,"")
J4J4=160+IF(SUMPRODUCT(--(MONTH($B$4:$B$200)=MONTH(N21)),--(YEAR($B$4:$B$200)=YEAR(N21)), --($C$4:$C$200=TRUE))=3, 40, 0)
B5:B83B5=DATE(YEAR(B4),MONTH(B4),DAY(B4)+14)
I8I8=(I4*I5)+I6+I7
J8J8=(J4*J5)+J7+J6
I10I10=I8+K25
J10J10=J8+K25
I11:J11I11=I10+K18
I12:J12I12=I11+K31
I13:J13I13=SUM(K25,K31)
I14:J14I14=K18
K15:L15,K40:L40K15=-SUM(I16:I17)
I16I16=I11*0.1
J16J16=J12*0.1
K18:L18K18=-SUM(I19:I24)
I19:J19I19=11.8469375855641%*I10
I20:J20I20=6.35837780933653%*I10
I21:J21I21=1.48707733153566%*I10
I22:J22I22=0.602722394659365%*I10
I23:J23I23=0.551763358376212%*I10
I24:J24I24=0.107668935595126%*I10
N19N19=IF(SUMPRODUCT(--(MONTH($B$4:$B$200)=MONTH(N21)),--(YEAR($B$4:$B$200)=YEAR(N21)), --($C$4:$C$200=TRUE))=3, 40, 0)
L21L21=SUM(I19:I24)
L22L22=SUM(J19:J24)
K25:L25K25=-SUM(I26:I30)
L27L27=SUM(I26:I30)
L28L28=SUM(J26:J30)
J26:J30,J44:J45,J41:J42,J32:J39J26=I26*2
K31:L31K31=-SUM(I32:I39)
L35L35=SUM(I32:I39)
L36L36=SUM(J32:J39)
K43:L43K43=SUM(I44:I45)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Pls check this

PS: date is in dd-mm-yyyy format
Content of cell N21 has been put in N2, for reducing the size

Added a helper column B, which has the month
I feel, it would reduce the complexity of formulae

Book1.xlsm
ABCDEFGHIJKLMN
1DateMonthTriple Pay MonthsPhone StipendBi WeeklyMonthly
205-01-2024Jan-2400Hours80160Jan-240Date05-04-2024
319-01-2024Jan-2401Hourly pay11Feb-240
402-02-2024Feb-2400BonusMar-241
516-02-2024Feb-2401Stipends050Apr-240
601-03-2024Mar-2410Total80210May-240
715-03-2024Mar-2411Jun-240
829-03-2024Mar-2410Jul-240
912-04-2024Apr-2401Aug-240
1026-04-2024Apr-2400Sep-240
1110-05-2024May-2401Oct-240
1224-05-2024May-2400Nov-240
1307-06-2024Jun-2401Dec-240
1421-06-2024Jun-2400
1505-07-2024Jul-2401
1619-07-2024Jul-2400
1702-08-2024Aug-2401
1816-08-2024Aug-2400
ShGE03
Cell Formulas
RangeFormula
B2:B18B2=DATE(YEAR(A2),MONTH(A2),1)
C2:C18C2=IF(COUNTIF(B:B,B2)>=3,1,0)
H2H2=160+40*VLOOKUP(DATE(YEAR(N2),MONTH(N2),1),$J$2:$K$13,2,0)
K2:K13K2=IFERROR(VLOOKUP(J2,B:C,2,0),0)
A3:A18A3=A2+14
D3:D18D3=1-D2
J3:J13J3=EDATE(J2,1)
G5G5=50*VLOOKUP(VLOOKUP($N$2,A:A,1,1),A:D,4,0)
G6:H6G6=G2*G3+G4+G5
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
142
ellison
E
ExcelNewbie2020
  • Solved
Allocation of Amount, based on the Monthly amount and dates
Replies
4
Views
423
ExcelNewbie2020
ExcelNewbie2020
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
237
Thlama
T
W
  • Question Question
Adding another condition to IF(AND) formula
Replies
6
Views
267
etaf
etaf
Z
  • Solved
Conditional Format Date Range
Replies
4
Views
907
zero269
Z

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

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