Much appreciated. Thank you Lupp. The odf I can give if it seem to be necessary. I first add the picture. Let’s see if that is enough.
Sorry, I am too silly to learn something from your image. (There were announced at least 4 images, by the way.) I finally managed at least to get your question in a somehow readable form (one page). Seems to be in conflict with my ways of thinking. Might you be able to attach a (reduced) example not containing any confidential information, I can work with (.ods)? Please mark cells/ranges containing expected results using a specific format…
That is odd. I added one *.jpg file. Which kind of picture-format is preferred here?
I can do that later so will get back to then. Thank you for taking the time and effort in trying to understand.
Can you send me the four parts? I remove the jpg for now.
Seems there was a misunderstanding. I don’t worry about the image (picture) format. I simply cannot get the needed information out of it. Can’t you supply a reduced Calc document containing a selection of your data and of the related expected/wanted results? This would by far be better for our understanding than an image.And it would much “help to help”.
Yes I can do that - but there will be only the selection of the existing numbers. The related expected/wanted results I can only add as comments as I have in the *.jpg. I can do that tomorrow. I agree with you that it “help to help”.
You may calculate/estimate the expected results by mind or by pocket calculator. A little bit of additional comment may also help. But we have to be aware of a possibly different usage of some terms and linguistic constructs. Being a German I’m not really familiar with English.
I will have to think through based on the second answer given by Horst. As for the expected results - they were in the picture. It is in the picture in cells G14 = minutes, cells D13 and D14 have the endresults, which now are calculated manually, based on the first set of time (17-24 midnight). The second set of time is unused in the example and is going to be used for rows 15 and 16.
I will add these again in odf after redo
Ich verstehen ein Bisssen Deutch, aber “it is years ago”.Entchuligung
Only a first part, maybe.
How shall I read “ex. One hour = 60 = full 100, 2 minute = 2 = 0,03 and 45 min = 0,75, 15 min = 0,25 etc…” ? Try to be more precise and more concise as well, please.
An answer based on a guess: If you enter a time into a cell recognising it as a time value it will be represented as a fraction of one full day (>1 for more than 24 hours). This is independent of the format in which it is displayed. Multiplying such a value by 24 will result in a number representing the same time value as a fraction of one full hour. This will be another number, of course. Again multiplying, now by 100, will result in the number of hundredths of an hour for the same time interval …
If you have entered a text representing a time interval (“00:45:00” e.g. for 45 minutes) you will have to convert it first into a numerical value at all before you can use it in calculations …
I’m afraid, answers can hardly be concise if the questions are not actually precise. Working on an example and explaining some suggestions is much more efficient in most cases.
1/3
I try to correct a little first here. If it by this become more understandable then I edit the original query.
this: ex. One hour = 60 = full 100, 2 minute = 2 = 0,03 and 45 min = 0,75, 15 min = 0,25
The main thinking is 1) to have a base function that convert the time in to correct format
→ done by dividing the done minutes, with the total number of minutes possible,
ex.
2mins → 2/60 (60 is the total number of minutes possible in this one) = 0,03
73 mins → 73/60 = 1,22
Why like this? Because if you write 1hr 2mins as 1,02 the minute-calculations will go wrong in the end. (if I am wrong in this then I go wrong all the way, but this is how I remember that time-conversion was done when counting full workinghours from a working-schedule. (I’m not a mathematician…) Did this open it? So from this.
Main thinking is that I want the minutes typed in one cell to be converted according to that simple function in to another cell. I guess this answered that part itself.
3/3
But… the next part is that then I want this converted sum to be subtracted from the changing value, which is mentioned in the *.jpg as 1st part of set and 2nd part of set.
I need to think some more now. Get back to you.
We won’t approach a solution this way.
“1) to have a base function that convert the time in to correct format …”
What is a “base function”? The “correct format”? How was “the time” entered or returned by another calculation? What is the data type ( basically text OR number) in which it is represented before the intended conversion?
We will not need a long misinterpretative conversation between two users of English both not beeing native speakers if you supply the requested ods
So where I in actual am stuck is that 1st and 2nd set regarding the hours. How do Iget a sum of numeric hour from the time ex. 17-11 which is 7 hours? Can you open up a little bit more about the 24 hour?
Actually nobody should write 1h 2 min as 1.02 which will, depending on the locale, be “recognised” as 1 + 2/100 or first of January or something numerical else - or just taken as plain text “1.02”.
Attach an example!
*.ods-file uploaded. Hope that helps. Apology for little delay in it.
Thanks!
To your first half (Time Sets):
It would be much easier when you can split the entry 16-8 into to cells e.g B1=16 and C1=8. Then your first set cell is =24-B1 and your second set cell is =C1.
If you have to stick with text you have to use formulas. Text is in cell A1.
Set1: =24-LEFT(A1,FIND("-",A1)-1)
Set2: =0-MID(A1,FIND("-",A1)+1,10)
EDIT:
If I understand your ODS file correctly you need only 2 little formulas in col E.
Time entry in col I (16:00:00) & J (7:30:00) or any time you want.
Case1 time to midnight: col E6: =(1-I6)*24. Gives you 8.
Case2 time from Midnight: col E7: =J7*24. Gives you 7.5.
If you enter a time into Calc it converts it into a serial number 0.00 = Midnight, 0.5 = midday, 1.00 = next day midnight. If you multiply this number with 24 you get hours with decimal minutes.
The “0-” in the second formula converts the text to a number.
Thank you Horst for this suggestion. Are you too able to check the ods? It doesn’t matter whether it is numerical or text - the simpler and easier manageable the better. I really don’t know how to best make it. I would appreciate it, if you can evaluate what you told before again against the uploaded *.ods-file.