Formular format help??

Open office uses the semicolon as the argument seprator (in the US at least) and Numbers uses the comma. That's the only thing I think you should change

I go it to work:

C4=IF((((F3-C3)-(E3+D3))*24)>8,8,((F3-C3)-(E3+D3))*24)

C5=IF(((F3-C3)-(E3+D3))*24>8,((F3-C3)-E3+D3)*24-8,0)

The formula for C5 had an extra close parenthesis. Bolded text is where extra ")" was.... or there was a missing "("

C5=IF(((F3-C3)-(E3+D3))*24>8,((F3-C3)-(E3+D3))*24-8,0)

Do you have any other questions?

Paul,

Numbers treats Dates and Times differently than Excel and sometimes you need to alter the expressions to account for this.

Here's what I did with your example to put it into a Numbers format:

I used a table with one Header Row, one Footer Row and one Header Column. All the body cells are formatted as Duration in the HH:MM format, as are the totals in the Footer Row.

Here are the equations:

Straight Hours:

=IFERROR(MIN(E-B-(C+D), "8h"), "")

Overtime Hours:

=IFERROR(E-B-(C+D)-F, "")

Total:

=IFERROR(SUM(F2+G2), "")

Fill these equations down to include all the Body rows.

The summary Total for Straight Hours is:

=SUM(F)

Fill Across to extend this formula to OT and Total.

Your example didn't give me any clues as to how you wanted to treat the weekend with respect to OT.

Regards,

Jerry

My solution should work on the iOS version, but I can't say for sure since I don't have one.

Tip: to enter 8:00 am in this system, you can just type "8h" and it will display as "8:00". or to enter a 45 minute break you can type "45m" and it will display as "0:45".

One more thing 🙂.

I suggest that you begin with a new document. Please don't try to edit your XLS import document. It's got too much (bad) history. Begin with a small table and build it as I described in my suggestion. When I tried to work with your import, I had trouble with changing the cell formats and with table lines before I threw it out. Not sure if there would have been other difficulties.

Regards,

Jerry

(1) storing standard numeric values in cells C3 thru F3 I was able to edit the second formula a way which may be what you wanted to achieve :

=IF(((F3-C3)-(E3+D3))*24>8;8;((F3-C3)-(E3+D3))*24)

=IF(((F3-C3)-(E3+D3))*24>8;((F3-C3)-(E3+D3))*24-8;0)

These formulas are valid on a machine using English as language and the comma as decimal delimiter.

(2) the used factor 24 let me think that the stored values aren't standard numbers but date time ones.

So, I tried to build a table with time values in libreOffice.

I got surprising results so I transferred it in openOffice and got the same behavior.

Here it is :

I'm puzzled by what I got in cell H4 whose formula is :

=IF(((F4-C4)-(E4+D4))*24>8;8;((F4-C4)-(E4+D4))*24)

So, I wrote a subset formula in K4 :

=(F4-C4) and really don't understand that.

In I4, the formula is :

=IF(((F4-C4)-(E4+D4))*24>8;((F4-C4)-(E4+D4))*24-8;0)

The formula behave as if F4 is not containing 11:00 but 11:00-24:00

and continuing to substract 24:00 when we move to the bottom.

To be sure that my main formulas were right, I decided to work with numbers in the rows 14 thru 23.

In C14 the formula is =2/24

In D14, it's =1/24

In E14, it's =1/24

In F14, it's =B14/24

Then I filled to the bottom (including the formulas)

This time, I got what I wanted so I was sure that the formulas are syntaxically correct.

As the function TIMEVALUE exists in openOffice and libreOffice, I tried to apply it building new formulas:

in M3, it's :

=IF(((TIMEVALUE(F3)-TIMEVALUE(C3))-(TIMEVALUE(E3)+TIMEVALUE(D3)))*24>8;8;((TIMEV ALUE(F3)-TIMEVALUE(C3))-(TIMEVALUE(E3)+TIMEVALUE(D3)))*24)

in N3, it's :

=IF(((TIMEVALUE(F3)-TIMEVALUE(C3))-(TIMEVALUE(E3)+TIMEVALUE(D3)))*24>8;((TIMEVALUE(F3)-TIMEVALUE(C3))-(TIMEVALUE(E3)+TIMEVALUE(D3)))*24-8;0)

I applied Fill Down but got errors because TIMEVALUE wasn't applying to text items but to time ones.

This is why I made a new attempt in rows 25 thru 34.

The cells are defined as text ones and bingo the formulas in columns M and N gave the correct results.

Quite good news

but I would be glad to know why the first set of formulas return the odd negative values.

Maybe an openOffice feature which I ignore.

Back to Numbers, I tried to build the same kind of table.

I got :

This time, the formulas using TIMEVALUE are in columns J and K and behave flawlessly.

in J3, it's :

=IF(((TIMEVALUE(F3)-TIMEVALUE(C3))-(TIMEVALUE(E3)+TIMEVALUE(D3)))*24>8;8;((TIMEV ALUE(F3)-TIMEVALUE(C3))-(TIMEVALUE(E3)+TIMEVALUE(D3)))*24)

in K3, it's :

=IF(((TIMEVALUE(F3)-TIMEVALUE(C3))-(TIMEVALUE(E3)+TIMEVALUE(D3)))*24>8;((TIMEVALUE(F3)-TIMEVALUE(C3))-(TIMEVALUE(E3)+TIMEVALUE(D3)))*24-8;0)

They are identical to those used in openOffice but we can't import the tables to get the correct behavior in both applications.

The only workflows which give the same tables in both applications are

(1) the one in which the original values are stored as decimal numbers on the basis : 1 hour = 1/24

(2) the one using text entries and TIMEVALUE

Yvan KOENIG (VALLAURIS, France) dimanche 22 avril 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

Yvan,

I think the Numbers approach is much cleaner than the original.

Jerry

Jerrold,

(1) I agree but as helpers, our problem is to try to find a workflow fitting askers needs.

(2) I really wish to learn that I made something weird with the openOffice table.

I really don't understand the results of the substractions.

Yvan KOENIG (VALLAURIS, France) dimanche 22 avril 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

The thing that I don't understand is the simi-colons in the sample .xls file. I wasn't aware that any US English spreadsheet app used them as parameter separators. The OP hasn't declared a location, but I'm assuming it's the United States.

Jerry

Jerrold

It's just that you forgot something. 😟

I explained several times that the delimiter is not defined according to the language but according to the decimal delimiter.

In countries using French with the decimal period, Numbers and other spreadsheets use the comma as parameters delimiter. If I remember well, it's the setting in French Switzerland.

In countries using English with the decimal comma, Numbers and other spreadsheets use the semi-colon as parameters delimiter. I don't remember if it's the standard UK setting but I know English users working this way.

I built specific resources files for these « exotic » settings when I worked upon AppleWorks. I'm not sure but maybe they are available in my iDisk.

Yvan KOENIG (VALLAURIS, France) dimanche 22 avril 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k