We will be migrating from Ask to Discourse on the first week of August, read the details here

# How can I display two cells contents in another cell, both in hour format ? [closed] I know how to show (concatenate) the result of two cells. However, if the contents of the cells is in date-time format this seems harder.

I have a list of how many hours I worked on something per week over a period: 8 6 16 18 12 24 36 24 12. I want do display the min, and max. amount of hours I did (so far easy). I wish to show that in a single cell: "6-36" (with the unit for that column the absolute number of hours).

I have only been able to get the results shown as fraction of days, ie. "0.25-1.5".

I tried converting the numbers with the TEXT function, but that gives an error. Seemingly it won't work with date-time because those aren't simple numbers. I haven't found a DATETIME function that would return the right number.

Note that one issue in this is wanting absolute hours ([HH]), which I then use as a normal number, but wanting to keep them as a time-value so I can enter fractions of an hour in minutes (and not wanting to wrap after 24 h, hence [HH], not HH as a unit).

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-15 06:07:28.336928

Sort by » oldest newest most voted  Use these functions VAL( MAX(A1:A10) * 24), where A1:A10 is only a example. You can concatenate these functions and build the format that you want. Like:

= VAL( MAX(A1:A10) * 24) & " - " & VAL( MIN(A1:A10) * 24).

The VALUE() function converts text values in numbers. Looking again, I see that it isn't necessary in this case. You can use simply the following formula:

= MAX(A1:A10) * 24 & " - " & MIN(A1:A10) * 24

more