Data to appear when between 2 dates - Round 2

bobgrand

Board Regular
Joined
Apr 14, 2008
Messages
244
Office Version
  1. 365
Platform
  1. Windows
OK, Let's try this again.
I hope this makes more sense and look forward to see some responses if this is at all possible. I currently do all of this manually for a project that will be going on over the next 18 months.
On the Due Day in column B, I would like to see the quantities in column A show up on or before the Order Date but not on the date prior to the upcoming Order Date in row 1.
Using the Today's Date formula in A1, lets say today is May 7, I would like to see the quantities in column A show up in cells E7, E8 & E9. Quantities should only show up when the due day is on or before the order date but after the date prior to the current order date. I would enter the order on 05/08/19 and order the quantities that are due by day 9, 11 and 13.
All the quantities in columns F thru J would be blank because those dates have not arrived yet. The ordering cycle is always Wed, Wed, Fri, Wed Wed, Fri, etc.

I really hope I explained this well enough to understand and I hope I have put your excel skills to the challenge.


ABCDEFGHIJ
105/13/19Order date>wed 05/01/19fri 05/03/19wed 05/08/19wed 05/15/19fri 05/17/19wed 05/22/19wed 05/29/19fri 05/31/19
2qtyDue Day
32001200
450350
52005200
620720
710910
8251125
9601360
10501550
1120017200
12501950
1320021200
14202320
15102510
16252725
17602960
18503131

<tbody>
</tbody>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
sorry, i dont fully understand
How does excel know the due date ...

A1 = todays date - so that will keep changing

B3 = Due Date - and has a 1 in the cell, which I assume means 1 day after some date - but it cant be today() cell, as every day the spreadsheet date will change in A1 and so those orders will change

As your example
A1 = 15th May
C3 has 200 , under 1st may column

OR
is B3 showing the day of the month - so 1 = 1st of month based on the month from A1

so if you open the spreadsheet on 1st June - what happens ??
 
Upvote 0
sorry, i dont fully understand
How does excel know the due date ... <<< This is where I need help with the formula

A1 = todays date - so that will keep changing
This is correct

B3 = Due Date - and has a 1 in the cell, which I assume means 1 day after some date - but it cant be today() cell, as every day the spreadsheet date will change in A1 and so those orders will change
Column B - DUE DAY = The day of each month. This is where I can't figure out a formula. If the day in the order date is on or before the DUE DAY in column B but greater than the previous Order Date.

As your example
A1 = 15th May
C3 has 200 , under 1st may column

If A1 = 1st May populate the value in A3 to C3
If A1 = 7th May than populate the values from Column A, 10, 25, 60 to cells E7, E8, E9
If A1 = 15th May than populate the value from A10 to F10
If A1 = 16th May than populate the values from A11, A12, A13 to cells G11, G12, G13


OR
is B3 showing the day of the month - so 1 = 1st of month based on the month from A1
Correct B3 thru B18 are days of the month. These are manual typed values and not in any way at the moment using A1

so if you open the spreadsheet on 1st June - what happens ??
I have no formulas to make this happen yet as I am having trouble with with using the due day with the order date formula to make this happen.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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