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

In your country 2100 may be a leap year, but it isn't in the rest of the
world.
--
David Biddulph

mubashir aziz wrote:[color=blue]
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=green]
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())
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)

Hey David pls. don't get personnel wd: , I don't ve to take anything
with leap year as i was just concerned about my formula and in any case
it will work fine :) as whatever be the dates .......



David Biddulph;352811 Wrote: [color=blue]
In your country 2100 may be a leap year, but it isn't in the rest of
the
world.
--
David Biddulph

mubashir aziz wrote:
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:
Mine goes correctly from 28 Feb 2100 to 1 Mar 2100, so it looks
like[color=green]
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())
mubashir aziz

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




--
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

mubashir aziz;352830 Wrote:
Hey David pls. don't get personnel wd: , I don't ve to take anything
with leap year as i was just concerned about my formula and in any case
it will work fine :) as whatever be the dates .......
I have to admit, i believed the year 2100 was a leap year, as i
thought 2008 was a leap year, taking a look at year 2096 shows February
to have 29 days, and as it happens every 4 years i would have assumed
that 2100 was a leap year, i live in the UK if that makes a difference
:), aside from that i assume that the year given was just an example to
prove a formula.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98265

Simon Lloyd;352847 Wrote:
I have to admit, i believed the year 2100 was a leap year, as i thought
2008 was a leap year, taking a look at year 2096 shows February to have
29 days, and as it happens every 4 years i would have assumed that 2100
was a leap year, i live in the UK if that makes a difference :), aside
from that i assume that the year given was just an example to prove a
formula.It bugged me a bit and guess what 2100 isn't a leap year...my apologies,
not that i worked it out rather that i went here 'Leap Year Calculator'
(http://www.dataip.co.uk/Reference/LeapYear.php)


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98265

On Thu, 21 May 2009 16:58:24 +0100, Simon Lloyd
wrote:


Simon Lloyd;352847 Wrote:
I have to admit, i believed the year 2100 was a leap year, as i thought
2008 was a leap year, taking a look at year 2096 shows February to have
29 days, and as it happens every 4 years i would have assumed that 2100
was a leap year, i live in the UK if that makes a difference :), aside
from that i assume that the year given was just an example to prove a
formula.It bugged me a bit and guess what 2100 isn't a leap year...my apologies,
not that i worked it out rather that i went here 'Leap Year Calculator'
(http://www.dataip.co.uk/Reference/LeapYear.php)


OK. That web page has a java script, but he describes what is needed.

So I need to resolve whether a math result is a whole number.

What is the basic function text for that?

I have to test a given number for divisibility by 4, then by 100, then
by 400.

I only need one test to construct the other two, but what string?

IfIsInterger? function, or is there a test function that only returns
true if the number is a whole number?

FatBytestard;354132 Wrote:
On Thu, 21 May 2009 16:58:24 +0100, Simon Lloyd
wrote:


Simon Lloyd;352847 Wrote:
I have to admit, i believed the year 2100 was a leap year, as i
thought
2008 was a leap year, taking a look at year 2096 shows February to
have
29 days, and as it happens every 4 years i would have assumed that
2100
was a leap year, i live in the UK if that makes a difference :),
aside
from that i assume that the year given was just an example to prove
a
formula.It bugged me a bit and guess what 2100 isn't a leap
year...my apologies,
not that i worked it out rather that i went here 'Leap Year
Calculator'
('Leap Year Calculator'
(http://www.dataip.co.uk/Reference/LeapYear.php))


OK. That web page has a java script, but he describes what is needed.

So I need to resolve whether a math result is a whole number.

What is the basic function text for that?

I have to test a given number for divisibility by 4, then by 100, then
by 400.

I only need one test to construct the other two, but what string?

IfIsInterger? function, or is there a test function that only returns
true if the number is a whole number?Here's a function, supplied by RoyUk (a moderator at The Code Cage and
has his own site 'www.excel-it.com' (http://www.excel-it.com)) that
should suit your needs.

Code:
--------------------
Option Explicit

Function LeapYear(year As Integer) As Boolean
If (Month(DateSerial(year, 2, 29)) = 2) Then
LeapYear = True
Else: LeapYear = False
End If
End Function
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98265

On Fri, 22 May 2009 08:39:29 +0100, Simon Lloyd
wrote:


FatBytestard;354132 Wrote:
On Thu, 21 May 2009 16:58:24 +0100, Simon Lloyd
wrote:


Simon Lloyd;352847 Wrote:
I have to admit, i believed the year 2100 was a leap year, as i
thought
2008 was a leap year, taking a look at year 2096 shows February to
have
29 days, and as it happens every 4 years i would have assumed that
2100
was a leap year, i live in the UK if that makes a difference :),
aside
from that i assume that the year given was just an example to prove
a
formula.It bugged me a bit and guess what 2100 isn't a leap
year...my apologies,
not that i worked it out rather that i went here 'Leap Year
Calculator'
('Leap Year Calculator'
(http://www.dataip.co.uk/Reference/LeapYear.php))


OK. That web page has a java script, but he describes what is needed.

So I need to resolve whether a math result is a whole number.

What is the basic function text for that?

I have to test a given number for divisibility by 4, then by 100, then
by 400.

I only need one test to construct the other two, but what string?

IfIsInterger? function, or is there a test function that only returns
true if the number is a whole number?Here's a function, supplied by RoyUk (a moderator at The Code Cage and
has his own site 'www.excel-it.com' (http://www.excel-it.com)) that
should suit your needs.

Code:
--------------------
Option Explicit

Function LeapYear(year As Integer) As Boolean
If (Month(DateSerial(year, 2, 29)) = 2) Then
LeapYear = True
Else: LeapYear = False
End If
End Function
--------------------

Yes. When I entered 2/29 for this year into a cell, it came back as
3/01. I figured it was something simple like that.

Thanks.

On May 22, 8:01*am, Archimedes' Lever
wrote:
On Fri, 22 May 2009 08:39:29 +0100, Simon Lloyd





wrote:

FatBytestard;354132 Wrote:
On Thu, 21 May 2009 16:58:24 +0100, Simon Lloyd
wrote:

Simon Lloyd;352847 Wrote:
I have to admit, i believed the year 2100 was a leap year, as i
thought
2008 was a leap year, taking a look at year 2096 shows February to
have
29 days, and as it happens every 4 years i would have assumed that
2100
was a leap year, i live in the UK if that makes a difference :),
aside
from that i assume that the year given was just an example to prove
a
formula.It bugged me a bit and guess what 2100 isn't a leap
year...my apologies,
not that i worked it out rather that i went here 'Leap Year
Calculator'
('Leap Year Calculator'
(http://www.dataip.co.uk/Reference/LeapYear.php))

OK. That web page has a java script, but he describes what is needed.

So I need to resolve whether a math result is a whole number.

What is the basic function text for that?

I have to test a given number for divisibility by 4, then by 100, then
by 400.

I only need one test to construct the other two, but what string?

IfIsInterger? function, or is there a test function that only returns
true if the number is a whole number?Here's a function, supplied by RoyUk (a moderator at The Code Cage and
has his own site 'www.excel-it.com'(http://www.excel-it.com)) that
should suit your needs.

Code:
--------------------
* *Option Explicit

*Function LeapYear(year As Integer) As Boolean
*If (Month(DateSerial(year, 2, 29)) = 2) Then
*LeapYear = True
*Else: LeapYear = False
*End If
*End Function
--------------------

* Yes. *When I entered 2/29 for this year into a cell, it came back as
3/01. *I figured it was something simple like that.

* Thanks.- Hide quoted text -

- Show quoted text -

FAGGOT

I AM PROTEUS

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.