Calculate 1st and last day of the month / Get the average amount for each day of the month?

[ChristelleCurci]

Hi everyone,

I have a base where I have a list of invoices which have a date an an amount. I need to know the average earnings for each working day of the month the invoice is billed.

To do so, I wanted to calculate the number of working days for each month of the invoices. I saw that there’s the WORKINGDAY DIFF formula, but to make it works I’ll need the first and last day of each invoice, as the screenshot below:

Here, for example, I entered manually the 1st and last, but is there a way to automate this?

With my manually entered 1st and last date of the month, I calculated the working days for the month.
Then, I created a formula which calculate the average amount billed each day from the amount of the bill (Formula is: Amount of the bill divided by the # of working days in the month).
Finally, I sum all the bills of the month to know the average amount billed by day for the whole month. See my screenshot below:

I don’t really know if I’m heading in the right direction for that and would love some help. Is there a simpler way to do this? Is there a way to know automatically the 1st and last days of the month? In a second time, I’ll also need to sum it up, not for a month, but for a quarter, and I have no clue to work it out yet.

Thank you!

[JonathanBowen]

ChristelleCurcio:

Is there a way to know automatically the 1st and last days of the month?

Hi @ChristelleCurcio - there’s a discussion here about getting the first and last days of the month automatically (or via a formula):

Formula to track beginning of month OR end of month date Ask the community

Hi @James_Mendaros - the formula is a bit complicated, but can be done: IF( Timestamp < DATETIME_FORMAT(Timestamp, 'YYYY-MM-16'), DATETIME_FORMAT(Timestamp, 'YYYY-MM-01') , DATETIME_FORMAT(DATEADD(DATEADD(DATETIME_FORMAT(Timestamp, 'YYYY-MM-01'), 1, 'month'), -1, 'days'), 'YYYY-MM-DD') ) A quick explanation: IF( Timestamp < DATETIME_FORMAT(Timestamp, 'YYYY-MM-16') If the timestamp is less than the 16th of the timestamp month… DATETIME_FORMAT(Timestamp, 'YYYY-MM-01') then return …

For quarters you can use a formula to format the date as a quarter:

DATETIME_FORMAT(Date, 'Q-YYYY')

[kuovonne · Accepted solution]

Here’s a pair of formulas.

The first day of the month has the same month and year as the given date, but the day is “1”.

DATETIME_PARSE(YEAR({Date}) & "-" & MONTH({Date}) & "-01", 'YYYY-MM-DD')

The last day of the month is one month later than the first day of the month, less one day. Note that this formula references the field with the previous formula:

DATEADD(DATEADD({First Day of Month}, 1, 'month'), -1, 'day')

The first and last days of the quarter are easier, because they always have the same month and day, and only the year varies:

The first day of the quarter:

SWITCH(
  DATETIME_FORMAT({Date}, "Q"),
  "1", DATETIME_PARSE(YEAR({Date}) & "-01-01", "YYYY-MM-DD"),
  "2", DATETIME_PARSE(YEAR({Date}) & "-04-01", "YYYY-MM-DD"),
  "3", DATETIME_PARSE(YEAR({Date}) & "-07-01", "YYYY-MM-DD"),
  "4", DATETIME_PARSE(YEAR({Date}) & "-10-01", "YYYY-MM-DD")
)

The last day of the quarter:

SWITCH(
  DATETIME_FORMAT({Date}, "Q"),
  "1", DATETIME_PARSE(YEAR({Date}) & "-03-31", "YYYY-MM-DD"),
  "2", DATETIME_PARSE(YEAR({Date}) & "-06-30", "YYYY-MM-DD"),
  "3", DATETIME_PARSE(YEAR({Date}) & "-09-30", "YYYY-MM-DD"),
  "4", DATETIME_PARSE(YEAR({Date}) & "-12-31", "YYYY-MM-DD")
)

---

Note: The formulas in @JonathanBowen’s link return strings, not date objects. Since you want to use the dates in further calculations, you want date objects, not strings. My formulas return date objects.

[ChristelleCurci]

Thank you both for answering.
@kuovonne the formulas you gave solved my problem. I was indeed looking for date fields and not strings since I need to use them for stats after.