Help: Calculation to count hours worked per week (even when not all data is there)


I’m currently trying to learn more “Calc” functions and I’m trying to do a “weekly time-sheet” so I can write down my working hours and track them per week. The issue I have is that whenever I don’t add a value to one of my “fields” the counting doesn’t work. I know that the reason is my function and how I do the calculation, but I can’t figure out how I add another check to see if a field is empty and then ignore it.

It kind of looks like this

| Day | Clock-in |Break Start|Break end|Clock-out|Hours worked|


|Monday|09:10|12:20|13:00|15:48|“Formula to count hours worked this day”|

|Tuesday|09:00|12:00|13:00|17:00|“Formula to count hours worked this day”|



|Total hours worked|||||“Formula to count total worked hours”|

If possible I would also prefer if instead of making a field empty I can use a “-” since I think it makes it easier to read the sheet. Though if that makes it a lot harder, I’m glad to cut it. :smiley:

My problem is that when I haven’t finished my day yet (or even started it) it instead says “#VALUE!” and my total hours can’t be counted either since it tries to count the total working hours per week, but one (or more) of the values are “#VALUE!”.

I would love if I could get some help so until I fill in “clock-out” the value is automatically set to 0.0 Hours worked. So that if I’m out sick one day or haven’t reached that weekday yet, I can still see how many hours I have worked on a particular week.

Formula for counting “Hours worked” - This is where I believe the problem is

I would if I could get some help here to add an extra check so if any of the fields have the value “-” it defaults to make it 0.0 worked hours. And once I have a clock-in and clock-out it counts how many hours I have worked.

I don’t have a break every week-day since I only work 4 hour days some days of the week, so the “break-start and break-end” need to be optional fields which is what I tried doing by this part “(D33=”-")".

=IF((D33="-");(F33-C33)*24;(F33-C33)*24 - (E33-D33)*24)

Formula for counting “Total hours worked”

I don’t really think this needs anything changed if I can get help to make all “Hours Worked” fields to be 0 until they have a value in Clock-out.


Thanks in advance for any help with solving this! I tried looking at other time-sheets, but I just couldn’t figure out how if (something) else if (something else) else if (something else) else (last part). :frowning:

File: Upload files for free - WorkingHours.ods -

Hi. I am not an expert in Calc but I see some points you might have overlooked.

If you are not at the end of your day it might be best to set the day’s total at zero, especially as it will remind you to fill it in the next day if you forget.

I think that as you are using time calculations then you are best using time formatting. In the attached spreadsheet, I formatted the cells as [HH]:MM which meant that I could delete the *24 you had in your calcs.

I’m not about to create odd Time formats so I added some conditional formatting so if =0 the the text is white and you can’t see it.


Cheers, Al

As comment below


Nah. You’ll have to get rid of all those dashes, you can then have in cell G9 =IF((F9=0),0,(F9-C9) - (E9-D9)) and for overtime have in cell D6 =IF(C6<B6,0,C6-B6). Apply the conditional formatting to D6 if desired

Thank you for the help! That solved the whole issue for me. :slight_smile:

And you are right, I should rather check if I’m not at the end of the day (like your suggestion). Now I have a working time-sheet.

Thanks once again for the help. :slight_smile:

You might want to put this formula in C6 =IF(SUM(G9:G13)>B6;B6;SUM(G9:G13)) and the following one in D6 =IF(SUM(G9:G13)<B6;0;SUM(G9:G13)-B6) to tidy it up a bit more