Need help to create a Excel sheet for calculating average annual mileage

Hi!

I work with insurance, and during customer meetings, I need to calculate the average number of kilometers customers drive per year with each of the car they own.
I'm looking for help to create an Excel sheet that can do this easily.

The data I receive from customers includes:

1. The last recorded mileage (e.g., 07.06.2022 = 104,687 kilometers).
2. The mileage recorded on the current date (e.g., 29.08.2024 = 204,561 kilometers).

I need a sheet that can calculate:

- The number of months between the two dates.
- The average number of kilometers driven per month(or per day if that gives better data)
- The average number of kilometers driven per year.

The information I enter in the cells should be easily replaceable for each new meeting.

I've made a basic example of what I'm trying to achieve.
Feel free to do it the way you would do it, I'm sure ut can be done a better way than my examples.

Any help with this would be greatly appreciated.

Did you mean to post in the VBA section? Seems like a formula request to me. I can move the thread if necessary.

How do you want to define a month? 30 days? Or something else?

B6 contains text, not a date (but can be converted).

There are 26 months between the two dates - how do you arrive at 24.8?

Number of months:

=ROUND(DATEDIF(B5,SUBSTITUTE(B6,".","/")+0,"m")+((DATEDIF(B5,SUBSTITUTE(B6,".","/")+0,"md")/30)),1)

Total distance:

=C6-C5

Average per momth:

=ROUND(C13/C12,2)

Average per year:

=C14*12

See the attached for your locale. You might need to change m and md if Norwegian words for month and day start with different letters.

OK - see post #5.

It's 26.73, actually, which rounds to 26.7.

Total distance for car 2:

=LET(s,SUBSTITUTE(C10:C11," km",""),TAKE(s,-1)-TAKE(s,1))

The only thing I would change would be the distance input (DON'T add " km" - just enter a number) and the date that isn't a date - just enter a date properly.

I would not have done this without a workbook to see where you were going with it, so no, I won't recreate the wheel for you, sorry.

Off into a sunny garden now to dig out some roots.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

You, too.