Data to appear when between 2 dates - Round 2

bobgrand

Board Regular
Joined
Apr 14, 2008
Messages
201
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>
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,619
Office Version
365
Platform
MacOS
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 ??
 

bobgrand

Board Regular
Joined
Apr 14, 2008
Messages
201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,164
Messages
5,412,842
Members
403,452
Latest member
mcb123

This Week's Hot Topics

Top