Tuesday, November 26, 2013

yogi_Extract Deposit No1 Deposit No2 And Deposit No3 (if applicable) For The Month And Year Of Specified Date

                                          Google Spreadsheet   Post  #1434
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
question by treesloth (http://productforums.google.com/forum/?zx=jqpgnmfd9ysa#!mydiscussions/docs/gl4_Ca-ZoaA)
Finding the nth cell with a date in a certain month
Hi.  I'm working on a budget spreadsheet.  I get paid every 2 weeks.  I've come up with a listing of all of the days in the next year that I should expect to be paid.  Here's the first bit of it:

MonthDate of depositAmount of deposit
1111/15/2013$1,000,000,000.00
1111/29/2013$1,000,000,000.00
1212/13/2013$1,000,000,000.00
1212/27/2013$1,000,000,000.00
11/10/2014$1,000,000,000.00
11/24/2014$1,000,000,000.00
22/7/2014$1,000,000,000.00
22/21/2014$1,000,000,000.00
33/7/2014$1,000,000,000.00
33/21/2014$1,000,000,000.00
44/4/2014$1,000,000,000.00

As you can see, we're pinching our pennies. :-)  Now, on another sheet, I'd like to set up three cells.  The first one is the date of the first deposit in a month.  The second is the date of the second deposit.  The third is the date of the (infrequent) third deposit of the month.  Like this:

Current Month:12/1/2013
Carryover
First Deposit of Month12/13/2013
Second Deposit of Month12/27/2013
Third Deposit of MonthN/A

I'd like the dates in the right column to autofill from the listing of paydates above.  I'm really coming up with a blank on how to do that, though.  It seems like it would be something like, "find the first entry with a month and year matching the month and year in the current month" and then again for the second entry and the third.  But how?  Any suggestions on an approach to this?  Many thanks in advance.
---
hanks for the reply.  The sheet can be seen at:


I've added notes to the cells that, I hope, explain what I'd like in each cell.  Thanks again for taking the time.
-----------------------------------------------------------------------------------------------------------------------------

No comments:

Post a Comment