[Solved] Reset value to zero on given day of month

Discuss the spreadsheet application
Post Reply
mousecorns
Posts: 3
Joined: Sun Sep 05, 2021 10:29 am

[Solved] Reset value to zero on given day of month

Post by mousecorns »

Hello all

I am trying to set up a spreadsheet with a column calculating monthly income, the monthly total being shown on the same day of each month and reset to zero after that date. This would be easy enough using the DAY function if there were an entry for every day of the month but the problem is, there is not necessarily an entry for that specific day of every month. Any ideas on how to solve this?

Thanks.
Last edited by MrProgrammer on Thu Sep 30, 2021 10:04 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open Office 4. 1. 3.
macOS Catalina version 10.15.7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Resetting value to zero on given day of month

Post by Villeroy »

1000 records in random order aggregated by a pivot table (aka data pilot):
download/file.php?id=38070
OpenOffice Wiki wrote:Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions, use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mousecorns
Posts: 3
Joined: Sun Sep 05, 2021 10:29 am

Re: Resetting value to zero on given day of month

Post by mousecorns »

Thanks, Villeroy. Actually, I think I've managed to draw on my reserves of logic to come up with a function that works:

E5=IF(AND(DAY(A5)>2;(OR(DAY(A4)<3;MONTH(A5)>MONTH(A4))));B5;SUM(B5;E4))

So, where column A is the date and column B is the amount earned on that date, column E calculates a running total up to and including the 2nd of the month (or the last date before that), and starts calculating a new monthly total from the 3rd of the month (or the first date after that). If you can spot any holes in the formula, please let me know. ;-)
Open Office 4. 1. 3.
macOS Catalina version 10.15.7
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Resetting value to zero on given day of month

Post by MrProgrammer »

mousecorns wrote:E5=IF(AND(DAY(A5)>2;(OR(DAY(A4)<3;MONTH(A5)>MONTH(A4))));B5;SUM(B5;E4))
This can fail at the end of the year since MONTH("2022-01-04")<MONTH("2021-12-30") and the formula does not begin a new total.
mousecorns wrote:So, where column A is the date and column B is the amount earned on that date, column E calculates a running total up to and including the 2nd of the month (or the last date before that), and starts calculating a new monthly total from the 3rd of the month (or the first date after that).
Another way to arrange the calculation using the EOMONTH function:
202109101154.ods
(17.18 KiB) Downloaded 91 times
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Calc date formulas
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
mousecorns
Posts: 3
Joined: Sun Sep 05, 2021 10:29 am

Re: Resetting value to zero on given day of month

Post by mousecorns »

MrProgrammer wrote:
mousecorns wrote:This can fail at the end of the year since MONTH("2022-01-04")<MONTH("2021-12-30") and the formula does not begin a new total.
Thank you for pointing that out! I forgot about the new year...

EOMONTH is new to me. I'll have to look it up.
Open Office 4. 1. 3.
macOS Catalina version 10.15.7
Post Reply