leap year question

Hi,
I have a column for dates, starting with the first day of the year. I only 
have to input the first day on cell A@ (say 01/01/06) and the all the days of 
the years will be display in the same column (I wrote the simple formula 
=A2+1).  Now the next leap year is 2008 and i don't want to have to copy the 
last row to add the extra day, I want excel to display the last row of the 
date column if it's a leap year and not to display it when is not.  What 
should I do?  Thank you
Thalia
0
tiw (11)
4/20/2005 5:41:03 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
637 Views

Similar Articles

[PageSpeed] 27

use a formula in A2 of

=A1+(YEAR(A1+1)=YEAR(A1)

and copy down to A366


or even just put =IF(YEAR(A365+1)=YEAR(A365),A365+1,"") in A366

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"tiw" <tiw@discussions.microsoft.com> wrote in message
news:38C7238A-8AFA-4D3C-972A-F7D2A4CE94F0@microsoft.com...
> Hi,
> I have a column for dates, starting with the first day of the year. I only
> have to input the first day on cell A@ (say 01/01/06) and the all the days
of
> the years will be display in the same column (I wrote the simple formula
> =A2+1).  Now the next leap year is 2008 and i don't want to have to copy
the
> last row to add the extra day, I want excel to display the last row of the
> date column if it's a leap year and not to display it when is not.  What
> should I do?  Thank you
> Thalia


0
bob.phillips1 (6510)
4/20/2005 6:02:26 PM
Hi Thalia,

With one exception (1900) Excel knows exactly which years are leap years. If 
you just add 1 day, it will skip from Feb 28 to Mar 1 on one year, and to 
Feb 29 in another.

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"tiw" <tiw@discussions.microsoft.com> wrote in message 
news:38C7238A-8AFA-4D3C-972A-F7D2A4CE94F0@microsoft.com...
> Hi,
> I have a column for dates, starting with the first day of the year. I only
> have to input the first day on cell A@ (say 01/01/06) and the all the days 
> of
> the years will be display in the same column (I wrote the simple formula
> =A2+1).  Now the next leap year is 2008 and i don't want to have to copy 
> the
> last row to add the extra day, I want excel to display the last row of the
> date column if it's a leap year and not to display it when is not.  What
> should I do?  Thank you
> Thalia 


0
nicolaus (2022)
4/20/2005 6:08:53 PM
Hi Niek,
I know that, My issue is that I have a whole year of dates from Jan 1st to 
Dec 31, and on a leap year I would have one extra day.  in cell A2 I input 
the first day of the year say 01/01/05 and in the cell below I wrote the 
formula =A2+1 and carried that formula down to get all the cells I need for 
the year.  On 2008 I will need an extra cell in the end for the extra year 
wich will display 12/01/08 but in other years if I leave that cell it will 
display the first day of the next year and I don't want it to be displayed 
unless it's a leap year.
Thalia

"Niek Otten" wrote:

> Hi Thalia,
> 
> With one exception (1900) Excel knows exactly which years are leap years. If 
> you just add 1 day, it will skip from Feb 28 to Mar 1 on one year, and to 
> Feb 29 in another.
> 
> -- 
> 
> Kind Regards,
> 
> Niek Otten
> 
> Microsoft MVP - Excel
> 
> "tiw" <tiw@discussions.microsoft.com> wrote in message 
> news:38C7238A-8AFA-4D3C-972A-F7D2A4CE94F0@microsoft.com...
> > Hi,
> > I have a column for dates, starting with the first day of the year. I only
> > have to input the first day on cell A@ (say 01/01/06) and the all the days 
> > of
> > the years will be display in the same column (I wrote the simple formula
> > =A2+1).  Now the next leap year is 2008 and i don't want to have to copy 
> > the
> > last row to add the extra day, I want excel to display the last row of the
> > date column if it's a leap year and not to display it when is not.  What
> > should I do?  Thank you
> > Thalia 
> 
> 
> 
0
tiw (11)
4/20/2005 6:37:12 PM
Hi Bob,
Thank you, I used the second formula and it works, I tried the first one and 
I don't know why it didn't work.
Thalia

"Bob Phillips" wrote:

> use a formula in A2 of
> 
> =A1+(YEAR(A1+1)=YEAR(A1)
> 
> and copy down to A366
> 
> 
> or even just put =IF(YEAR(A365+1)=YEAR(A365),A365+1,"") in A366
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "tiw" <tiw@discussions.microsoft.com> wrote in message
> news:38C7238A-8AFA-4D3C-972A-F7D2A4CE94F0@microsoft.com...
> > Hi,
> > I have a column for dates, starting with the first day of the year. I only
> > have to input the first day on cell A@ (say 01/01/06) and the all the days
> of
> > the years will be display in the same column (I wrote the simple formula
> > =A2+1).  Now the next leap year is 2008 and i don't want to have to copy
> the
> > last row to add the extra day, I want excel to display the last row of the
> > date column if it's a leap year and not to display it when is not.  What
> > should I do?  Thank you
> > Thalia
> 
> 
> 
0
tiw (11)
4/20/2005 6:49:02 PM
see my response

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"tiw" <tiw@discussions.microsoft.com> wrote in message
news:56623A7B-3889-4386-BDBA-8F94BF44BD82@microsoft.com...
> Hi Niek,
> I know that, My issue is that I have a whole year of dates from Jan 1st to
> Dec 31, and on a leap year I would have one extra day.  in cell A2 I input
> the first day of the year say 01/01/05 and in the cell below I wrote the
> formula =A2+1 and carried that formula down to get all the cells I need
for
> the year.  On 2008 I will need an extra cell in the end for the extra year
> wich will display 12/01/08 but in other years if I leave that cell it will
> display the first day of the next year and I don't want it to be displayed
> unless it's a leap year.
> Thalia
>
> "Niek Otten" wrote:
>
> > Hi Thalia,
> >
> > With one exception (1900) Excel knows exactly which years are leap
years. If
> > you just add 1 day, it will skip from Feb 28 to Mar 1 on one year, and
to
> > Feb 29 in another.
> >
> > -- 
> >
> > Kind Regards,
> >
> > Niek Otten
> >
> > Microsoft MVP - Excel
> >
> > "tiw" <tiw@discussions.microsoft.com> wrote in message
> > news:38C7238A-8AFA-4D3C-972A-F7D2A4CE94F0@microsoft.com...
> > > Hi,
> > > I have a column for dates, starting with the first day of the year. I
only
> > > have to input the first day on cell A@ (say 01/01/06) and the all the
days
> > > of
> > > the years will be display in the same column (I wrote the simple
formula
> > > =A2+1).  Now the next leap year is 2008 and i don't want to have to
copy
> > > the
> > > last row to add the extra day, I want excel to display the last row of
the
> > > date column if it's a leap year and not to display it when is not.
What
> > > should I do?  Thank you
> > > Thalia
> >
> >
> >


0
bob.phillips1 (6510)
4/20/2005 6:49:10 PM
Reply:

Similar Artilces:

ms money not start after new year
anyone encounter the same problem with mine that, my money 2003 not start after the new year. it keeps popup the widows to ask me send the error to microsoft. of course i didn't. i tried upgrade to 2004 and the problem still happen so. i tried to run installation setup check inside money system folder, it said something CLSID_CallSummery not register .... anyone can help? thanks. In microsoft.public.money, belinck wrote: > >anyone encounter the same problem with mine that, my money 2003 not start >after the new year. it keeps popup the widows to ask me send the error to &...

Recalc question
Is is possible to have an automatic worksheet recalc, say at a timed interval, or when the worksheet is accessed? This is required to update the now() function without having to press F9. Regards You can use an OnTime procedure. See: http://www.cpearson.com/excel/ontime.htm -- Gary''s Student - gsnu200726 "newman" wrote: > Is is possible to have an automatic worksheet recalc, say at a timed > interval, or when the worksheet is accessed? > > This is required to update the now() function without having to press > F9. > > Regards > As an asid...

Query Question 03-05-08
I am working with queries pulling from an SQL linked table. I'm pulling orders within a given time period with the below fields. Each order line item appears on a separate line. Customer Name Order Number Date Item Type New Start Joe Smith 12345 3/3/08 Core Y Joe Smith 12345 3/3/08 Comp Y Pete Smith 33224 3/4/08 Comp Y I need to do a query to pull customers who only have Comp items only within the time period I pull, so ...

29th of February in the year 1900
The year 1900 was not a leap year and we all know that but Excel will show a 29 of February for that year. We also know that there was a bug in Lotus 1-2-3 so Microsoft decided to leave things the way they were in Excel so Lotus users would have less code to change if they wanted to switch to Excel. My question is: "February 29th 1900 is a Wednesday. That makes the 1st of April 1900 a Thursday (it should have been a Wednesday). Every following day, including today, is then offset by one week day. How come Excel calculates today, September 8th 2003, as a Monday. Where in between ...

Publisher Question #3
How do you draw a line around a selected area within a text box? I want to draw or out line an area on a picture I inserted to show the area of the landscape that is for sale. thanks Cindy With the image in place, go to the left tool bar and select the rectangle and use it to draw the line. -- Don Vancouver USA "Suziq" <Suziq@discussions.microsoft.com> wrote in message news:A22B4234-EC55-428B-8065-FA7999BD78FE@microsoft.com... > How do you draw a line around a selected area within a text box? I want to > draw or out line an area on a picture I inserted to show...

Question about comments
I recently migrated from Excel 5 to Excel 2000 and I have a question about comments. Is there a way to make comments behave like notes in excel 5? I don't like the fact that comments pop up every time the cursor is on a cell with a comment. I prefer to see comments only when I click on them. I tried to hide comments in the view settings but each time I view a comment the view preference is reset to the default. The only thing that works is to hide comments completely and select "edit comment" from the context menu each time I want to read one. However, I don't c...

subclassed button in a dialog bar question
I'm trying to add a subclassed button to a dialog bar. When I create the button in code, it's disabled. I think I know why it's disabled, but my question is how do I do this correctly? Thanks nevermind... duh! "guysmiley" <kermit@thefrog.com> wrote in message news:%23epHaJmhDHA.3204@TK2MSFTNGP11.phx.gbl... > I'm trying to add a subclassed button to a dialog bar. When I create the > button in code, it's disabled. I think I know why it's disabled, but my > question is how do I do this correctly? > > Thanks > > guysmiley wrote: ...

Connection Filtering Question
If a connection filter is set up on Exchange 2003 to apply RBLs, does the filter bounce the message and add the connection filter block message? Or does it only reply with the connection filter block message? This is not clear to me in the Exchange Help. TIA Phil Not sure if this is what you are after, but... In trying a direct connection to the SMTP virtual server via Outlook Express, I find that the SMTP transaction is rejected with a 550 5.7.1. The Outlook Express send/receive window does display the custom error message if one is configured. The message never leaves the outbox. &...

Delivery problems: Recipient Policies question (please help)
Hi. I have Windows 2003 SBS PRO (Win2003 Server + Exchange 2003 [Exchange 2000 mode] + ISA) server. The server is a PDC for mydomain.local . The default domain name for Exchange,... is also mydomain.local . After installation I used 'Configure E-Mail and Internet Connection Wizard' to connect my server to the internet. When I was asked what to do with my Exchange server, I selected 'Internet E-Mail' and configured POP3 connector to retrive mails from a single POP3 mailbox for @mydomain.si DNS domain. A user want to retrive also his mails from his POP3 account (user@...

Change date by one year
I have a list of birthdays formulated 8/12/05 I want to change all the dates to next year i.e. 8/12/06 oldjay You could use a formula: =date(year(a1)+1,month(a1),day(a1)) Oldjay wrote: > > I have a list of birthdays formulated 8/12/05 > I want to change all the dates to next year i.e. 8/12/06 > > oldjay -- Dave Peterson Or, for a "quick and dirty" solution Ctrl+h to bring up Find and Replace Find What /2005 Replace with /2006 Replace all Regards Roger Govier Dave Peterson wrote: > You could use a formula: > > =date(year(a1)+1,month(a1),day(...

Rename of worksheet question
I have a time card that I created and want to take the name from one of the cells that is in a vlookup table, and use that name to add a new worksheet with that name. When I record the macro it uses the name that is in the cell originally, but when I run the macro it still uses the same name as the first time? Is there a way of qualifying that I want the "current value" not the original name in the cell as the name for the worksheet? Thanks in advance John John, try something like this, Worksheets.Add.Name = Sheets("Sheet1").Range("A1").Value -- Pau...

query question
hi, i have already created a query between table A and table B. i printed out this query which only show the records were matched between table A and table B. some records in the table A was missed to show on the report. in fact, i wish that the report can show all records based on table A together with table B's information. how can i do it thanks for your help in advance jimmy Hi Jimmy are we talking Excel or Access, if excel i've no idea, but if it's Access, right mouse click on the join between table A and B in your query, choose Join Properties and choose the option...

Question about saving a layout
I like to zoom all documents I'm working on to 185% in Page View in Microsoft Word for Mac 2004. I'd like this to be the default view when I open any Word documents. How can I save this workspace so every document is opened at 185% zoom? Every time I save my document in this zoom, when I open it again its back to 122% zoom. I've looksed in prefs, but can find no settings. Thanks! Hello Michael, You should find that documents will re-open with the same zoom value you had when you closed them (and the same view, e.g. Page Layout view). To have blank new documents open at 185...

Home key behavior question
I just made the transition from Excel97 to Excel 2007 and am struggling with the usage of the home key. For years I have been using a bank balance journal that I created with Excel97. The sheet contains 5 columns (A-E) and is protected with the cells in columns A- D unlocked and the cells in column E locked. When this spread is open in Excel97 the home key will make the cell in column A of current row active. When open in Excel 2007 the home key activates the first unlocked cell in column A (A7 in my case as A1 thru E6 are locked). I have tried recreating the sheet from scratch in Excel 2...

Access Report question 04-16-10
I have a data set I am building regarding our dealerships and their service capacity. There are 15 different items or so per store and 5 years of data. For certain of the data I would like to create totals in the report, not however totals for all of the different items. Is there a way to accomplish this in a report? I would like the data to layout as follows Dealer code and name 2005 2006 2007 2008 CP RO's 100 WTY RO's 125 Int RO's ...

Newbie Question, Displaying scaled measurement while drawing a lin
Hello, I'm trying to find a setting to allow Visio to show the scaled measurement as I draw a line. As I draw the line, I'd like to see the value change with line length. I'm guessing I'm not looking in the right spot. Thanks for any help! Tim On Wed, 31 Dec 2008 10:50:01 -0800, TheTW <TheTW@discussions.microsoft.com> wrote: >Hello, > >I'm trying to find a setting to allow Visio to show the scaled measurement >as I draw a line. As I draw the line, I'd like to see the value change with >line length. > >I'm guessing I'm not loo...

Custom color question + smooth chart curves question
I have prepared a large number of Excel templates that have charts I pre-defined for the user. Each of the user-defined charts I set up uses colors that are non-standard (I added them into the color palette using RGB values). These custom colors conform to a corporate palette. These Excel templates were set up so that users could develop charts that they would paste into a Word template that had also been set up to conform to many of the same colors. Within Excel, I actually added the custom swatches to the palette, along the bottom row, which has room for such user-defined colors. The inten...

QSRules Question
Hi, Still investigating possible interaction with Store Ops, and am attempting to inteface with it using C#, presume its same process as VB. I have no idea how to even connect to Store Ops, should store ops be running, or will it be started by my app. I have written the code below to try to send a message to an operator, but am having no success, I get the status of 'statuscategoryServerFail' when attempting to send a message. I am obviously missing some initialisation step, can someone please tell me what it is unitil I get my hands on the Customization Guide. If someone could e...

Question #4
I,had install office 2003 and unstall now.How do restore eariler version which is office 2002.When I,did double click excel.exe from office 10 files folder did receive dialog information operating system is not presently configured to run this application.What kind of configuration I,should do.Highly appreciate if anyone help. If you post your question in the Excel Setup or Excel General Questions newsgroups, someone may be able to help. This newsgroup is for questions about Excel Charts. Philip wrote: > I,had install office 2003 and unstall now.How do restore eariler version &...

Outlook LDAP question
I just upgraded from Office 2000 to Office 2003. I love some of the new features, but others are very aggravating. If you can help, please reply. 1. Outlook 2003. When starting it asks for an LDAP server. I'm a home office user and dont even know what an LDAP server is, muh less have one. How can I shut this request off. 2. Outlook 2003. Layout. I like the Outlook 2000 layout much better. It is less cluttered and more functional. How can I change the layout to the older style? Thanks for your help mwalsh @ nashuais.com 1. Upgrade installations do not work smoothly. You m...

RE: Excel Drop down box question
Hey all, I am using excel 2007 on XP Professional SP2. I am using a drop down box on a worksheet that selects from a column of designated data elsewhere on the worksheet. The data I have is 1 row of codes, with 1 row of descriptions for that code. I would like to select a code in the drop down box and have the corresponding description fill in an adjacent cell to the drop down box. Is this possible? Example Codes Description 1 One 2 Two 3 Three 4 Four etc... When code '1' is selected in the drop down box for cell A1, I want cell A2 to populate ...

Voiding a prior year check
I am embarrassed to say that we are still running GP v5.5 (yes 5.5) and I am trying to void a check from a prior fiscal year that has already been closed. Believe me, I understand why the system won't let me do it, but I was wondering if anyone had some out of the box thinking as to how to accomplish this. I would like to maintain a paper trail for audit purposes so if anyone has any help to offer, I would greatly appreciate it. Thank you in advance. Deb, You should be able to do it no problem. Go to Transactions>Purchasing>Void Historical Transaction. Find the trx and mark...

Year-To-Date Query or Report
Does anyone have information on where I can find or how to do a YTD query or report? Thanks, Ron Here's a starting point: Subquery basics: Year to date at: http://allenbrowne.com/subquery-01.html#YTD -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rwr" <rwr@robcom.com> wrote in message news:%23yz6K72aHHA.4396@TK2MSFTNGP06.phx.gbl... > Does anyone have information on where I can find or how to do a YTD > query or report? > &g...

Install Question CRM Small Business Edition
In installation guides I mentioned for installing CRM Professional to create a crmadmin user in AD that should be used for installing CRM Pro Edition. To install the Small Business Edition is it recommended to do this as the Administrator user? or does also apply to create a crmadmin user? Thanks. Hi, That probably depends on how you are managing your SB server and whether the administrator of SB should or not have admin privileges on CRM. The alternative is to create a CRM admin user and give them domain admin privileges for the install and then take these away after. Regards, Pete...

queue question
I have about a dozen queues that can't send messages. The total message count across these queues is about 46 messages. Why when I look in C:\Program Files\Exchsrvr\Mailroot\vsi 1\Queue don't I see any files? OK, not it appears that all outbound mail has stopped. I have a lot of this in the SMTP virtual server log. What does it mean? 2007-02-09 03:06:04 64.18.7.10 OutboundConnectionResponse SMTPSVC1 MRBSERVER - 25 - - 220+Postini+ESMTP+37+y6_8_11c0+ready.++CA+Business+and+Professions+Code+Section+17538.45+forbids+use+of+this+system+for+unsolicited+electronic+mail+advertisemen...