Google-sheets – Balance on the FIRST day of the month, and the sum per month

google sheets

I'm importing CSV's of all of my transactions in the hopes that I can import a lot of these values into a larger cash flow sheet I've setup elsewhere.

One of the formulas I'm trying to figure out is for Google Sheets to tell me what the balance I have on the first day of each month that is listed on an adjacent drop-down list.

So I have two sets of tables in the following columns:

A,E,F (Date in the format of "MM-DD-YYYY", Transaction Amount, Running Balance): for all of my transactions for the year.
J,K (Month in the format of "MMMM", Balance on the first Day of the month). This list of Months from January-December is in 'J2:J'. The balance on the first day of the adjacent month from 'J2:J' should be listed in 'K2:K'.

For reference, I have the monthly date ranges setup on another tab via column A,B,C -> Month, First Day, Last Day. This was already setup to filter by month on the same sheet, so I'm assuming I could use this to also figure out what the first date of the month is. However, I cannot figure out how to pull the balance from the first day of the month.

Similarly, I'm also interested in figuring out what the sum of all of the transactions that have occurred during a specified month (E2:E) are as well.

Here is the sheet:
https://docs.google.com/spreadsheets/d/1aj44YJIrk6el_L0TexQeuLxYapvkXt2b1RbQBwPL13o/edit?usp=sharing

Best Answer

UNTESTED. Assuming the year is always 2015 something like the following in K2 copied down to K13 might suit:

=IF(J2="December",SUMIF(A:A,"<"&DATEVALUE("12/1/2015"),E:E),SUMIF(A:A,"<"&DATEVALUE(ROW()-1&"/1/2015"),E:E))

Related Solutions

Google-sheets – How to chart based on day and month using dates in Google Sheets

Basically what you're looking for is something like Pivot Tables of Excel, where you can group by anything you want.

Unfortunately there is no such support in Google Spreadsheet. What you (or someone) could do though is to write a simple script to group them for you. If you're interested you can start with some tutorials.

Google Sheets – How to Sort by Month and Day but Not Year

Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:

=TEXT(A2, "00")

I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.

Formula

=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - " 
 & TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)

Explained

Here's a break-down description of the formula:

The FILTER function will filter for all rows that have something.
The MONTH and DAY functions will extract the respective values from the date.
The TEXT function will convert the value from point 2 into a pre-formatted STRING.
The ARRAYFORMULA function will apply all the above to the complete column (skipping the header).
The SORT function will sort the result, given by the ARRAYFORMULA.

The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.

Screenshot

Example

See example file I've created: sorting dates as text