Ask Your Question

Calc and working hours

asked 2018-03-23 08:28:45 +0200

DavideTN gravatar image

updated 2018-03-23 08:59:45 +0200

Lupp gravatar image


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

image description

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


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().)

edit retag flag offensive close merge delete


What is that SE() function?

Mike Kaganski gravatar imageMike Kaganski ( 2018-03-23 08:40:57 +0200 )edit

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

Lupp gravatar imageLupp ( 2018-03-23 08:57:17 +0200 )edit

Thanks, I forgot to translate it.

DavideTN gravatar imageDavideTN ( 2018-03-23 09:12:13 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2018-03-23 09:16:38 +0200 )edit

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

Lupp gravatar imageLupp ( 2018-03-23 09:32:48 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-03-23 09:29:57 +0200

DavideTN gravatar image

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

edit flag offensive delete link more


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

Lupp gravatar imageLupp ( 2018-03-23 09:45:26 +0200 )edit

Ok, I try this formula.

Thank you very much

DavideTN gravatar imageDavideTN ( 2018-03-23 09:56:34 +0200 )edit

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?

DavideTN gravatar imageDavideTN ( 2018-03-26 09:24:42 +0200 )edit

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, Apache OpenOffice, and many still running installs of older MS Excel?

Lupp gravatar imageLupp ( 2018-03-26 12:38:26 +0200 )edit

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.

DavideTN gravatar imageDavideTN ( 2018-03-27 07:10:37 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-03-27 11:14:12 +0200 )edit

answered 2018-03-23 09:06:38 +0200

Lupp gravatar image

updated 2018-03-23 10:00:25 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-23 08:28:45 +0200

Seen: 910 times

Last updated: Mar 23 '18