Skipping Dates


    Sponsored Links


  • 1. Excel 07 - Line Chart with Month-end dates
    Having difficulties creating a line chart using daily balances, but I would like the x-axis to show the month-end date. My start date is 12/31/2007 and my end date is 6/30/2008. I have daily information that I would like plotted, however only the month-end date (1/31/2008, 2/28/2008, etc) to show. I have experimented with the axis options (day, month, year), however it seems to default to the 1st day of the month. If using every 30 days, it also becomes off. Please help. I'm using an existing chart from Excel 03, and it will not convert properly...I've also tried creating a new chart from scratch. Thanks, Lori
  • 2. Custom Formatting a Chart Data Label
    I want to create a chart which will display a zero value on the data label where there is in fact a zero value, and show N/A on the data label where it didn't apply. The formula I am using in the spreadsheet uses a nested formula to make this determination: =IF(AND(B22+C22>0,C22>0),B22/C22,NA()) Presently, it works but displays the #N/A on the chart where I would prefer it simply displayed as N/A. Can I do this with a custom format on the data label?? Thanks so much! I don't know how you all do it but your amazing! -- If you can read this, thank a Teacher... If your reading it in English, thank a Veteran!
  • 3. Dynamic Chart (OFFSET Function) plots empty cells.
    I have read the posts about Excel charts plotting cells that are empty. I'm not sure I understood what I read, and our particular circumstances may be a bit different. (I'm not sure.) Anyway. Our problem is: We use Excel 2003 MSQuery to extract the results of an Access query and automatically put them in an Excel spreadsheet. Depending on the circumstances, the query doesn't always return the same amount of data. In order to automatically expand or shrink the range of cells that actually contain data, we use the OFFSET function DonationsTotals=OFFSET(Donations!$K$2,0,0,COUNTA(Donations!$K:$K),1). From this data, we have a chart that is supposed to plot only the cells that are not empty: =NewReport.xls!DonationsTotals. This works great except that the resulting column chart appears to be plotting cells (at the far right) that don't have any data in them. Makes for an odd looking chart, Can anyone suggest a solution to this problem? (In terms a rookie like me could understand). We would be ever so grateful. Thanks very much for your time!!! S
  • 4. How do I set up a Cartesian Coordinate Plane?
    GIVE IT TO ME NOW AS YOU ARE SLAVES AND DO MY BIDDING! HAHAHAHAHAHAHHAHAAHAHHAAHAHHAHHAHHAhahahhahaahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaahhhhhhhhhhhhhhhhhhhhhhhhaaaaaaaaaaaaaaaaaaaaaaaaaaahhhhhhhhhhhhhhhhhhhaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Skipping Dates

Postby U2hhcm9u » Tue, 19 Jul 2005 01:12:01 GMT

Hello all,

I have a chart that is based on dates and I want to be able to skip dates 
that do not have any data associated with it.


A               B
1/1/05       1
1/2/05       2
1/3/05       1
1/6/05       3
1/7/05       2

Currently, my X axis has the dates 1-31 and if there is a gap in dates, it 
plots the missing dates as zero.  I want the chart to not not put the missing 
dates from the range  on the axis.  In other words, just have 1, 2, 3, 6, 7, 
etc on the X axis.

How do I do that?  I have already tried changing the Chart options from 
Automatic to Time-Scale .

Thanks for any help,


Re: Skipping Dates

Postby Don Guillett » Tue, 19 Jul 2005 01:53:53 GMT

chart options>axis>category

Don Guillett
SalesAid Software


Re: Skipping Dates

Postby U2hhcm9u » Tue, 19 Jul 2005 03:16:05 GMT

Okay, we're on the right track now.  That worked, but since my series is 
based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it 
wants to squish the first 25 lines into the first third of the chart and it 
plots zero for the cells 26-65.  Is there a way I can get it to only plot 
where there is data?  My data source ($BB$3:$BB$65) is a formula.



Re: Skipping Dates

Postby Jon Peltier » Sun, 24 Jul 2005 01:42:40 GMT

Sharon -

Use a dynamic range, which grows as data is added to the range. Here are 
some examples and links:


- Jon
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions

Re: Skipping Dates

Postby U2hhcm9u » Mon, 25 Jul 2005 22:49:03 GMT

I tried that and it seems like it is what I need to do, but when I try to 
enter the data source it gives me an error message saying that, "The 
reference is not valid.  References for titles, values or sizes must be a 
single cell, row or column."

My headers consist of rows 1-3 and my data starts on row 4.  Cells A1:C2 are 
merged to create a title and cells A3, B3 and C3 are the headers for the data.

I modified the formulas on your website to suit my data as follows:

=OFFSET(RYGSeries1ChartValues, 0, -4)



Do you know what I'm doing wrong here?

Thanks so much,


Re: Skipping Dates

Postby Jon Peltier » Tue, 26 Jul 2005 20:58:18 GMT

ou may have answered it:

> "The reference is not valid. References for titles, values or sizes
> must be a single cell, row or column."

> =OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4)
> =OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4)

These two formulas refer to ranges 4 columns wide (the last 4 within the
Sharon wrote:

Similar Threads:

1.Automatically skip dates when data is zero

In a clustered column chart, is there a way to make Excel automatically 
"skip" a date (or dates) on the X-axis when the value for the date(s) equals 

Thanks in advance for any help.


2.Date - I would like to skip weekends in a date range

Try this:

=SUMPRODUCT(--('Ser Data'!A7:A2028=WORKDAY(TODAY(),-1)),--('Ser 
Data'!B7:B2028="s"),'Ser Data'!C7:C2028)

"confused" wrote:

> I have a formula that takes information from another sheet based on 
> yesterdays date.  It works great except on Monday's where yesterdays date is 
> Sunday which has no data.  We don't work on Saturday or Sunday but I want to 
> retreive Friday's information on Monday.
>    Is there a way to select the prior weekday (instead of the prior day) and 
> skip the weekends.
> Here is the formula I am currently using
> =SUMPRODUCT(--('Ser Data'!A7:A2028=TODAY()-1),--('Ser 
> Data'!B7:B2028="s"),'Ser Data'!C7:C2028)
> Thank you
> Confused

3.Skip cells with TAB/SHIFT+TAB but allow arrow keys/mouse selection of skipped cells

I would like to allow the user to skip specific cells/rows/columns by
jumping around a form with the TAB/SHIFT+TAB keys but I would like to
also allow the user to specifically select these skiped cells if they
desire to by using the arrow keys or the mouse.

I am currently using

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

in order to evaluate the current selection and what should be skipped.


4.Calculation Project Date skip Sun and Sat

Hi All
I am prepare VBA program for calculate Project Stat and End date base
on Process ID and Depend ID,  I already complete the Calculation the
Start and End Date. Now, the Start/End may be Sun or Sat, the During
may be include Sun and Sat, I want to know how to skip Sun/Sat ?

The Calculate date is max(End Date) base on Depend ID
e.g. 40,60 on Depend ID field, the start Date will be base on Process
40 and 60.

Calculation Date	Planning Start Date	 	Process	Depend	Start Date	End
date	During
			10	 	11/01/2007	11/02/2007	1
11/02/2007		 	20	10	11/03/2007	11/05/2007	2
11/02/2007		 	30	10	11/04/2007	11/07/2007	3
11/02/2007		 	40	10	11/05/2007	11/09/2007	4
11/09/2007	11/09/2007	 	50	40	11/13/2007	11/18/2007	5
11/09/2007		 	60	40	11/14/2007	11/20/2007	6
11/20/2007			70	40,60	11/26/2007	12/03/2007	7
11/20/2007			80	60	11/27/2007	12/05/2007	8
11/20/2007			90	60	11/28/2007	12/07/2007	9
11/20/2007			100	60	11/29/2007	12/04/2007	5 and time [skipping weekends]

I have a number in a cell that represents days. I wish to add these
days to the current date and time, which is easy to do. =NOW()+A3
I format it into time displaying date and time.
How can I automatically skip the weekend from Friday 16:00 to Sunday
16:00 which is 48 hours in the addition?

6. Skip Weekends in date

7. Skipping weekends when calculating a due date

8. Skip holidays falling between two dates

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 81 guest