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).
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
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-03-01 13:24:08 +0200
Seen: 181 times
Last updated: Mar 02 '12
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
Please mark the accepted answer as such, don't insert "[RESOLVED]" in your question.
MagicFab ( 2012-03-02 18:16:18 +0200 )edit