Forum Discussion
Month days: 30 vs 31
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.
- Thanks. I haven't work with the N() function but I will see if it can help me. This its a work in progress and if you compare the first month vs the last one - no even close.
