Skip to main content

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!



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







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





DATETIME_FORMAT(Date, 'Q-YYYY')

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.

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.

Reply


Terms & Conditions