I need a february 29th row, but only if the day of year actually exists

[FatBytestard]

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.

 

[Bernard Liengme]

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

 

[FatBytestard]

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

 

[mubashir aziz]

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

 

[David Biddulph]

Doesn't that make 2100 a leap year?

 

[mubashir aziz]

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

Doesn't that make 2100 a leap year?

 

[David Biddulph]

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

 

[mubashir aziz]

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

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

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

year actually exists - The Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=98265)

 

[David Biddulph]

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

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

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

 

[mubashir aziz]

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

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

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

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

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

 

[Simon Lloyd]

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]

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)

 

[FatBytestard]

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?

 

[Simon Lloyd]

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

 

[Archimedes' Lever]

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.

 

[proteusiiv]

  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

 

[proteusiiv]

  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?

EAT SHOT ASSHOLE ARCIMEDES' LEVER DON KELLY YOU FUCKING BASTARD

I AM PROTEUS

 

[Rich/rerat]

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




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.

 

[Rich/rerat]

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.

--

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




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.