Balance History Tracker

Spreadsheets gSheets Templates
, ,
42

Hi! Using this template as a building block to a vision I have. In doing that, I ran across an issue where when sorting an account, the sorting only took the date into account, & not the time. I changed the formula in A13 to this to fix it so it sorts in chronological order including the time.

=filter(sort(iferror(sort(filter(sortn(sort(filter(H13:P,(H13:H>=L2),(I13:I<=L3),(if(isblank(D6),H13:H>1/1/1900,K13:K=D6)),(if(isblank(D5),H13:H>1/1/1900,L13:L=D5)),(if(isblank(B5),H13:H>1/1/1900,O13:O=B5)),(if(isblank(B6),H13:H>1/1/1900,N13:N=B6))),1,false),9^9,2,1,true,3,true,4,true),{1,1,0,1,1,0,1,1,1}),1,true,3,true,2,true),"Nothing returned based on filter conditions."),1,if(F5="Descending",FALSE,TRUE)),{0,1,1,1,1,1,1})
2 Likes
43

Thanks for that! I updated the template to version 1.2. @randy can you please publish to the Solutions and give credit?

Thanks!

44

I get this error when I try to install. Any suggestions?

CleanShot 2024-02-15 at 18.53.59

45

I saw your message and just tried it, @nspreitzer. The install worked for me.

Were you able to resolve this? Are you able to access the master here? Is there anything unusual about your environment? Corporate Google ccount?

46

Whoops! I forgot to respond. Sorry, @randy.

  • I was not able to resolve, no. I tried 3 times and gave up.
  • I am indeed able to access the master sheet you linked.
  • Nothing unusual about my account that I can think of. Personal account, not corporate, not G-Suite.
47

how did you add that chart ?

48

Trying out this BH Tracker - thanks! The Sort by Date feature appears to look at the month and day, but not the year. Copy of the Descending Date inserted here …,
image

Consequently, 2024 Balances don’t appear until you scroll down to June, where a descending filter should put 2024 on top of list. …
image

2 Likes
50

To follow on with the date, the time frame seems to be ignored and the sort sorts the dates in lexicographic order. I removed then reinstalled the sheet before updating this thread.

This month ?!??

Screenshot from 2024-07-24 10-33-39
Screenshot from 2024-07-24 10-33-39785×443 51.5 KB

Ascending sort

Screenshot from 2024-07-24 10-34-14
Screenshot from 2024-07-24 10-34-14785×443 49.6 KB

Custom date range:

Screenshot from 2024-07-24 10-45-26
Screenshot from 2024-07-24 10-45-26785×443 51.6 KB

Screenshot from 2024-07-24 10-45-37
Screenshot from 2024-07-24 10-45-37785×443 53.3 KB

2 Likes
51

I’ll have to take a look at it but as you say something seems odd.

1 Like
52

FYI, I’m seeing the same sorting behavior as @ramerkw

1 Like
53

Date Tiller Issue
Date Tiller Issue1219×447 30.5 KB

I’m seeing the same sorting behavior as well. I really wish I could get a solid graph of my balance history over time.

1 Like
54

For what it’s worth, I’ve gotten a lot closer to the desired results by forcing the DATEVALUE() within the formula in cell A13.

=filter(sort(iferror(sort(filter(SORTN(sort(filter(H13:P,(DATEVALUE(H13:H)>=L2),(DATEVALUE(I13:I)<=L3),(if(isblank(D6),DATEVALUE(H13:H)>1/1/1900,K13:K=D6)),(if(isblank(D5),DATEVALUE(H13:H)>1/1/1900,L13:L=D5)),(if(isblank(B5),DATEVALUE(H13:H)>1/1/1900,O13:O=B5)),(if(isblank(B6),DATEVALUE(H13:H)>1/1/1900,N13:N=B6))),1,false),9^9,2,1,true,3,true,4,true),{1,1,0,1,1,0,1,1,1}),1,true,3,true,2,true),“Nothing returned based on filter conditions.”),1,if(F5=“Descending”,FALSE,TRUE)),{0,1,1,1,1,1,1})

Graph
Graph1700×636 56.5 KB

55

@ryan.lehms can you elaborate? I’d love to get this to work. Also, are you creating the graph, or is it supposed to be built into this template (I can create easily enough of course, but wonder if my template is missing something).

56

Warning: copy/paste of the formula will give #ERROR because of the typographic quotes around the string literals. Just edit the formula to put in ordinary quote marks.

Also: The sort seems ok within the year, but multiple year range still unexpected result.

57

Use the Preformatted text forum message option when sharing formulas and then they can be copy/pasted without issue :slight_smile:
image

2 Likes
58

Yeah, it gets us closer to the desired result, but the multiple year range is still borked. I ultimately gave up and switched the graph into scatter mode and called it a day.

59

I think I may have resolved the sorting issue. It seems the issue lies in Column H13:H and the formula in A13. The formula in H13 and subsequent cells below are converting the date and time into a text string when concatenating and not into a true datetime value. So when you run the sort it is sorting alphabetically, not by date. I revised the code in H13 to the below and copied it down to all the cells below.

=IF(AND(ISNUMBER(I13), ISNUMBER(J13)), I13 + J13, "")

The formula in A13, I highlighted the change I made in the screen shot below. I changed that highlighted section from I13:I<=L3 to H13:H<=L3.
image

These two changes seems to have resolved the sort. Please test it out and let me know if this resolves this sorting issue for anyone else. Mine seems to work perfectly now.

2 Likes
60

Yes, it seems to have resolved the sorting issue! Although I used the revised code in H13 (not I13), along with the formula change in A13. Thank you!

1 Like
61

You are correct I updated my comment. I meant to say that the formula was in H 13. Glad it worked.

1 Like
62

I can’t figure out how to sort the dates. To me, it makes this spreadsheet unusable. Otherwise it seems so good to have that balance history! It’s all jumbled with the dates sorted by month.