[Solved] IF() time calculation frustration

[granitedeck]

Hi there.

My Quattro Pro finally died (it served me well, though crashed way too often) and I was recreating a spreadsheet to track employee time in OO. OO has a time format, so I figured out how to get net time. I'm having trouble figuring out the syntax for the overtime calculation. Here is what I used:

=IF(G7<=8:0;0:0; IF(G7>8:0;(G7-8:0)))

where G7 is net time, "8:0" is 8 hours...I get Err:502 in the box.

Thanks for your time and patience.

best regards,

Aaron London

[Villeroy]

Do you mean hours?
Use TIME(8;0;0) or TIME(0;8;0) if you mean minutes.
You can also use 1/3 for 8 hours or 1/180 for 8 minutes, which is a third of a day, since all times in a spreadsheet are fractions of days.

[TheGurkha]

Hi and welcome to the forums.

Try this: =IF(G7<="8:0:0";"0:0:0"; IF(G7>"8:0:0";(G7-"8:0:0")))

Note the " " around the times, and that times have 3 components, like 08:0:0 or 0:0:0 (not 8:0 and 0:0).

If this has answered your question please go to your first post and use the Edit button, and add [Solved] to the start of the title. You can use the green tick icon at the same time if you like.

[Dave]

I have to assume that you mean 8 hours for amount of overtime, not the time of day.

=IF(G7<=8;0; G7-8)

If not more than 8 hour overtime, zero overtime hours, otherwise subtract 8 from the total time [in G7] to calculate the number of overtime hours.

David.

[granitedeck]

Thank for the fast reply - still not working - so, here are the relevant values/fomula in the test line. I was using the HH:MM format. I tried changing the H7 formula to #:# and that didn't work. Switched it all to #:#:# - no change. Then I took out '-"8:0:0" from H7 to see if it would return the value G7, but, it still returns "O:0:0". Any idea?

(D7 - "start time") 06:00:00
(E7 - "end time") 18:00:00
(F7 - "lunch") 00:30:00
(G7 - "net) =E7-(D7+F7) [returns 11:30 as the value]
(H7 - "overtime") =IF(G7<="8:0:0";"0:0:0"; IF(G7>"8:0:0";(G7-"8:0:0"))) [returns 0:0:0 as value]

[TheGurkha]

OK, got it.

You need to use this monster:

Code: Select all

=IF(G7<=TIME(8;0;0);TIME(0;0;0); IF(G7>TIME(8;0;0);(G7-TIME(8;0;0))))

See the example sheet attached.

Overtime.ods

Overtime example

(7.43 KiB) Downloaded 450 times

If this has answered your question please go to your first post and use the Edit button, and add [Solved] to the start of the title. You can use the green tick icon at the same time if you like.

[Villeroy]

TIME(0;0;0) is equivalent to plain zero.

And IF is the most frequently misused function. It obfuscates everything.

=IF(G7<=TIME(8;0;0);TIME(0;0;0); IF(G7>TIME(8;0;0);(G7-TIME(8;0;0))))

can be reduced to:

Code: Select all

=IF(G7<=TIME(8;0;0);0;G7-TIME(8;0;0))

wich can be reduced to

Code: Select all

=MAX(0;G7-TIME(8;0;0))

or even shorter

Code: Select all

=MAX(0;G7-8/24)

[TheGurkha]

Stop - make it any smaller and it'll disappear

[granitedeck]

Thank you all very much - works precisely!

have a great weekend,

best,

Aaron

[johann-1,0]

=MAX(0;G7-1/3)

Now it's really reduced to its minimal expression, lol.

[johann-1,0]

Hello. Vileroy, when I use your formulas, it sends me an error message: #NAME.
Please, may anybody help me?

Openoffice 3x. Ubuntu 8.04 LTS

[FJCC]

Judging from your other post you are using the Spanish user interface. I that case the function is MÁX() with an accent.

[johann-1,0]

Judging from your other post you are using the Spanish user interface. I that case the function is MÁX() with an accent.

Thank you FJCC, the problem wasn't with the "MÁX()" function but with the "TIME()" function. In the user documentation it appears right. In fact, I posted there because of this post

[johann-1,0]

Hello again friends. I'm requesting for help again. I'm uploading the spreadsheet for you to see it easily. I will do an explanation of the document, but you must see it to understand (I think it).
Automated Calculations
Column B: Start hour
Column C: End hour
Column D: End hour - Start hour
Column E: The column D formatted as numbers
Column F: Adds 1 to column "D"
Column G: The above calculations made in one step, for example (for the second file) "=(C2<B2) + (C2-B2)".
Column H: Time(D2:D5), this is, the difference between the two hours expressed in serial numbers. In Spanish it is NSHORA(D2:D5). Gives error 511 (variables needed).

Manual Calculations(green cells)
Column I: Hours numbers manually calculated and divided by 24. For example, for 9:00 it is "=9/24"
Column J: Adds 1 to Column I.
Column K: Column J*24*60.

As you could see in the uploaded file, manual calculations are correct, but automated ones give wrong results (red cells) and this is because (although the documentation says the opposite) the number assigned to a specific hour is not between 0 and 1, and it can have values outside those limits. In that case, automated calculations are wrong because when 1 is added, it could be still outside the limits. Please, see the red cells.

I venture to suggest a possible solution (at least for me, because it seems that the only one that has that problem is me) is to do a repetitive cycle like (in pseudocode):

Code: Select all

 x=C2-B2
while x<0 do
            x=1 + x
end while

I'll appreciate if you could tell me how to implement that in a cell.
On the other hand, when I use the TIME function it tells that variables are needed (error 511). How can I fix that?

Estadisticadeestudio2.ods

(8.83 KiB) Downloaded 360 times

[Villeroy]

Have a look at http://user.services.openoffice.org/en/ ... 796#p40796 and the 2 attached fiels shift.ods (concept demo) and shift2.ods (more usable).

[johann-1,0]

Oooooooooooooooh, what a fast reply. Thank you. I will take a look on it.

[johann-1,0]

Code: Select all Expand viewCollapse view
x=C2-B2
while x<0 do
x=1 + x
end while



I'll appreciate if you could tell me how to implement that in a cell.

I would be excellent if you may tell me another way (shorter) to implement that.

[Villeroy]

You want to calculate a time span where the start time is greater than the end time?
Simply add 1 to the start time in order to shift the end time into the next day after the start time
duration =end-start+(start>end)

0|----e---------s-----|1-----e+1---------|2-----

0 formatted as point in time 1899-12-30 00:00, one is 1899-12-30 00:00
Let e be 0.25 (a quarter of a day, formatted 1899-12-30 06:00)
Let s be 0.75 (3 quarters of a day, formatted 1899-12-30 18:00)
(s>e) is 1 (formatted TRUE),
e+1-s = 0.25+1-0.75 = 0.5 (half a day, formatted 12:00)
From unit "day" to unit "hours": 0.5*24 gives 12 hours (formatted as plain decimal number)

[johann-1,0]

You want to calculate a time span where the start time is greater than the end time?
Simply add 1 to the start date.
duration =end-start+(start>end)

0|----e---------s-----|1-----e+1---------|2-----

0 formatted as point in time 1899-12-30 00:00, one is 1899-12-30 00:00
Let e be 0.25 (a quarter of a day, formatted 1899-12-30 06:00)
Let s be 0.75 (3 quarters of a day, formatted 1899-12-30 18:00)
(s>e) is 1 (formatted TRUE),
e+1-s = 0.25+1-0.75 = 0.5 (half a day, formatted 12:00)
From unit "day" to unit "hours": 0.5*24 gives 12 hours (formatted as plain decimal number)

In fact, I want to calculate that difference between any hours, but when I use the formula "=End hour - Start hour + (End hour < Start hour )" sometimes it gives numbers under "-1" that can't be used to do calculations (like the first red cell). What I wanted to do is to design an algorithm to add 1 to the formula until it has a positive value that we can use to calculate positives values of time. I will keep looking for a less consuming algorithm.
Thanks a lot

[Villeroy]

Get the true values of your input values (remove formatting) in order to find out why you still get negative values. If both values are between 0 and 1 (0:00 and 23:59:59.999) this can not happen.
As you can see in the first 4 or 5 columns of my files, this calculation works well within the limit of 24 hours. In the second file I added some validation to take care of this.

[johann-1,0]

I don't understand but i repeated the exercise in a new spreadsheet and the formula "C2-B2+(C2<B2)" worked fine :S
What is still not working is the "NSHORA" function. It keeps saying "error 511".

[Villeroy]

In your Estadisticadeestudio2.ods I remove all formatting (Ctrl+A Format>Default) and get the following:

Code: Select all

Hora inicial	Hora Final
0,85	0,23
1,85	0,29
0,83	0,29
0,71	0,25

One of the values is greater than 1. The bug is in your data (which is the normal case in all spreadsheets).

[johann-1,0]

Yes Villeroy, you are right. I tried and it results as you said...but the problem persists in F3. That cell should be positive.

[Villeroy]

After changing the value from 44:30 to 20:30 I get 0.56 in F3 which is the correct result.
TIME(one_number) returns Err:511 because TIME calculates one number from 3 other numbers: TIME(hh ; mm ; ss) which is not required here. TIME(hh;mm;ss) is the same as hh/24 + mm/1440 + ss/86400

[johann-1,0]

After changing the value from 44:30 to 20:30 I get 0.56 in F3 which is the correct result.
TIME(one_number) returns Err:511 because TIME calculates one number from 3 other numbers: TIME(hh ; mm ; ss) which is not required here. TIME(hh;mm;ss) is the same as hh/24 + mm/1440 + ss/86400

I thought that, but I supposed that Calc could see the three parameters, because in the columns appear the hour with those three parameters, i.e. the hour, the minutes and seconds.

[Villeroy]

You mean a spreadsheet with artificial intelligence built-in?

[johann-1,0]

You mean a spreadsheet with artificial intelligence built-in?

Hahahaha, too funny.
Of course, I don't expect it to have artificial intelligence, and I'm not complaining here, but I thought it would recognize the hours, minutes and seconds when a cell has, for example, data like "20:20:00".
Thank you again, Villeroy.