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

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:
Choose an empty cell, insert the given formula and drag the Fill Handle icon to copy the formu...png

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

Regards
Lutfor Rahman Shimanto
ExcelDemy
 

Attachments

  • Gary Thornton (SOLVED).xlsx
    9.7 KB · Views: 4
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.
 

Attachments

  • Excel example.xlsm
    6.1 KB · Views: 1
Last edited by a moderator:
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", "")
1713350432772.png

I have attached the solution file; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy
 

Attachments

  • Gary Thornton (SOLVED).xlsx
    9.7 KB · Views: 3
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.
 

Attachments

  • For Attachment.xlsm
    6.3 KB · Views: 2
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.
 
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.
    Formula To Order Product Automatically Once Every Three Months.png
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
 

Attachments

  • Gary Thornton (SOLVED).xlsm
    11.4 KB · Views: 1

Online statistics

Members online
0
Guests online
10
Total visitors
10

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top