I have a blood pressure tracker on the templates page (Microsoft's)
that can handle any year.

I just realized, however, that I made no proviso for leap years. So I
am going to add a 29th day in the February moth sheet of my workbook.

What I want to do is a formula along the lines of "if exist" that 29th
day of February in the chosen year, when true the date would be returned
to the cell location, and the plotted chart would respond accordingly.

If the day does not exist, the cell remains blank, and the plot figures
on the 28 day month.


Additional question:

Can the data set a plot is derived from be called out in a cell such
that the chart varies according to the cell contents, choosing a named
data set dependent on the result. So I could mark the sheet at the 28th
day row , and give it a name (28DayMonth), and I could mark a 29 day data
set, and name it LeapYear or LeapData, forcing the chart to use the added
row of data, yet keeping it out when the 29th day doesn't exist.

Have you thought of using real dates?
In A1 enter 1/1/2009
Format to show the date however you like it (e.g 1-Jan-2009)
In A2 enter =A1+1 (or A1+7 to jump by weeks)
Copy down the column
If there is a 29 Feb, Excel will look after it
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"FatBytestard" wrote in message
...


I have a blood pressure tracker on the templates page (Microsoft's)
that can handle any year.

I just realized, however, that I made no proviso for leap years. So I
am going to add a 29th day in the February moth sheet of my workbook.

What I want to do is a formula along the lines of "if exist" that 29th
day of February in the chosen year, when true the date would be returned
to the cell location, and the plotted chart would respond accordingly.

If the day does not exist, the cell remains blank, and the plot figures
on the 28 day month.


Additional question:

Can the data set a plot is derived from be called out in a cell such
that the chart varies according to the cell contents, choosing a named
data set dependent on the result. So I could mark the sheet at the 28th
day row , and give it a name (28DayMonth), and I could mark a 29 day data
set, and name it LeapYear or LeapData, forcing the chart to use the added
row of data, yet keeping it out when the 29th day doesn't exist.

On Tue, 19 May 2009 23:12:55 -0300, "Bernard Liengme"
wrote:

Have you thought of using real dates?
In A1 enter 1/1/2009
Format to show the date however you like it (e.g 1-Jan-2009)
In A2 enter =A1+1 (or A1+7 to jump by weeks)
Copy down the column
If there is a 29 Feb, Excel will look after it
best wishes

Did you even read my post?

I have 12 sheets, one for each month. In each of the twelve, the date,
and day of week is given so that it shows up in the chart. That chart is
based on a range of cells. There are twelve charts and one annual chart.

So I need two things. A formula to test whether 2/29/GivenYear is a
valid date, and to change the data range that the chart refers to if it
is a valid date. If the range the chart utilizes can be referred to in a
cell, I will have no problem defining the two data ranges. If not, I'll
have to make a leap year calendar sheet and chart for leap years only.

and will still need to perform some kind of test to determine which way
to switch the February sheet.

Right now, =DATE(GivenYear,2,29) shows up in a cell as March 1st for
this year, for example ("GivenYear" is a cell reference containing a
year).

Try this formula as i've used it and NA() is the best approach to be
ignored in chart .... you can hide #NA error with conditional formatting
or cell error as blank ..........

sample formula = IF(Conditon<YEAR IS LEAP,"LEAP","NOT LEAP")

=IF(MOD(YEAR(B2),4)=0,AC2+1,NA())

I'm considering that first date is in B2 and o last cell is AD2 having
above formula ....
Here your current year will be zero if that is leap year and first
formula will work else 2nd option ..... Try this and do let me know


--
mubashir aziz

If this post helps Don't 4get to click Yes
------------------------------------------------------------------------
mubashir aziz's Profile: http://www.thecodecage.com/forumz/member.php?userid=237
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98265

Doesn't that make 2100 a leap year?
--
David Biddulph

mubashir aziz wrote:
Try this formula as i've used it and NA() is the best approach to be
ignored in chart .... you can hide #NA error with conditional
formatting or cell error as blank ..........

sample formula = IF(Conditon<YEAR IS LEAP,"LEAP","NOT LEAP")

=IF(MOD(YEAR(B2),4)=0,AC2+1,NA())

I'm considering that first date is in B2 and o last cell is AD2 having
above formula ....
Here your current year will be zero if that is leap year and first
formula will work else 2nd option ..... Try this and do let me know

Try to write 1-feb-2100 and drag it down. Is it giving you 29-Feb-2009
????? That is widnow prolbem like we had before start of 2000 ....


David Biddulph;351289 Wrote:
Doesn't that make 2100 a leap year?
--
David Biddulph

mubashir aziz wrote:
Try this formula as i've used it and NA() is the best approach to be
ignored in chart .... you can hide #NA error with conditional
formatting or cell error as blank ..........

sample formula = IF(Conditon<YEAR IS LEAP,"LEAP","NOT LEAP")

=IF(MOD(YEAR(B2),4)=0,AC2+1,NA())

I'm considering that first date is in B2 and o last cell is AD2
having
above formula ....
Here your current year will be zero if that is leap year and first
formula will work else 2nd option ..... Try this and do let me know


--
mubashir aziz

If this post helps Don't 4get to click Yes
------------------------------------------------------------------------
mubashir aziz's Profile: http://www.thecodecage.com/forumz/member.php?userid=237
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98265

Mine goes correctly from 28 Feb 2100 to 1 Mar 2100, so it looks like you've
got a separate problem.
--
David Biddulph

"mubashir aziz" wrote in message
...

Try to write 1-feb-2100 and drag it down. Is it giving you 29-Feb-2009
????? That is widnow prolbem like we had before start of 2000 ....


David Biddulph;351289 Wrote:
Doesn't that make 2100 a leap year?
--
David Biddulph

mubashir aziz wrote:
Try this formula as i've used it and NA() is the best approach to be
ignored in chart .... you can hide #NA error with conditional
formatting or cell error as blank ..........

sample formula = IF(Conditon<YEAR IS LEAP,"LEAP","NOT LEAP")

=IF(MOD(YEAR(B2),4)=0,AC2+1,NA())

I'm considering that first date is in B2 and o last cell is AD2
having
above formula ....
Here your current year will be zero if that is leap year and first
formula will work else 2nd option ..... Try this and do let me know


--
mubashir aziz

If this post helps Don't 4get to click Yes
------------------------------------------------------------------------
mubashir aziz's Profile:
http://www.thecodecage.com/forumz/member.php?userid=237
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98265

I think you didn't read my answer properly i asked that did you get 29th
Feb 2100 as 2100 is a leap year. If not then there is nothing wrong
with formula and it will help to make chart etc. ....


David Biddulph;351405 Wrote: [color=blue]
Mine goes correctly from 28 Feb 2100 to 1 Mar 2100, so it looks like
you've
got a separate problem.
--
David Biddulph

"mubashir aziz" wrote in
message
...

Try to write 1-feb-2100 and drag it down. Is it giving you
29-Feb-2009[color=green]
????? That is widnow prolbem like we had before start of 2000 ....


David Biddulph;351289 Wrote:
Doesn't that make 2100 a leap year?
--
David Biddulph

mubashir aziz wrote:
Try this formula as i've used it and NA() is the best approach to
be
ignored in chart .... you can hide #NA error with conditional
formatting or cell error as blank ..........

sample formula = IF(Conditon<YEAR IS LEAP,"LEAP","NOT LEAP")

=IF(MOD(YEAR(B2),4)=0,AC2+1,NA())
mubashir aziz

If this post helps Don't 4get to click Yes

------------------------------------------------------------------------
mubashir aziz's Profile:
'The Code Cage Forums - View Profile: mubashir aziz'
(http://www.thecodecage.com/forumz/member.php?userid=237)
View this thread: 'I need a february 29th row, but only if the day of
year actually exists - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=98265)



--
mubashir aziz

If this post helps Don't 4get to click Yes
------------------------------------------------------------------------
mubashir aziz's Profile: http://www.thecodecage.com/forumz/member.php?userid=237
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98265

FatBytestard,
For the February Worksheet.
A1: Date Header
A2=2/1/09 (year will increase with new workbook)
A3: =A2+1
Drag cell contacts to A28.
In Cell A29 place the following formula:
=IF(DAY(A2+28)<29,"",A2+28)

For charting just include Row#30 in your range.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule
<<Previous Text Snipped to Save Bandwidth When Appropriate


"FatBytestard" wrote in message
...


I have a blood pressure tracker on the templates page (Microsoft's)
that can handle any year.

I just realized, however, that I made no proviso for leap years. So I
am going to add a 29th day in the February moth sheet of my workbook.

What I want to do is a formula along the lines of "if exist" that 29th
day of February in the chosen year, when true the date would be returned
to the cell location, and the plotted chart would respond accordingly.

If the day does not exist, the cell remains blank, and the plot figures
on the 28 day month.


Additional question:

Can the data set a plot is derived from be called out in a cell such
that the chart varies according to the cell contents, choosing a named
data set dependent on the result. So I could mark the sheet at the 28th
day row , and give it a name (28DayMonth), and I could mark a 29 day data
set, and name it LeapYear or LeapData, forcing the chart to use the added
row of data, yet keeping it out when the 29th day doesn't exist.

FatBytestard,
Should have been:
FatBytestard,
For the February Worksheet.
A1: Date Header
A2=2/1/09 (year will increase with new workbook)
A3: =A2+1

Drag cell contacts to A29.
In Cell A30 place the following formula:
=IF(DAY(A2+28)<29,"",A2+28)

For charting just include Row#30 in your range.

--

"Rich/rerat" wrote in message
...
FatBytestard,
For the February Worksheet.
A1: Date Header
A2=2/1/09 (year will increase with new workbook)
A3: =A2+1
Drag cell contacts to A28.
In Cell A29 place the following formula:
=IF(DAY(A2+28)<29,"",A2+28)

For charting just include Row#30 in your range.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule
<<Previous Text Snipped to Save Bandwidth When Appropriate


"FatBytestard" wrote in message
...


I have a blood pressure tracker on the templates page (Microsoft's)
that can handle any year.

I just realized, however, that I made no proviso for leap years. So I
am going to add a 29th day in the February moth sheet of my workbook.

What I want to do is a formula along the lines of "if exist" that 29th
day of February in the chosen year, when true the date would be returned
to the cell location, and the plotted chart would respond accordingly.

If the day does not exist, the cell remains blank, and the plot figures
on the 28 day month.


Additional question:

Can the data set a plot is derived from be called out in a cell such
that the chart varies according to the cell contents, choosing a named
data set dependent on the result. So I could mark the sheet at the 28th
day row , and give it a name (28DayMonth), and I could mark a 29 day data
set, and name it LeapYear or LeapData, forcing the chart to use the added
row of data, yet keeping it out when the 29th day doesn't exist.