Month and Year

Is there a way to have Exel only pick out a number from a column if th
corresponding cell in another column is the end of the month or year
For example in column A, I have the "Date" in "1/2/1984" format. I
column B, I have "Account Balance." Is it possible in column C to sho
the Account Balance only if it is the end of the month or end of th
year in column A?

Thanks in advance

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

0
2/9/2004 7:56:48 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
406 Views

Similar Articles

[PageSpeed] 13

End of month

    =IF(MONTH(A1)<>MONTH(A1+1),B1,"")

you won't need to test end of year, as end of year is also end of a month.
-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"deacs >" <<deacs.11dkgm@excelforum-nospam.com> wrote in message
news:deacs.11dkgm@excelforum-nospam.com...
> Is there a way to have Exel only pick out a number from a column if the
> corresponding cell in another column is the end of the month or year?
> For example in column A, I have the "Date" in "1/2/1984" format. In
> column B, I have "Account Balance." Is it possible in column C to show
> the Account Balance only if it is the end of the month or end of the
> year in column A?
>
> Thanks in advance!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
2/9/2004 8:33:37 PM
Bob,

Thanks so much for the help! It worked like a charm. Now, the next ste
for me is to figure out the % change in column C from month to mont
without having to manually look to see if column C contains any data
So, if column C has data, I'd like Excel calculate the monthly chang
in account balance. I assume it would just be another if statement, bu
I have been unable to make it work out right to account for th
different number of days in the months

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

0
2/9/2004 9:49:51 PM
Hi
try something like:
IF(C1<>"",calculate_change_formula,"")
and copy this down
Frank

> Bob,
>
> Thanks so much for the help! It worked like a charm. Now, the next
> step for me is to figure out the % change in column C from month to
> month without having to manually look to see if column C contains any
> data. So, if column C has data, I'd like Excel calculate the monthly
> change in account balance. I assume it would just be another if
> statement, but I have been unable to make it work out right to
> account for the different number of days in the months.
>
>
> ---
> Message posted from http://www.ExcelForum.com/


0
frank.kabel (11126)
2/9/2004 10:01:21 PM
Deacs,

Is that a request?

How about in D2

=IF(OR(C2="",C3=""),"",(C3-C2)/C2)

although I think this may give you a problem if you have non-month-ends
interspersed, you are likely not to get any results in D1. I think I know
what you want, but I won't mention that.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"deacs >" <<deacs.11dpp1@excelforum-nospam.com> wrote in message
news:deacs.11dpp1@excelforum-nospam.com...
> Bob,
>
> Thanks so much for the help! It worked like a charm. Now, the next step
> for me is to figure out the % change in column C from month to month
> without having to manually look to see if column C contains any data.
> So, if column C has data, I'd like Excel calculate the monthly change
> in account balance. I assume it would just be another if statement, but
> I have been unable to make it work out right to account for the
> different number of days in the months.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
2/9/2004 10:08:54 PM
Yes, the problem is that I have cells that are non month en
interspersed. And yes, I was requesting help, but no worries, I'll jus
try to work it out. Worst case, I just manually go down the column an
input the % change formula. Thanks again for your help

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

0
2/9/2004 10:41:03 PM
Don't be daft, what are computers for.

Try this.

In E2, not E1 note, put this array formula (that is confirm with
Ctrl-Shift-Enter, not just Enter)
=INDIRECT(CHAR(COLUMN($C$1)+64)&MAX(ROW($1:1)*(C$1:C1<>"")))
and copy down. Some of the initial rows might show #REF, don't worry

In D2, put this formula
=IF(AND(C2<>"",NOT(ISERROR(E2))),(C2-E2)/E2,"")
and copy down.

Column D should now just should percentage movements for the month end
dates, all others left blank, and pick up the previous month-end figure.


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"deacs >" <<deacs.11ds2d@excelforum-nospam.com> wrote in message
news:deacs.11ds2d@excelforum-nospam.com...
> Yes, the problem is that I have cells that are non month end
> interspersed. And yes, I was requesting help, but no worries, I'll just
> try to work it out. Worst case, I just manually go down the column and
> input the % change formula. Thanks again for your help.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
2/9/2004 11:32:27 PM
Reply:

Similar Artilces:

append multiple data every end of the month...for accrual pruposes
i have a table tblPayee/Payor...with fields PID, Rate, Name...there are about 200 PIDs, with their corrseponding rates...these rates are subject to change.. .. what i want to do is that every last day of the month, i would like to compute for the individual accrued balances ....id lyk to ask for possible ways on how to do this... i planned of appending multiple data on a table named tblFundAccruals w/ field names IndexNum, PID, Month and Rate whch i could not figure out how because of the large no. of PIDs... any help wud be appreciated ...thank u... -- ai® Message posted via AccessMon...

prior year payroll inactives
It is wonderful to keep prior year inactive employees in the database but it would be nice for reporting purposes if the employee was inactivated in a prior year and the report request is for the current year, that the prior year inactive employees be excluded. Version 8.0 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader an...

Month and Year calc
Hi all Could someone help me with a formula to do the following : I wish to enter a date in cell A1 in the format Jan-04 (mmm-yy) - that's the easy bit ! I then want excel to populate subsequent cells B1, C1.....etc with Feb-04, Mar-04.....etc. If I then change A1 to say Aug-04 I would like subsequent cells to follow the sequence Sep-04, Oct-04 etc. The worksheet will have to cover 5 years so obviously Jan-05 will have to follow Dec-04 and so on. TIA Cas Hi if you enter in cell A1 a date (and format it with your custom format MMMM-YY) try the following functuon in B1 =DATE(YEAR(A...

MONTH(a1)
I'VE ACHANGEABLE NUMBER IIN a1 AND I PUT IN b1 "=MONTH(A1) IN ORDER TO GIVE ME THE MONTH'S NAME WHEN I CHANGE THE NUMBER IN A1 BUT IT DDINT WORK WHY? PLZ HELP THANXXXXXXXXXXXXXXXX FOR AL -- frs ----------------------------------------------------------------------- frsm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3123 View this thread: http://www.excelforum.com/showthread.php?threadid=51453 I believe you have to have a date in A1, not just a number. HTH Regards, Howard "frsm" <frsm.23jpcy_1140468300.4445@excelforum-nospam.com...

How do I break a date range by month?
I am trying to calculated the days in a month for a 14 day pay period. What I want to do is to calculate how many days are in each month if the pay period crosses a month. For example the begining date is 8/21/05 and it ends 9/3/05. I was wondering if there is a formula that will allow me to calculated that there are 3 days in Sept and 11 in August. Thanks =DATE(YEAR(start),MONTH(start)+1,0)-start+1 and =end-DATE(YEAR(end),MONTH(end),0) -- HTH RP (remove nothere from the email address if mailing direct) "HeatherDawn" <HeatherDawn@discussions.microsoft.com> wrote...

IRR on monthly intervals
I am running a $30MM negative month 1, a $1MM positive for 46 mo. and then a $60MM positive in month 48. How do I calculate IRR over the 48 months? Duke Since your time periods are all the same and you cash flows are not all identical, the easiest way is to lay out the data in 48 rows and apply the IRR formula to the range. Note, that it will assume that period 1 is time =0, which for all practical purposes, it is. To annualize the monthly rate use (1+IRR(range))^12 PC "duke" <dkc@opon.com> wrote in message news:023f01c34d70$04b40b50$a501280a@phx.gbl... > I am ru...

Add one Month in the next cell for several months/years
I want to add a month in the next cell over for several years. EX: In cell A1 is Dec 2005. In cell B1 I want Jan 2006 and so on for several months/years. When you put in a formula of =A1+31, eventually it will put in the wrong month. I assume because of Leap Year. In A1, enter 01/12/2005 In B1, enter date(year(A1),month(A1)+1,1) and copy across -- HTH RP (remove nothere from the email address if mailing direct) "heater" <heater@discussions.microsoft.com> wrote in message news:7E74428F-CAF5-4227-A246-959A27A70072@microsoft.com... > I want to add a month in the ...

How do I send monthly automatic e-mails
I want to send out a monthly reminder letter to a group of people automatically on a given day each month. Can I do this in Outlook? "srburguy" <srburguy@discussions.microsoft.com> wrote in message news:F4517E50-E377-4E74-9ECD-22AA9ACF3AE9@microsoft.com... >I want to send out a monthly reminder letter to a group of people > automatically on a given day each month. Can I do this in Outlook? See this: http://www.slipstick.com/mail1/recurringmail.asp -- Brian Tillman [MVP-Outlook] ...

Posting to closed year #2
Hi there We need to make an adjustment to a closed year. We have opened the year\period... and the accounts allow posting to history. But when we post the transaction it errors out and states . Transaction error-edit required We cannot see where the transaction needs editing. I cannot seem to find reletive info on the KB either. Any one have a step by step to ensure we are not missing anything Thank you Tami Did you follow Article ID : 851423? The only thing I think that could be the problem is that you are trying to correct a transactions that goes beyong the first closed year. ...

Use of Month() within SumIF()
I have a formula problem related to the use of the function month(). My data spreadsheet contains multiple columns 2 of which are Date & Sales. I am trying to export to another sheet the sum of each months sales using the formula sumif(range,month(),range. The month() formula refers to a cell with an interger from 1-12 corresponding to the month I want total sales summed for i.e. sumif(range,month(3),range) for March. Myh answer always comes up 0. What am I doing wrong. I realize there are other ways of accomplishing this (pivot tables, additional columns etc.), however, th...

Savings balance error in Monthly Report
I don't ususally run the monthly reports, but I did this month and noticed that Money was reporting that I have $158,388.62 in my Savings Account (which it claims earns 5.5%). This is under the heading of "Invest in your Debt". It is of course advising me to use some of that to pay down my current debt. As much as I wish this balance were correct, it ain't. Anyone know where Money gets this number and interest rate? Only thing I can think of is that maybe it's including my House. ...

Year End Inventory
We are coming to our first year-end and have some concerns. It is our normal practice to markdown/ write off items at the end of the year and I haven't been able to find a place within GP 9.0 to do this. If anyone could point me in the right direction I would really appreciate it. Julie Hmmmm......well, Markdown usually means to lower the selling price so that people will buy you out of the item. This is accomplished by adjusting the selling price in the price list (Cards->Inventory->Items->Goto Menu->Pricelist) Now, to write off the slow movers, you first need to i...

Error message when I try to open the "Fiscal Year Setting"
Hi all! I have trouble described in: http://support.microsoft.com/default.aspx/kb/921394 but it no treated with query: update organizationbase set fiscalsettingsupdated=0 before updating I had fiscalsettingsupdated=1, it were successfully changed but Error message remain. help please! I have Russian version CRM3. ...

How do I get monthly data in the data area to remain in order
The data area is rearranging the months in alphabetical order. How can I change this? It sounds like your months might be entered as text. Try entering the months as dates and then formatting them in the "Format Cells" dialog box. For example, enter: 01/01/2007 02/01/2007 03/01/2007 Then go to the Format Cells dialog box and reformat the dates as you want them to look. -- John Mansfield "LauraFitzroy" wrote: > The data area is rearranging the months in alphabetical order. How can I > change this? Thanks John!! It worked. Also, before receiving yr...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

Is it possible to grab just the Mondays-Thursdays of every month?
I am creating a spreadsheet to keep track of dentist office productivity. In one box, I want to allow the user to enter the month and have the workdates change accordingly. For example, our office works Mondays through Thursdays. In 2005, the first Monday in January is the 3rd. How could I change the dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all the M-Th of the month are included. These dates should change if the user types in a different month. Thank you for your help. Arlen Arlen Check out the =WEEKDAY function in Excel. Alex "Arlen"...

Excel cut/paste prob: Adds one day and changes year to 2009
When I cut and paste a date into another worksheet, one day is added and the year is changed. For example, 2/5/05 will change to 2/6/09. I am using Office for Mac. How can I avoid this problem. It is compromising my reports and giving false information. The workbook you're copying from uses the WinXL default 1900 date system while your destination workbook is using the MacXL default 1904 date system. You can change the destination workbook system (Preferences/Calculation, uncheck the 1904 date system checkbox). Alternatively, you can enter 1462 in an empty cell. Copy the cell...

Change number to month.
I've got a bunch of numbers from 1 - 12 that represent months. How can I change all those numbers and instead of having a 1, have January or instead of a 9 have September?? Have a look at http://www.mrexcel.com/archive/Formulas/7514.html Success! -- met vriendelijke groetjes freddy.van.looy@telenet.be "GEM" <GEM@discussions.microsoft.com> schreef in bericht news:A5BF5F25-55B8-4460-ACC9-B0D4DC3ABFE4@microsoft.com... > I've got a bunch of numbers from 1 - 12 that represent months. How can I > change all those numbers and instead of having a 1, have January ...

Monthly Report
Dear Freinds I am new to this world but i hope that i could get some help I Have data listed in sheet called Data as follows: DateSales ItemSold Dtl1 Dtl2 Dtl3 T-Price Quantity 01.01.2010 Item1 60 6 01.01.2010 Item2 80 4 01.01.2010 Item3 90 2 02.01.2010 Item4 100 50 02.01.2010 Item5 150 6 09.01.2010 Item6 70 2 09.01.2010 Item7 ...

Products and Invoicing
I am just fishing for some ideas here - We sell teleconferences and webconferences and bill once a month. We would like to integrate this into CRM and the GP piece, but I am a bit confused on how to go forward. Is anyone else "selling" products this way? and if so, how did you set up your catalog, etc...? I am thinking of starting an order, then just leaving it open and adding the conferences as "products" - then generate the invoice. The problem with creating each conference as an "order" is that invoices seem to only come from one order - not multiple orde...

Year End closing
We are currently using payables, receivables, HR, Payroll, Project accounting, Financials and Fixed assets. Can some one lend me, the check list for all the above mentioned modules. Thanks, Ram If you log into CustomerSource you can download year-end processing guides for GP modules via the knowledgebase. -- Charles Allen, MVP "Ram" wrote: > We are currently using payables, receivables, HR, Payroll, Project > accounting, Financials and Fixed assets. > > Can some one lend me, the check list for all the above mentioned modules. > > Thanks, > > ...

Show yearly budged item in cash flow?
I am using Money 2005 Deluxe. I am working with a checking account that shows Montlhy budgeted items as well as scheduled withdrawals and expenses. This works well. However, items that are budgeted yearly for this checking account are not showing up in the cash flow. For example: Cash Flow shows the scheduled paycheck deposit, the scheduled transfers out of accounts, the monthly budgeted cash withdrawals at the ATM, the monthly budgeted automobile maintenance, etc. BUT, I have an item called Automobile: Tires that I have budgeted for $1000 YEARLY. Under categories I have made sure th...

Great Plains Fiscal Year Error Message
New year-end is now March 2011 (was December 2010). After doing research in GP?s help module on what to do to change over to the new fiscal year-end, I changed (in Fiscal Periods) the end date to March 31, 2011 giving us 15 fiscal periods instead of 12). Under Tools>Setup>Fixed Assets>Quarter I extended the 4th Quarter to March 31, 2011 (it was December 31, 2010 previously). The problem came when I went to integrate our first Work in Process batch (Tools>Routines>Fixed Assets>GL posting) of 2011. I entered the ending period date under ?Selection Range? as 2011-001 an...

months to full calender year
I have a monthly cash flow schedule that spans 4 calendar years. Each month has date representing the month (e.g. mar-2010). I need to distinguish between months that for which the whole calendar year appears on the schedule and those that are partial calender years. Any ideas how to do this? ...

Imported as YYYYMMDD but need Month Day Year
Example import: 20060202 Not sure if it's text or not but need to convert to real date format so that I can do date calculations. Try this: Select the single-column range of "dates" From the Excel main menu: <data><text-to-columns> Click [Next] Click [Next] Set the date pattern to: YMD (from the dropdown list in the upper right) Click [Finish] Does that help? *********** Regards, Ron XL2002, WinXP "beginnergirl" wrote: > Example import: > > 20060202 > > Not sure if it's text or not but need to convert to real date format so th...