Listing months and years in two columns based on start and end date.

Hello Everyone,

I freely admit to being an Excel idjut and I hope you all can help m
out.  What I would like to do is enter a start date in one cell and en
date in another cell.  Then I would like for the spreadsheet to lis
the months in between the start date and end date in one column and th
associated year to that month in the next column.

For example, if my start date is May 2000 and my end date is July 2001
the columns should be May 2000, June 2000, July 2000 until the end dat
is reached.

Is there a way to do this?

Thanks!

KLyn

--
Message posted from http://www.ExcelForum.com

0
5/6/2004 6:34:25 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
399 Views

Similar Articles

[PageSpeed] 27

I forgot to mention...I'm using Excel 97.

KLyn

--
Message posted from http://www.ExcelForum.com

0
5/6/2004 6:49:52 PM
On Thu, 6 May 2004 13:34:25 -0500, KLynnB
<<KLynnB.15uknb@excelforum-nospam.com>> wrote:

>Hello Everyone,
>
>I freely admit to being an Excel idjut and I hope you all can help me
>out.  What I would like to do is enter a start date in one cell and end
>date in another cell.  Then I would like for the spreadsheet to list
>the months in between the start date and end date in one column and the
>associated year to that month in the next column.
>
>For example, if my start date is May 2000 and my end date is July 2001,
>the columns should be May 2000, June 2000, July 2000 until the end date
>is reached.
>
>Is there a way to do this?
>
>Thanks!
>
>KLynn
>
>

If StartDate is in A1, then place the following formula in B1:


=IF(A1="","",IF(DATE(YEAR(A1),MONTH(A1)+1,1)<=EndDate,DATE(YEAR(A1),MONTH(A1)+1,1),""))

and copy/drag it as far to the right as you might possibly have columns (i.e.
IV1).


--ron
0
ronrosenfeld (3122)
5/6/2004 10:37:39 PM
Thank you ron.  :

--
Message posted from http://www.ExcelForum.com

0
5/7/2004 1:31:19 PM
On Fri, 7 May 2004 08:31:19 -0500, KLynnB
<<KLynnB.15w1a5@excelforum-nospam.com>> wrote:

>Thank you ron.  :)
>
>
>---
>Message posted from http://www.ExcelForum.com/

I'm glad it worked for you.
--ron
0
ronrosenfeld (3122)
5/7/2004 4:14:19 PM
Hey Ron (or anyone else),

To expand on this, I'm trying to fill the column down with the dates for 
that month and it not include any days from the same month, the next 
month, but for the cell to be blank after the last day of the month. 
I've been playing with your formula below, adding DAY.

=IF(D33="","",IF(DATE(YEAR(D33),MONTH(D33),DAY(D33+1))<=$G$3,DATE(YEAR(D33),MONTH(D33), 
DAY(D33+1)),""))

D33=the first day of the month from your formula below
G33=the first day of the next month (I have 2 columns in between the 
month columns)

Problem is, after I get to the last day of the month, it goes back to 
the 1st of the month. I understand why this is happenning in the 
formula, but do not know how to get it be blank or not to be the first 
day of the next month.

As is:
..
..
..
27-Jul-04	1-Aug-04
28-Jul-04	2-Aug-04
29-Jul-04	.
30-Jul-04	.
31-Jul-04	.
1-Jul-04

Should be:
..
..
..
27-Jul-04	1-Aug-04
28-Jul-04	2-Aug-04
29-Jul-04	.
30-Jul-04	.
31-Jul-04	.

Any help would be appreciated.

Thanks,

Tom

Ron Rosenfeld wrote:
> On Thu, 6 May 2004 13:34:25 -0500, KLynnB
> <<KLynnB.15uknb@excelforum-nospam.com>> wrote:
> 
> 
>>Hello Everyone,
>>
>>I freely admit to being an Excel idjut and I hope you all can help me
>>out.  What I would like to do is enter a start date in one cell and end
>>date in another cell.  Then I would like for the spreadsheet to list
>>the months in between the start date and end date in one column and the
>>associated year to that month in the next column.
>>
>>For example, if my start date is May 2000 and my end date is July 2001,
>>the columns should be May 2000, June 2000, July 2000 until the end date
>>is reached.
>>
>>Is there a way to do this?
>>
>>Thanks!
>>
>>KLynn
>>
>>
> 
> 
> If StartDate is in A1, then place the following formula in B1:
> 
> 
> =IF(A1="","",IF(DATE(YEAR(A1),MONTH(A1)+1,1)<=EndDate,DATE(YEAR(A1),MONTH(A1)+1,1),""))
> 
> and copy/drag it as far to the right as you might possibly have columns (i.e.
> IV1).
> 
> 
> --ron

0
tomb_ga (1)
6/23/2004 8:37:08 PM
Reply:

Similar Artilces:

microsoft outlook date and time incorrect
Please guys everyone knows about the date settings on microsoft outlook so this fault is alittle harder than that to locate. Emails while in your own computer or sent to yourself have the correct date and time. When opened in the receiving computer it has the correct date and time. But as listed unopened in the receiving computer it has the incorrect and time. date might be the previous day or the next day. Tme has no relationo time sent. This Microsoft outlook using office 2000 or office 2007 on the same computer. Any advice appreciated. It would be best to ask this...

getting a list of permissions on a folder
Hi all, using 2008r2 now. I need to get a list of users permissions by folder and subfolders. We have a shared folder called Public and under that is subfolders with many different folders and different permissions for each folder. Does 2008R2 have an easy way to list the permissions for a folder? I don't know how to use powershell so what else would be available to do this. Thanks in advance for your help on this. Sher "Sher" <Sher@discussions.microsoft.com> wrote in message news:E7DB3CBA-0F8F-4143-BEAF-3A527EB0D102@microsoft.com... > Hi all, > u...

find next number in list
hi all, I am using excel to maintain a list of dvds I rent online, and the order in which they are sent to me. There are three columns: first the number the dvd has in my list, then the title, and lastly the number of when it was sent, something like this: 1 dvd1 3 2 dvd2 1 3 dvd3 4 dvd4 2 Now, when I receive a new dvd (in the example: dvd3), I want to add the next number (here: 4) to the 3rd column. When the numbers get higher it gets harder to find the next number manually. Is there any way to automatically find this number, without using macros (for which I am too lazy)? TIA ...

searching a column for duplicate, then replacing the row.
i have a userform that pulls in information from a row on spreadsheet. i.e. workbook(1).sheets(1) column A contains a list of numbers. userform1.listbox1 has a list of all the numbers in column A when the listbox1 is clicked it fills in all the appropriate control with what is in the other columns on the same row. column A number i placed in textbox1 on userform. right now, after changes to the form and the user clicks th commandbutton to save. it creates a whole new row with the same numbe in column A, so i have duplicates. is there a way to replace the entire row instead? with a msgbo...

Counting only uppercase letters with wildcard in column/row
I'm working with columns and/or rows containing both upper and lower case letters with wildcards. I only want to count upper case letters with wildcards. Any help to this problem will be greatly appreciated. BTW I'm using excel 2007. Thanks! Post the formula that you are using now that counts all cases -- Regards, Peo Sjoblom "TJA" <TJA@discussions.microsoft.com> wrote in message news:8E30D684-0A90-4064-BE72-738552550D3C@microsoft.com... > I'm working with columns and/or rows containing both upper and lower case > letters with wildcards. I only ...

Hiding columns
Hi there all, I've got a tiny table in excel, I've hidden all the rows below the used ones to make it look nicer. Why can't I hide all the columns I don't want to use? Cheers, Pete "Peter Fossey" <peter@fossweb.net> wrote in message news:0a8501c34c59$1d215570$a101280a@phx.gbl... > Hi there all, > > I've got a tiny table in excel, I've hidden all the rows > below the used ones to make it look nicer. > Why can't I hide all the columns I don't want to use? > > Cheers, > Pete You should be able to do this. What happens wh...

Date Format #18
Greetings, I got a workbook that was done on English PC, but the ender user wil be using thier PC with Spanish setting, the issue is the date format i English is (mm/dd/yy) MONTH, DAY, YEAR but in Spainsh it's (dd/mm/aa DIA, MES, ANO . . . the main problem is the "YY" (YEAR) vs "AA" (ANO if a spanish PC is used and a date is entered it will cuase a erro like this "JAN-01-YYYY". 1- If there a way to force an English date format in this workbook an turn back Spanish. 2- Is there a VB solution to this. 3- I should I resort to a DATE forumla, e.g. drop box...

Make rows be columns & vice versa
I have a spreadsheet that I need to make the columns into rows and rows into columns. Does this make sense and if so, how to I do it? TIA...Rebecca --- Message posted from http://www.ExcelForum.com/ Hi not quite sure but have a look at 'Edit - Paste Special - transpose' to transpose a copied range -- Regards Frank Kabel Frankfurt, Germany "rebeccaford >" <<rebeccaford.14ar3o@excelforum-nospam.com> schrieb im Newsbeitrag news:rebeccaford.14ar3o@excelforum-nospam.com... > I have a spreadsheet that I need to make the columns into rows and rows > into co...

List and data storage
I'm creating a new site where many of the fields will reference OTHER lists - for example a list of clients, a list of employees, a list of work types, etc. Is there a best practice for "storing" these standalone lists that will be referred to by OTHER lists but not exposed directly to the end users? For example, is it common to create a "hidden" site with lists and data that is used this way but not exposed? Many of these lists will be referenced from multiple "applications" on the sites. Gavin Anyone :-) "Gavin Steiner"...

List control Full Width Selection
How would I enable 'full width selection' in my list control? I.e. when in report mode, clicking in any column will select the item and the selection highlight spans the entire list control rather than just the first column. TIA, --- Al. Sorry, a little more investigation and I found it... // Get the list view and control CListView *pLV = (CListView*)GetDlgItem ( IDC_RESULTS ) ; CListCtrl &rLC = pLV->GetListCtrl() ; // Set full-width selection rLC.SetExtendedStyle ( rLC.GetExtendedStyle() | LVS_EX_FULLROWSELECT ); --- Al. >-----Original Message----- >...

Dates
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C87B24.A4059ED0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Windows XP, Office 97 (or Office 2003) Trying to track and record the number of visits a person has made. = Plus, record the date of all their visits. Here's what I'd like to do. =20 Have Column D record the last date a person attended, and Column E to = show the total visits. I want to enter the latest date in Column D, visibly replacing that = date, but retaining history for all dates visite...

date data type is not a date??
Following is code I am running. I intend to compare two dates. One date (orderrg.value) is from a spreadsheet and is defined as a type date. The other date is the system date. However when I check to see if the orderrg.value is a date via ISDATE, it fails. Following the code is the prinout from the immediate window. It looks to me like it should be able to recognize it as a date, but yet it doesn't. Does anyone have any ideas? Private Sub Workbook_Open() Dim wscommission As Worksheet Dim nindex As Integer Dim lastorder As Integer Dim sheetname As String Dim orde...

Blocked Senders List 12-29-09
I put an e-mail address on my blocked senders list, but now I want to take it off-how do I do that? Tools | Junk E-mail Options | Blocked Senders. Find the address and remove it. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "5hulses" <5hulses@discussions.microsoft.com> wrote in message news:A30C50F8-A901-49D3-870F-B57DE0ABE445@microsoft.com... >I put an e-mail address on my blocked senders list, but now I want to >take it > off-how do I do that? "5hulses" <5hulses@discussions.microsoft.com> a ...

two servers shut down at the same time?
I have two new HP ML 350 servers, one has been up a couple days, the other has been running for a couple months. Yesterday, they both went down at 2:40 PM, within 20 seconds of each other. It looks like a power failure, but they are in different rooms, each with it's own APC SC1500 UPS. There was no power interruption at the time, machines not on UPS never went down. Log files only say unexpedted shutdown possibly due to crash or power failure. What in the world could have caused that? Do I have two defective UPS's? "dlw" <dlw@discussions.microsoft....

Calculating Days Between Dates
I use the following formula to calculate the days between dates and to return zero when dates are the same � =IF(OR(A1="",A2=""),0,DATEDIF(A1,A2, "d")) How would I also allow for the eventuality where the date in cell A2 is earlier than A1 and where the result would then be a minus figure ? At present the formula returns a "Num" error when this happens -- martins ------------------------------------------------------------------------ martins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31616 View this thread: htt...

top menu shows two 'file' headers and dropdowns
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC when I open Word, the top menu bar shows <br> apple word file file view insert etc <br> in other words, two file headers and the dropdowns beneath them. <br> the dropdowns differ slightly from one another <br> otherwise, Word seems to be working fine, <br> I don't like the look of it, though. <br> Any solutions gratefully received. Am happy to send a screen shot to illustrate the point, if that would help. <br> thank you First, try a "Power-off rest...

Two copies of same ON 2007 notebook open
Onenote 2007 SP2 Windows 7 Home Premium 64-bit Login is an admin account Sometimes when changing sections within a notebook, Onenote is bringing up a dialog asking whether I want to open the whole notebook, or just a section. The notebook I would be opening is the same one I'm already in. Choosing to open the whole notebook, I get another copy of the same notebook listed in my Notebooks bar on the left of the screen, which then starts synchronizing.. apparently with itself. Checking the properties, the location of my original notebook is what I expect it to be, in C:\Us...

Keeping past appointments longer than 3 months?
After three months, my outlook is removing past items in my Calender and this is causing me real problems as I'm a contractor and need to know the days I worked for upto a year (or more). I cannot find the settings in Outlook to leave the appointments in the Calender. I don't want to archive them, simply leave them in the calender indefinitely. Any help would be much appreciated! Thanks in advance, V. "V" <V@discussions.microsoft.com> wrote in message news:BC3BA38E-83DF-43A4-8290-94672EEEC716@microsoft.com... > After three months, my outlook is re...

Where to get Excel spreadsheet listing all GP tables for v10
Is this spreadsheet on the sdk? Is there a way to get it without installing the sdk? That is, from a download off MS site or PartnerSource or CustomerSource ? Thanks The spreadsheet is available from the DynamicAccounting.net site or via direct download at http://www.box.net/shared/jc90kdsh2k. I built it. It's a re-presentation of the information found in Tools- >Resource Descriptions->Tables in GP. There are version for GP 9 and GP 10 and I'll have one for GP 2010 once it is final. Mark On Feb 3, 2:01=A0pm, hdjim <hdji...@yahoo.com> wrote: > Is...

available columns in ADUC
In ADUC, from "View" -> add/remove columns I can't see some useful columns such as mobile number and fax number for user objects. How can I add them to the available columns list? many thanks Howdie! On 11.03.2010 04:35, tree leafs wrote: > In ADUC, from "View" -> add/remove columns I can't see some useful columns > such as mobile number and fax number for user objects. How can I add them to > the available columns list? Paul has a good article on this: http://pberblog.com/post/2009/06/21/Add-extra-columns-to-Active-Directory-U...

FORMULA HELP! Find all values in column at random rows and perform calcutlation.
ex. A 1 2 3 3.9% 4 5 6 -2.7% 7 8 9 6.5% 10 11 12 1.2% .. .. .. 1000 Total of formula here. I don't know what row a % number will show up on col A but it is before row 1000. I would like row 1000 under column A to have a formula that says look for any row in column A and if you find a number, add 1 to that number and then find the next one and add 1 to that number and then multiply those two together and then find the next one add 1 then multiply to the last set...etc. Bascially, A100 would have the formula (1+A3)*(1+A6)*(1+A9)*(1+A12)...e...

Money 03, how to extend date range for cash flow scenario?
Hi guys, as above. I am presented with only a 2 month timeframe when I try the cash flow scenario. How can I set it so I can have a 12 month timeframe? Thanks guys! Buck If, by cash flow scenario, you mean the forecast cash flow graph, go to Forecast your cash flow and manipulate the Time Period just above and to the right of the graph. This allows a whole bunch of choices up to Custom. "Buck Rogers" <who@cares.com.au> wrote in message news:DvuSa.762$vD1.32261@nnrp1.ozemail.com.au... > I am presented with only a 2 month timeframe when I try the > cash flow scenari...

Update field w/ string based on service due dates
Hi, I have a form that provides a listing of various instruments that are serviced on a monthly to annual basis, with general info about the instrument, last and next service dates, etc.. What I am trying to do is create an On Open event procedure that will identify all instruments that are listed with a string of either "Past Due" or "Schedule Vendor" based on whether each of their respective 'Due' dates have already transpired or not from the current month. The code I initially created for this was as follows: Private Sub Form_Open() Dim myDate, myDue myD...

Sorting columns
I have 10 columns with 30 entry cells. Some cells have a single word other are blank. I want to copy all words to a single colum and them alphabetize them Hi, try this it copies the first 10 columns to column 11 omitting blanks Sub marine() Dim NewRow As Long, NewColumn As Long Dim X As Long, Col as Long NewRow = 1 NewColumn = 11 'Change to suit For col = 1 To 10 For X = 1 To ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row If Cells(X, col) <> "" Then Cells(NewRow, NewColumn).Value = Cells(X, col).Value NewRow = NewRow + 1 End If Next Next End Sub ...

alphabetize entire rows by first word of first column
I want to make a list of names and addresses putting name, address, city, state, zip across in separate columns. Is there a way to attach these so that when I alphabetize the names each of the cells in the row stay with the correct person? When you go to alphabatize, aka sort the data, make sure you select ALL of the columns. -- Best Regards, Luke M "Cindy Sue" <Cindy Sue@discussions.microsoft.com> wrote in message news:15D7A4A6-500E-43F6-8974-E0A661B79222@microsoft.com... >I want to make a list of names and addresses putting name, address, city, >...