count the same days

[pascalg]

Hi, i hope i've come to the right place with my question.

I'm currently analysing movements that are logged in an excel sheet of circa 75k rows. There is information about the date, time, duration, location from and to and some information about the serial number etc.
Sometimes a car will drive 1 or 2 rides a day, sometimes 15 drives a day. I would like to generate a ride-number count.

For example.
Car A drives 10 times at 16-Sept. then i would like it to count it like this:

16/9 1. 10:00 - 11:00 (1h)
16/9 2. 11:10-11:30 (0.2)
16/9 3 11:40-12:00 (0.2).

The time and duration is already in the spreadsheet. I just need to add an colom that counts the days, and when its another date starts again. (so 17/9 will start with 1).

Is this at all possible/ do-able?

Kind regards,
Pascal

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

If you have Excel 2016, please update your forum profile to clarify this.

[pascalg]

included an example of the sheet

[AliGW]

2022 v16 - what is that?

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Please add a few rows of expected results manually to your sample workbook, as requested above.

[pascalg]

Version of MS office 16.64 (mac). - changed in my profile.

[AliGW]

You have not added to the workbook, though. Show some expected results, please.

[oeldere]

With a table of your data.

insert => table.


With a pivot table of your data.

insert => pivot table.



See the attached file.

[ByteMarks]

In column B add the formula

Please Login or Register  to view this content.

, format as General and fill down

Alternatively

Please Login or Register  to view this content.

will keep the numbering sequential regardless of sort.