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.
[Solved] Reset value to zero on given day of month
-
- Posts: 3
- Joined: Sun Sep 05, 2021 10:29 am
[Solved] Reset value to zero on given day of month
Last edited by MrProgrammer on Thu Sep 30, 2021 10:04 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
Open Office 4. 1. 3.
macOS Catalina version 10.15.7
macOS Catalina version 10.15.7
Re: Resetting value to zero on given day of month
1000 records in random order aggregated by a pivot table (aka data pilot):
download/file.php?id=38070
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 3
- Joined: Sun Sep 05, 2021 10:29 am
Re: Resetting value to zero on given day of month
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.
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
macOS Catalina version 10.15.7
- 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
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:E5=IF(AND(DAY(A5)>2;(OR(DAY(A4)<3;MONTH(A5)>MONTH(A4))));B5;SUM(B5;E4))
Another way to arrange the calculation using the EOMONTH function: 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.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).
[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).
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).
-
- Posts: 3
- Joined: Sun Sep 05, 2021 10:29 am
Re: Resetting value to zero on given day of month
Thank you for pointing that out! I forgot about the new year...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.
EOMONTH is new to me. I'll have to look it up.
Open Office 4. 1. 3.
macOS Catalina version 10.15.7
macOS Catalina version 10.15.7