Looking for a formula for travel expenses

I am trying to setup a formula to calculate milage reimbursement for staff.
Employees are paid the following:

  • 68¢ per kilometre for the first 5,000 kilometres driven
  • 62¢ per kilometre driven after that

I currently have a formula multiplying the entered distance x0.68 but cannot figure out a formula to calculate the total distance travelled for the year and then adjust the rate depending on that total.

Thank you for your help and time.

@TouriScope
Replace “Distance” with your column name

MIN(
    {Distance}-0,
    5000
)*0.68
+
MAX(
    {Distance}-5000,
    0
)*0.62
1 Like

Thank you so much for your help. Works like a charm but is the formula still based on the accumulated total of the column? Have a great day Francis.

1 Like

Could you please explain what you mean in a bit more detail or perhaps provide a screenshot?

Thanks for you quick reply Francis.
The rate is based on a yearly total so when 5000 km is reached, the rate changes to .62$/km. So 0-5000km = .68$/km and any distance after that would be charged 0.62$/km. Your formula works well if the single trip is more than 5000km not the acculumated total of the column.

The formula should work correctly if the total is lower than 5000km as well. Have you tested that? Can you share the results?

I’m not sure what you’re referring to when you say the “accumulated total”. Can you please share a screenshot?

Is it the case that the same employee is recording their mileage in different items? It’s hard for me to offer further help without seeing exactly what’s going on.

Since I am still formulating the board, the data is not entered completely.

I did do a test to see that if I enter an item of 5000 km the following entry which would be more that the yearly total of 5000km is still calculated at the initial rate of 0.68$/km.

Each travel expense will be entered as an individual item so idealy all of the distances would be added up together and when the limit of 5000km is reached then any other entry after the rate would adjust.

As you can see in the example, the rate after the entry of 5000 km should be at a rate of .62$ since the total for the colum is over the column total of 5000km.

Hope this makes sense.

Thanks

I see. Firstly, you need to remember that monday.com is not Excel! Each row represents that same data and cumulative calculations are only really for charts and visualisations. monday.com is a database!

Fortunately, there is a way to handle this.

  1. Store each “entry” as a subitem per staff member (parent item is the staff member—subitems are mileage entries)
  2. On the subitem mileage column, open up the options (click the three dots on the header) and select “show summary on parent item”. This will mirror the total value of the subitems in the parent item
  3. Then, you can apply the same formula to the parent item.

If using subitems and parent items in this way isn’t convenient, you can always have a separate board for staff members and then link each mileage entry to the staff member item on that other board. Then you can mirror the mileage column and set it to Show Summary as the “Sum” of those values. You can then use that sum in the same way as above.

Please feel to reach out if you want some more help structuring your monday.com solution.

1 Like

I completely understand your point that it is not excel and that there are limitations.

Your solution should work but I still need to figure out the board layout since 5000km is per employee per year.

Thanks again for your help Francis. I appreciate your time on this.

1 Like