Forum Discussion
Month days: 30 vs 31
Good day to you all.
I have a workbook that I use for keeping record of inventory and scheduling purchases. I enter information daily and use that information as the data for several charts, graphs and estimates. Every work sheet it’s a month. I began this workbook over a year ago and every month I keep adding or changing to improve the workbook. Because of this I usually use my previous month as a template for the next month.
My question its more over 30 vs 31 days months. Every month I have to keep changing all the ranges for many of the formulas to adjust to 30 or 31 days. I tried a formula using IF and EOMONTH but didn’t work. Do you know of a different way? Thanks
EDIT: Im trying to explain my chart.
A7:X37 - I enter information only on areas dark yellow. Every day I enter inventory at Opening and total sales of the previous date. Today its marked dark pink. The future sales its based on the previous month averages sales (by day). I also use this info for scheduling future inventory purchases.
The main area where I have to change my formulas its at the end of the workbook. For example the table CY22:DB31. That table works estimate by day. But if the month have 30 days (or 28) I have to adjust so the estimate its correct.
4 Replies
LLeon-GV.... Since you provide no formulas or, better, attach an example Excel file, we have no idea what you mean by ``changing all the ranges for many of the formulas to adjust to 30 or 31 days``.
And BTW, the days of the month can be 28 to 31, not merely 30 or 31 days.
At the risk of misdirection while trying to be helpful, I wonder if the DAYS360 function accomplishes what you need.
I added this month workbook. Its in Spanish. But I enter information only on areas with dark yellow. All the other areas are formulas. To the right of the yellow area- It helps me to see the whole month. Down (the yellow) helps me to see the day sales.
LLeon-GV... Thanks for the Excel file attachment. Big help!
I am not sure how much help I can offer. It is a complicated design, and there are many interacting parts that I cannot find or understand without a deep-dive.
I would start with the following change in A35 and copy into A36:A37:
=IF(A34="", "", IF(DAY(A34)+1>DAY(EOMONTH(A34,0)), "", A34+1))
That results in the null string when day of A35:A37 would otherwise exceed the last day of the month.
To test, in A7, enter the dates 2/1/2021 (Feb), 2/1/2000 (leap year), and 11/1/2021 (Nov) as well as 10/1/2021 (Oct), which you have.
With the shorter months, study the rest of the worksheet for undersirable results.
You might have to change some formulas to something of the form:
=IF(A35="", "", original formula)
For example (perhaps):
K35: =IF(A35="","",SUM(G35:I35))
L35: =IF(A35="","",IF(SUM(M35:O35)>SUMIF($K$53:$K$59,$CG35,$O$53:$O$59),"","↓"))
And with those changes, some arithmetic expressions might return #VALUE because of the text (null string). In those cases, the N() function might be helpful.
I have not yet found an example in your worksheet. But what I mean is: change =X1+Y1 to =X1+N(Y1) if Y1 might be the null string.
Finally, the chart limits might become a problem. For example, with 2/1/2021 (Feb) in A7, I can see that the right end of the charts drop to zero.
I cannot help you with the charts. First, I do not know how to handle that even in my version of Excel (2010). Second, newer versions of Excel might offer new options to deal with this difficult problem.
Be sure to tell us what version (the earliest version) of Excel that you need the workbook to work with.
BTW, I believe that HansV is very good with charts. Let's hope he sees this thread.
That is about all I can say to help you. If you have specific questions, ask them. I or someone else should be able to answer them, now that we have an Excel file to look at.
