Calc and working hours

[DavideTN]

Hi,

I’ve a Calc 6.0 spreadsheet where peoples must sign their working hours (HH:MM).

In Total, to obtain the working hours for each person, I put this function

=IF(B3;IF(C3>B3;C3-B3;"");"")+(E3-D3)

For David it’s ok, but if a person - for example Robert or Mark - forgets to sign Afternoon Out, his Total function isn’t correct. In this case I would that function returns #### or a colour as a result of an error in the cell.

Is there a way to do this?

(Edited by @Lupp replacing SE() with IF().)

[mikekaganski]

What is that SE() function?

[Lupp]

It’s the Italian version of IF(). The OQ tried to put the formula “to international” and missed one occurrence.
I rectified this.

[DavideTN]

Thanks, I forgot to translate it.

[mikekaganski]

A tip: you could enable “Use English function names” under Options-LibreOffice Calc-Formula to have the functions spelled right for you.

[Lupp]

Even better for easing international cooperation: Use English (UK) UI (user interface). Your (Davide’s) English is obviously more than sufficient.

[Lupp]

You took precautions concerning the morning part to avoid gravely misleading results. You returned the empty string instead of a numeric resut then. Concerning the afternoon part the situation is the same. In addition you should return a clear error message for the ‘Total’ column if there was an error with one of the parts.

In addition you should consider to read my answer in this thread.

Edit 1 giving the amendment announced in my comment on the “I-don’t-know-answer”:

=IFERROR(IF(AND(B3>0;B3<1);IF(AND(C3>0;C3<13/24;C3>=B3);C3-B3;"test failed");"test failed")+IF(AND(D3>=13/24;D3<1);IF(AND(E3>0;E3<1;E3>=D3);E3-D3;"test failed");"test failed");"errors")

This isn’t exactly a monster formula. Nonetheless we should pursue clearer formulae and a structure of the sheets allowing for helper columns making everything easy.

No general purpose programmer would use that kind of bloated expressions. He (f/m) would inevitably use helper variables and control structures.

In fact you would need to also define what’s "Morning’ or ‘Afternoon’ exactly, and to check for compliance of the input with the definition … (See the clumsy attempt to do so exemplified by the 13/24 in my formula.)

Keeping worktimes, often in combination with the application of changing hourly rates is a ticklish thing, and not at all easily done with spreadsheet as soon as primages or work-times spanning more than one calendaric day occur.

[DavideTN]

I don’t know. Adding a subtotal is not a solutions in my case.
I’ve too many spreadsheet to modify

[Lupp]

-1- Please use the ‘add a comment’ tool for commenting and for asking a question in return concerning a post.
-2- Many sheets of same structure are most likely bad design basically.
-3- The insertion of columns for subtotals (morning / aftrnoon) would surely be the best way, but the additional tests can also be introduced to the formula for ‘Total’. See the amendment to my answer.

[DavideTN]

Ok, I try this formula.

Thank you very much

[DavideTN]

I’ve found a way changing hour format in cell from HH:MM to [H]:MM. Don’t ask me why or how but It works and ### appear.
Last problem: how can I change this format in 365 spreadsheets with bash (or a script)? Is there a way?

[Lupp]

This won’t be your “last problem” if you insist on a design with 365 (Why not 366?) sheets or more. It’s bad for many reasons.
If you feel sure to know better, you should start with solving this problem on your own.
I won’t ask you about time formats. I know.
Are you aware of the fact that spreadsheets containing more than 256 sheets cannot be opened with any of OpenOffice.org, Apache OpenOffice, and many still running installs of older MS Excel?

[DavideTN]

No, 365 spreadsheets are divided into 12 files, one for month.
I can apply [H]:MM format to cells by selecting every spreadsheets in a file, but maybe it’s better a script solution, also because spreadsheets are protected.

[Lupp]

Without knowing the details probably urging you to do it this way I would assume this a very bad design.
If the splits are needed due to the huge amount of data rows, you should consider to move the project to a database.
Don’t yo want to do any evaluations accross the days and the months?
If the size allows for it: Collect all the data rows in one sheet.