synchronizing my medication orders

[Stumpped]

I am a little stimied here. Trying to figure out what I would need to do to be able to have all my medications ordered on the same day of the month eventually. I built a spreadsheet and now I am looking for advice as to where my calculations need to go from here. I usually order my medications every month, but since they have changed my meds over the year, none of my medications are ordered on the same day and are not even anymore. I can order my medications as soon as every 25 days and up to 30 or more days, depending on the amount of supplied medication I have on hand.
Here is the spreadsheet I have right now. The columns are the pills I have in my pill box, the pills I have in bottles, and the total pills. Then there are the number of pills per week I take for each medication and the number of pills per day I take. One pill I only take 1 pill 3 times a week, so the pills per day looks a little screwy. The last column is the number of days I have before I run out. Next, is the days left before running out, and the last column is the days between orders. So I can order every 25 to 30 days.

							days left before	days between
							running out	orders
MEDICATION NAME:	Pills in pill box	Pills in bottle	Total Pills	pills per week	pills per day	how many days until I run out	to order
medication 1	6	69	75	14	2	37.5	7 to 12 days	25 to 30
medication 2	12	247	259	14	2	129.5	7 to 12 days	25 to 30
medication 3	6	35	41	7	1	41	7 to 12 days	25 to 30
medication 4	3	27	30	3	0.428571429	70	7 to 12 days	25 to 30
medication 5	6	48	54	7	1	54	7 to 12 days	25 to 30
medication 6	5	30	35	7	1	35	7 to 12 days	25 to 30
medication 7	6	122	128	14	2	64	7 to 12 days	25 to 30
medication 8	6	23	29	7	1	29	7 to 12 days	25 to 30
medication 9	6	37	43	7	1	43	7 to 12 days	25 to 30
medication 10	12	35	47	14	2	23.5	7 to 12 days	25 to 30

 

[awoohaw]

first since you only want to reorder once a month, I don't think your weekly usages matter.
second, you don't say how many pills are in each prescription. But here is my attempt at it.
It highlights the months that you will run out if you don't reorder. The value in the month is assuming you will reorder the first of that month:

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1						2023-01-01	2023-02-01	2023-03-01	2023-04-01	2023-05-01	2023-06-01	2023-07-01	2023-08-01
2		presciption size	buffer	dosage daily size	start	31	28	31	30	31	30	31	31
3	DrugA	50	7	1	15	58	51	44	37	80	73	66	59
4	DrugB	30	3	0.3333333	20	17	14	11	38	35	32	29	26
5	DrugC	400	14	2	100	86	72	458	444	430	416	402	388
6	DrugD	500	28	4	250	222	194	166	138	610	582	554	526
7
Stumped

Cell Formulas
Range	Formula
F1	F1	=DATE(2023,1,1)
G1:M1	G1	=EOMONTH(F1,0)+1
F3:M6	F3	=(E3-$C3)+ IF((E3-$C3)<$D3*IF(AND(MOD(YEAR(F$1),4)=0,MONTH(YEAR(F$1)=2)),29,F$2),$B3,0)
D4	D4	=1/3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:M6	Expression	=F3>=E3	text	NO

 

[Stumpped]

I appreciate your attempt at solving this issue. You are correct, I did not provide you with enough information to be able to solve it, but I was also unaware that I should have given that information in the first place. Like you said I didn't think about how many pills I received in each prescription bottle. I was able to solve this by a brute-force method. I took the list of medicines and added 30 days which is a standard prescription date refill time. Then I either kept the refill dates at every 30 days and then subtracted up to 5 from each prescription until I ended up in August with all my medications being ordered on August 30th.

 

[awoohaw]

I'm glad you figured that out.

The solution i offered does what you just wrote. It works for a prescription with what ever number of pills is appropriate for the monthly dosages. The formula also takes into consideration that some months have more, less, or equal to 30 days, as well as using a buffer of 7 days, I guess you chose 5, which is your choice.