[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

 

[Gary Thornton]

Thank you, Lutfor Rahman Shimanto for your assistance and kind words. I have attempted to use your updated formula, but I am still getting a #VALUE error. Apologies for not being able to attach my file - I'll try again. Cheers, Gary Thornton.

 

[Gary Thornton]

Thank you, Lutfor Rahman Shimanto for your assistance and kind words. I have attempted to use your updated formula, but I am still getting a #VALUE error. Apologies for not being able to attach my file - I'll try again. Cheers, Gary Thornton.

 

[Lutfor Rahman Shimanto]

Thank you, Lutfor Rahman Shimanto for your assistance and kind words. I have attempted to use your updated formula, but I am still getting a #VALUE error. Apologies for not being able to attach my file - I'll try again. Cheers, Gary Thornton.

Dear Gary Thornton

Thanks for sharing your problem with a dataset. It helped me understand the date data structure you are using.

I am delighted to inform you that I have reviewed your problem and developed a complex formula using IF, OR, MONTH, DATEVALUE, LEFT, MID, and FIND functions.

Follow these steps:

1. Choose an empty cell.
2. Apply the following formula:

   =IF(OR(MONTH(DATEVALUE(LEFT(I1, 3) & " 1"))=1, MONTH(DATEVALUE(MID(I1, FIND(" – ", I1) + 3, 3) & " 1"))=1, MONTH(DATEVALUE(LEFT(I1, 3) & " 1"))=4, MONTH(DATEVALUE(MID(I1, FIND(" – ", I1) + 3, 3) & " 1"))=4, MONTH(DATEVALUE(LEFT(I1, 3) & " 1"))=7, MONTH(DATEVALUE(MID(I1, FIND(" – ", I1) + 3, 3) & " 1"))=7, MONTH(DATEVALUE(LEFT(I1, 3) & " 1"))=10, MONTH(DATEVALUE(MID(I1, FIND(" – ", I1) + 3, 3) & " 1"))=10), 1, 0)

3. Now, drag the Fill Handle icon to the right.
   

I hope you have found the formula you were looking for. I have attached the solution workbook as well. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[Gary Thornton]

Thank you, Lutfor Rahman Shimanto for your assistance. I am now getting the correct response in xlsm format, but not in the ods format (that I use for my ordering program.) A simple matter of saving my file in a different format should fix everything! Thanks again, Gary Thornton.

 

[Lutfor Rahman Shimanto]

Thank you, Lutfor Rahman Shimanto for your assistance. I am now getting the correct response in xlsm format, but not in the ods format (that I use for my ordering program.) A simple matter of saving my file in a different format should fix everything! Thanks again, Gary Thornton.

Dear Gary Thornton

Thanks for your nice words! You are always welcome.

Yes! You are correct. All you need to do is to save your file in the intended format.

We look forward to seeing you again. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy