I have a column with information like “05:15” which is the duration (hour/minute) but in excel the format is not number and I can’t add these durations to have a sum. So, I want to convert these cells into numbers so as to work these data. My goal is to sum the number of working hours per person.
you can convert a duration like “05:15” into a number of hours by using this expression:
(date([Column], 'HH:mm') - today()) * 24
In this expression, the date function parses give text value and creates a date. When no year, month, day specified but only hours and minutes, the function returns a time in the current date. Therefore we need to subtract today’s date to obtain only time. The unit of measure for time is day, so 1 = 1 day. Therefore in order to obtain hours, we need to multiply it by 24.
One you obtain hours, you can sum them up as regular numbers.