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

Ask Your Question

How can I display two cells contents in another cell, both in hour format ? [closed]

asked 2012-03-01 13:24:08 +0200

bosyber gravatar image

updated 2020-08-07 23:13:32 +0200

Alex Kemp gravatar image

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 flag offensive 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


Please mark the accepted answer as such, don't insert "[RESOLVED]" in your question.

MagicFab gravatar imageMagicFab ( 2012-03-02 18:16:18 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2012-03-02 09:30:26 +0200

jofilho gravatar image

updated 2012-03-02 18:18:14 +0200

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

edit flag offensive delete link more



That is odd. Or embarrassing? Before I couldn't get that to work without #502 errors. But now it does indeed work.

bosyber gravatar imagebosyber ( 2012-03-02 18:06:45 +0200 )edit

Question Tools


Asked: 2012-03-01 13:24:08 +0200

Seen: 864 times

Last updated: Mar 02 '12