Taking Action Based on Day of The Week

[HyCohen]

Hello,

Let's say I am selling a weekly newspaper @ $1 each (retail), and I purchase 10 newspapers at the wholesale price of $0.25 each. During the 7 day week — Wednesday through Tuesday — I sold 8 newspapers, leaving me with 2 leftover papers from that week. So, when the new newspaper comes out on Wednesday I will purchase a new batch of papers to sell. However, I also want to sell the remaining 2 newspapers from the previous week at a discount.

Another example, but as a spreadsheet:

Date	Current Week's Papers Purchased	No. of Current Week's Papers Sold	No. of Previous Week's Papers Sold	Total Current Week's Papers In Stock	Total Previous Week's Papers In Stock	Notes
10/2	10	1	1	9	4	To start, I had 5 prev. week's papers left over.
10/3		2	0	7	4
10/4		1	2	6	2
10/5		4	0	2	2
10/6	4	1	0	5	2	Since I was down to 2 cur. week's paper's left I got 4 more = 6, but I sold 1 = 4 in stock
10/7	0	2	1	3	1
10/8	0	0	0	3	1
10/9	10	1	0	9	3	New week: purchased 10 current week's papers & sold 1 = 9 left in stock. Moved the 3 papers remaining in the current week's papers column into the prev. week's newspaper column, and lost the 1 paper that was in the prev. week's paper column.

<tbody>
</tbody>

How can I get Excel to move the papers that are in the current week's papers column into the past week's papers column where they will be tracked and sold at a reduced rate?

Thanks for your help!

Warmly,
Hy with Guide Dog, "Fleetwood"

 

[Redwolfx]

Just Checking your "Weeks" Go Wednesday to Tuesday correct?

If so...

After Entering initial data in Row 2, Assuming the Date is in Column A, Current Week in Column E and Previous week in F.

in Cell E3 =IF(TEXT(A3,"ddd")="Wed",B3-C3,E2+B3-C3)
in Cell F3 =IF(TEXT(A3,"ddd")="Wed",E2-D3,F2-D3)