Trying to calculate tariff amount based on start and end datetime stamp

[excel1504]

Hi 

I am trying to calculate number of hours a user should be charged based on their session start datetime and end datetime. Each product has a tariff band based on start and end time example below:

The below user has used the product from 02/10/22 9:30pm to 08/10/2022 9:00am. The tariff for this product is 10am to 11am it is £1, however this can vary on the day they are using the product. This user has used the product for up to 5 days so I am trying to calculate the cost per day and total cost. Please could you advise?

Tariff Tab 1

User Product Usage Cost Sheet

[#2 · Accepted solution]

@excel1504 

Hi, I have worked out a solution for you. I hope it fits like this.

I had to change your tariff table a bit. It now looks like this. Please only enter the start date when a new tariff starts. This has to happen for the whole week in one table.

If you need a new start date for a tariff, please calculate it as follows and enter it in column C. Saturday midnight = 0.0; Sunday 1, Monday 2 etc. Then divide the time in 24h format by 60 and add it to the day as a decimal place.

An example Thursday 3:15 pm = 5 + (15.25/24) = 5.63541666666667

To check, your input is converted and displayed in columns A and B. You can then enter your input in columns G-J.

Then you can make your entries for start and end in columns G-J. In column N, the tariffs per minute are added together.

Please check critically if the calculation fits for you. Of course, I cannot guarantee for the correctness.