Need to only display dates from a single month in a table

iclancy

New Member
Joined
Mar 12, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi, I am trying to fix an issue with my table that causes dates from outside of the current month to display in the table. This is a sheet that I must protect before forwarding it to prevent accidental modification by the user, so they may only modify the orange cells. The screenshot below shows the field where the user enters the current month, after which the table updates according to the month entered.

1615575834478.png


As seen in the table below, if a month with less than 31 days is entered, the table includes the day from the following month as the 31st day:

1615575810720.png


Consequently, the date will also display in the following chart, which displays an unwanted "0" entry at the end of the chart, which messes up the trendline when full set of data are entered:

1615575939049.png


My question is: Is there either a way to set up a rule that omits either the date from the following month, or gets rid of the "0" entry in either the table or the chart?
 

Attachments

  • 1615575727614.png
    1615575727614.png
    6.5 KB · Views: 3

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,288
Welcome to the board.

I am sure the problem can be fixed.
However you didn't say anything about how your source data are arranged and which macro or formulas are used create the output table.
For some "more detailed" suggestions you should give us details on how the data are organized, provide some realistic data that we can work on, and specify which output you are willing to generate; if you wish we work with your approach, then give us the details on how you approached the problem, or we will be free to suggest any approach.

Bye
 

iclancy

New Member
Joined
Mar 12, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your reply. My spreadsheet uses the following formulas:

1615583245273.png

"Service Days this Month" (B11) derives the number of days in the month entered in B10:

=DAY(DATE(YEAR(B10),MONTH(B10)+1,1)-1)

1615583405971.png


The dates shown in Column B start with cell B29 referring to cell B10, followed by B29+1, B30+1, and so on.

The values in Column C corresponding to the dates are the sum of ridership data entered in and pulled from two separate sheets.
=SUM('BKO to KFS'!Z2:Z3,'KFS to BKO'!Z2:Z3)

The Ridership Trend chart simply pulls data directly from columns B and C of this table.

I hope this helps.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,288
In B30 use the following formula, instead of =B29+1:
Excel Formula:
=IF(MONTH(B29+1)=MONTH($B$10),B29+1,NA())
Then copy down up to B59.
This will put #N/A in the cells that don't belong to the starting month; the same error will result in column C, if the formula in those cells has column B as a predecessor

The cells with #N/D will be ignored in the graph

Try...
 

iclancy

New Member
Joined
Mar 12, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
In B30 use the following formula, instead of =B29+1:
Excel Formula:
=IF(MONTH(B29+1)=MONTH($B$10),B29+1,NA())
Then copy down up to B59.
This will put #N/A in the cells that don't belong to the starting month; the same error will result in column C, if the formula in those cells has column B as a predecessor

The cells with #N/D will be ignored in the graph

Try...
Thanks so much! That works fine. I knew it should be an IF statement, just wasn't sure how to organize it.

Appreciate your help
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,480
Messages
5,770,341
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top