[Solved] Calculate the hours between two dates

[XsiSec]

Hi Folks I have read a lots of topics about how to calculate hours between two dates, couldnt find anything that matched my requirements for 24 hours.

I need to calculate the amount of hours for each cell the red boxes is the answer.
Imagine
Days=Row1
W10=Row2





then comes result Row=3

Could someone help me with a formula?

Thank you

Thank you

[RusselB]

Welcome to the Forums.
The main problem you are having, is the fact that your entry for row W10 (which isn't a proper row number) contains a string that appears as 08:30-17:30
While math can be done with this, it is a lot more complicated.
It would be a lot easier to work with if you put your start time into one cell and your end time into another.
Then you just need to do a formula like End_Time - Start_Time

[Zizi64]

The date, time and the combined date-time values most be numerical types - if you want to use them in some calculations. I suggest you to use full date-time values fotr the Start time and for the End time (for example they can be located in two rows).
Then you will able to calculate the elapsed time value even between two days:

Row for Start values: 2020-11-05 23:00:00
Row for End values : 2020-11-06 01:00:00

formatted numerical result: End - Start = 2:00:00

[XsiSec]

How could an example look if I had start and end time as well?

[Zizi64]

Here is a sample file with Date_Time and DateTime values.

Date_Time_DateTime.ods

(10.23 KiB) Downloaded 131 times

Check the applied custom cell styles.

[Villeroy]

I have attached a more extensive example.
Columns A through C take a date, start time, end time as numbers. I filled in 1000 random records from January 2017 until recently in random order. For the end result it is not relevant in which order or rows or columns are entered.
-----------------------------------------------------------------------------------------------
Column D calculates the duration. If the end time is earlier than the start time, one day is added to the negative results, so 20:00 - 3:00 gives 7 hours (-17 +24 hours)
Column E calculates the year from the date column.
Column F calculates the week number from the date column.
Column G calculates the week day name from the date column.
Column H calculates the week day number from the date column.
Once you have entered a numeric date into a cell, the program can calculate everything about that date. No need to enter week numbers or anything like that.
-----------------------------------------------------------------------------------------------
From the calculated columns I generated a pivot table which calculates the durations for every year, every week and for every week day too.