[Solved] Formula to Order Product Automatically Once Every Three Months

[Gary Thornton]

Help! I am trying to create a formula that automatically orders product for me once every three months, beginning in January of each year.

 

[Lutfor Rahman Shimanto]

Help! I am trying to create a formula that automatically orders product for me once every three months, beginning in January of each year.

Hello Gary Thornton

Welcome to ExcelDemy Forum! Thanks for reaching out and sharing your requirements. Assuming you want to start ordering in January and repeat every three months, you can combine the IF, MOD, MONTH, DATE, and YEAR functions.

I have tried to demonstrate your situation, like the following:

​

I am attaching the solution workbook for better understanding; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[Gary Thornton]

Hi, Lutfor Rahman Shimanto, thank you so much for your advice. I'll give it a try. I haven't had the opportunity to sit down to 'play' with it yet, but I assume where your example produces 'Order', I can modify the string to produce '1' instead. I update my spreadsheet quantities every 2 months and copy my other formulas to the new monthly columns as I enter my new quantities on hand. As I said, I haven't worked on my spreadsheet yet, but will your string deliver a 'don't order' response until the 3 monthly criteria is met? Will the string work when I produce my next order schedule early next month ('0')? January, April, July and October are my 'trigger' dates to order this product.

 

[Lutfor Rahman Shimanto]

Hi, Lutfor Rahman Shimanto, thank you so much for your advice. I'll give it a try. I haven't had the opportunity to sit down to 'play' with it yet, but I assume where your example produces 'Order', I can modify the string to produce '1' instead. I update my spreadsheet quantities every 2 months and copy my other formulas to the new monthly columns as I enter my new quantities on hand. As I said, I haven't worked on my spreadsheet yet, but will your string deliver a 'don't order' response until the 3 monthly criteria is met? Will the string work when I produce my next order schedule early next month ('0')? January, April, July and October are my 'trigger' dates to order this product.

Dear Gary Thornton

Thanks for your nice words. Based on your reply, it seems you want to modify the formula. Instead of displaying Order, you want to display 1. You also want to consider January, April, July, and October as the triggering months to reorder the product; this means 1 has to be displayed for these months.

However, I found your attachment challenging to understand. Based on your requirements, I improved the formula and demonstrated the idea in the previous file.

=IF(OR(MONTH(A2)=1, MONTH(A2)=4, MONTH(A2)=7, MONTH(A2)=10), "1", "")

​

I have attached the solution file; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy