Problem with cell format

1

Hello,

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.

Thank you for your help,

Karine

2

Hi Karine,

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.

image
image.png850×176 8.13 KB

3

Thank you so much. It works.

And my other problem with selected column is resolved thanks to the update of easymorph I made this morning.

4

Hi Dmitry,

I just found a problem with the expression above. I have some cells which duration is 24:00. In this case, the cell is empty. How can I have 24 in number ?

Thank you for your help,

Karine

5

Hi Karine,

24:00 can’t be parsed by the date() function. In such cases expression returns an error.

You can handle this case by adding if function to the expression:

if([Column] = "24:00", 24, (date([Column], 'HH:mm') - today()) * 24)

6

You can also calculate the number of hours using the expression below:

(asnumber(left([Column], 2)) * 60 + asnumber(right([Column], 2))) / 60

To calculate the number of minutes, use the following expression:

asnumber(left([Column], 2)) * 60 + asnumber(right([Column], 2))

These expressions will handle values up to 99 hours 99 minutes.

7

Hi Andrew,

Thank you so much. It works perfectly.

Have a nice day.

Karine