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
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" <FatByt...@somewheronyourharddrive.org> wrote in message
news:ksj615d02gjruvc40...@4ax.com...
FatBytestard
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).
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
--
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/showthread.php?t=98265
David Biddulph
--
David Biddulph
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 ....
David Biddulph
got a separate problem.
--
David Biddulph
"mubashir aziz" <mubashir.a...@thecodecage.com> wrote in message
news:mubashir.a...@thecodecage.com...
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. ....
David Biddulph;351405 Wrote:
> 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" <mubashir.a...@thecodecage.com> wrote in
> message
> news:mubashir.a...@thecodecage.com...
> >
> > 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:[color=green]
> >> Doesn't that make 2100 a leap year?
> >> --
> >> David Biddulph
> >>
> >> mubashir aziz wrote:[color=darkred]
> >> > 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/showthread.php?t=98265)
David Biddulph
world.
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 .......
--
mubashir aziz
Simon Lloyd
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 .......
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
Simon Lloyd
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
not that i worked it out rather that i went here 'Leap Year Calculator'
(http://www.dataip.co.uk/Reference/LeapYear.php)
FatBytestard
<Simon.Llo...@thecodecage.com> 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?
Simon Lloyd
FatBytestard;354132 Wrote:
> On Thu, 21 May 2009 16:58:24 +0100, Simon Lloyd
> <Simon.Llo...@thecodecage.com> 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
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
--------------------
Archimedes' Lever
<Simon.Llo...@thecodecage.com> wrote:
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.
prote...@gmail.com
wrote:
>
> - Show quoted text -
FAGGOT
I AM PROTEUS
Rich/rerat
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" <FatByt...@somewheronyourharddrive.org> wrote in message
news:ksj615d02gjruvc40...@4ax.com...
Rich/rerat
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" <rrr_...@isp.com> wrote in message
news:O7PHWYJ6...@TK2MSFTNGP04.phx.gbl...
prote...@gmail.com
EAT SHOT ASSHOLE ARCIMEDES' LEVER DON KELLY YOU FUCKING BASTARD
I AM PROTEUS