Calc: Timecondition - automatic hour-calculation from changing 24hr-clock (ex. 16-9 from which 16-24 =8 and 00-9 =9), with additional 2-4 changing conditions of which one is the date-cell

English
,
#1

Update: Added *.ods-file. (12:49 GMT+2) Hope that clarify the steps. Text below left untouched. -G-

Hi,

.Calc.ods
.
Ok. I am not allowed yet to upload pictures. That made it a lot difficult to explain. How do I get minimum three points? Will get back to this after that, but if you are able to understand even part of my problem I am most grateful for a reply.

First. In reference to this: Calc: What is the correct function for automated sum calculation when multiple IF- & one then- factor is necessary?

In that I had a few conditions which got in Pivot; Va/ak and A/L/P. In addition to that I would like to have some automatic calculations, which are

  • timecondition from a 24h-scale 0-24. I hope I am able to explain it in short below. A small picture with comments is also added below. (pic added later -G-)

  • minute to hundreds conversion. Also hopefully got it with example below explained.

  • from these conditions, a formula with which to add sums in to right cells, check pic which has the cells for example (pic added later -G-)

What would you recommend to be the best practise for this?

Table has two parts

  1. filling the numbers, which need some more automation, → questions below.

  2. a pivot to which the sums are added accordign to condition. I also need to find out how to wisely add more conditions and organise them in to a pivot, but that hopefully is in the documentation?

Example explanations of what is mentioned above:

  • List item

Timecondition (automatic hourcalculation)

In the sense like; if timeframe is 16 – 9 then it is two sets; 16-24 and 00-9. First equals 8 and second equals 9.
This part is in pic: 8 goes to first set of Va/Ak, and 9 goes to second set of Va/Ak. (pic added later -G-)

Example timeframes:
11-00

17-08 (sets 17-24 & 00-8)

12-12 (sets 12-24 & 00-12)

11-07 (sets 11-24 & 00-07)

Depending on the sum of these the set-sums are used for calculating resting time from fullset-time (would be in pic).

  • List item

Automatic: minute-conversion (from minutes to hundreds)

ex. One hour = 60 = full 100, 2 minute = 2 = 0,03 and 45 min = 0,75, 15 min = 0,25 etc…"

Thanks in advance.

#2

You should have enough karma points now.
Please supply - if possible at all - reduced but functional examples in odf formats. An explaining image may, of course, help, too.

#3

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.

#4

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…

#5

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.

#6

Can you send me the four parts? I remove the jpg for now.

#7

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”.

#8

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”.

#9

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.

#10

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

#11

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.

#12

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

#13

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.

#14

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.

#15

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

#16

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?

#17

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”.

#18

Attach an example!

#19

*.ods-file uploaded. Hope that helps. Apology for little delay in it.
Thanks!

#20

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.