J
Jose Lopes
I’ve managed to create a calendar based on a stacked chart where I show my
delivery dates. I’ve also managed to calculate and chart the previous and
following Saturdays and Sundays from Today() and assigned them different
colors. Everything works dynamically but there is a “butâ€. The weekend day’s
series are inputted by hand and if one of my dates goes beyond the last
series defined, my chart loses its purpose.
My first Saturday is calculated before Today() using the following formula:
=Today() – week.day(Today();2)-1
To calculate the weekend days I just add 0 gap units for Sundays and 5 gap
units for the following Saturdays. This way, and considering
Today=27-03-2006, I’ve defined my current weekend series according to the
following table:
| sat 1 | dur | sun 1 | dur | sat
2 | dur …
---------------|-------------------|--------|---------|--------|---------|-------- …
weekends | 25-03-2006 | 1 | 0 | 1 | 5 |
1 …
This numbers stack up nicely in the calendar chart and everything it’s in
place… except the number of weekend series plotted: it’s not dynamic. I know
I could set this range of cells with the N/D error spread across some columns
and detect the max number of the booking dates, but this procedure does not
bode well to me. Maybe there is a better way to do this, or even the whole
thing. Any ideas will be much appreciated.
Thx in advance.
delivery dates. I’ve also managed to calculate and chart the previous and
following Saturdays and Sundays from Today() and assigned them different
colors. Everything works dynamically but there is a “butâ€. The weekend day’s
series are inputted by hand and if one of my dates goes beyond the last
series defined, my chart loses its purpose.
My first Saturday is calculated before Today() using the following formula:
=Today() – week.day(Today();2)-1
To calculate the weekend days I just add 0 gap units for Sundays and 5 gap
units for the following Saturdays. This way, and considering
Today=27-03-2006, I’ve defined my current weekend series according to the
following table:
| sat 1 | dur | sun 1 | dur | sat
2 | dur …
---------------|-------------------|--------|---------|--------|---------|-------- …
weekends | 25-03-2006 | 1 | 0 | 1 | 5 |
1 …
This numbers stack up nicely in the calendar chart and everything it’s in
place… except the number of weekend series plotted: it’s not dynamic. I know
I could set this range of cells with the N/D error spread across some columns
and detect the max number of the booking dates, but this procedure does not
bode well to me. Maybe there is a better way to do this, or even the whole
thing. Any ideas will be much appreciated.
Thx in advance.