Calculate Defined Weekly Demand Based on Current Date

**d_striker** · 06-14-2013, 11:01 AM

I have a spreadsheet that I use for forecasting future inventory based on current inventory levels, predefined weekly usage quantities, and shipping quantities. I typically update this sheet weekly and manually adjust the demand based on how many weeks are left in the month.

For example, Columns E, H, and K represent the monthly demand for the respective months. Currently, I take the Average Weekly Usage (AWU) in Column C and multiply by 4 to show the demand for a whole month. As there are only two weeks left in June, I changed the formula in Column E (June Demand) to multiply by 2.

This is only a portion of a fairly complex sheet and I'm trying to train others on taking over this task. There is a lot to remember in the process and if one forgets to adjust the current month's demand, the whole thing goes to heck.

How can I get the sheet to automatically adjust the demand based on some sort of date based equation?

If you can figure this out, you win the internet.

ETA-This is for Excel 2010 on a PC.

**d_striker** · 06-14-2013, 04:04 PM

52 views and no nibbles.

Have I finally presented a challenge that EF can't answer?

**loginjmor** · 06-14-2013, 04:47 PM

Hi -

Try the attached. It Compares the week number of today's date versus the 28th day of whatever month you have written in the header row (Row 1). I used 28 because February only has 28 days, all the rest have at least 28 days. If today's date is more than 4 weeks out from the end of the month, then the formula just multiplies by 4. If it's less than 4 weeks until the end of that month, it uses whatever is left (1,2, or 3 weeks. I suppose you could add something to check if today's date is after the current month, to avoid negative results, but it sounds like you update this frequently anyway.

So, going forward, all you have to do is change the first three letters of the month in the header and Excel will update the demand calculation.

Hope this helps.

**d_striker** · 06-27-2013, 02:13 PM

Originally Posted by loginjmor

Hi -

Try the attached. It Compares the week number of today's date versus the 28th day of whatever month you have written in the header row (Row 1). I used 28 because February only has 28 days, all the rest have at least 28 days. If today's date is more than 4 weeks out from the end of the month, then the formula just multiplies by 4. If it's less than 4 weeks until the end of that month, it uses whatever is left (1,2, or 3 weeks. I suppose you could add something to check if today's date is after the current month, to avoid negative results, but it sounds like you update this frequently anyway.

So, going forward, all you have to do is change the first three letters of the month in the header and Excel will update the demand calculation.

Hope this helps.

Thanks! Sorry about the long delay but I've been out of the office.

Your formula works great except for previous months. The actual spreadsheet that I use has all 12 months on it. Your formula shows a negative number for previous months which yields incorrect data.

I tried adding another and/if statement to it but I can't figure it out. What can I do to treat any negative number as 0?

**d_striker** · 07-02-2013, 02:36 PM

Bumpity bump.

**loginjmor** · 07-02-2013, 03:46 PM

Sorry -

I haven't been back to this site for about a week - Day job gets in the way sometimes.

Try the attached. I modified the formula to check if today's week is after whatever month is in the header cell above. If so, it simply returns a zero. If not, it checks to see how many weeks are left after today and returns the appropriate multiplier (4 or less).

Hope this helps.

**d_striker** · 07-09-2013, 03:24 PM

Originally Posted by loginjmor

Sorry -

I haven't been back to this site for about a week - Day job gets in the way sometimes.

Try the attached. I modified the formula to check if today's week is after whatever month is in the header cell above. If so, it simply returns a zero. If not, it checks to see how many weeks are left after today and returns the appropriate multiplier (4 or less).

Hope this helps.

No apologies necessary. I completely understand.

Thank you VERY MUCH for solving this out. I was going crazy trying to figure it out. Just when I think I'm getting more competent within Excel, people like you school me. This was one of the most complex formulas that I've posted on here and you killed it.

Thanks again!