Insert a value based on a date range from other cells

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I want to make a payment represented in A3 if the date falls within any two week period

What date are you referring to?
 

octavio

New Member
Joined
Oct 13, 2006
Messages
2
in this particular example it is the 22nd of each month which is is cell b3. I just want to automate the process by having the payment inserted in the right cell based on the date.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,548
Office Version
  1. 365
Platform
  1. Windows
octavio

See if this is headed in the right direction. Note that my dates are in Australian format (dd/mm/yyyy)

Formula in C3 (copied across and down):
Code:
=IF(AND(DAY(C$1)<=$B3,OR(DAY(C$1+14)>$B3,MONTH(C$1)<>MONTH(C$1+14))),$A3,"")

Is it possible that you could have data where the payment day is below 14 (like row 6 in my example)? If, so what results would you expect for row 6?
Mr Excel.xls
ABCDEFGHIJ
1PaymentPayment By This12/01/200726/01/200709/02/200723/02/200709/03/200723/03/200706/04/200720/04/2007
2AmountDay Each Month
310022100 100 100  100
415015150 150 150 150 
520027 200 200 200 200
62503        
7
Payments
 

Forum statistics

Threads
1,141,139
Messages
5,704,503
Members
421,353
Latest member
jekoxien15

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
Top