Hi All I need some help with LibraCalc.
Each Cell for F has =D## - B##
My total on F76 is SUM=(F71:F75)
in F76 I would also like it to subtract 30 minutes for lunch break or 60 minutes depending on what I put for my lunch say F74 is lunch
I tried SUM=F71:F75-F74 and get error
then in lets say Cell G76 I would like it to display everything over 8:00
so if F75 = 9:30 hours hours after it subtracts Lunch I would like G75 to display 1:30
thanks for any help in advance
Mike
What you are trying to do is =SUM(F71:F75)-2*F74. A quick glance shows the total should end in a 5. In F76 you have added in a time that should be subtracted so it needs to be subtracted twice. To avoid this risk of error it is best if you don’t mix different calculations in the same column. Obviously, this isn’t an answer but an explanation, more information is needed.
What is the significance of column C and E? Are they added or subtracted?
It would be best if the calculation for lunch were a negative number that could be added like the other times. Do we need to add another column to calculate this or would we use C and E to calculate this too?
I need the time to be broken down into PER Job thats why Col F
D.T is for my own personal use
at the end of the Day I want to take the total of Col F and subtract the LUNCH period to give me total time worked so I can look at it quickly for billing and also see when my company is trying to rip me off for time
I want it to add up total time of Col F and subtract 30 minutes without having to use a second cell
Without using another cell you have to integrate the 30 Minutes in the formula.
.
In Calc time is represented a fraction of a day, so 24 hours= 1 wich gives 0.5 for 12 hours, 0.25 for 6 hours etc. Generally you have a formula of =(hours/24). In your case you could use 1/48 for the half hour, but using the /24 version makes it easier to recognize “somebody is calculating with hours per day” in case your tool is reworked for missions to mars.
.
=SUM(F71:F75)-(0.5/24) would be my solution here, but I’d usually suggest to have a separate cell to keep lunch-time flexible.
I think your parameters are too restricting for a complex task.
In the attached spreadsheet in tab 2023 you can add times in as before but I have added a new column Break in which you can use for lunch or other breaks; if you enter anything in the break column it will subtract the times.
To extend the calculation columns, coloured green to identify, select the last two green cells and drag down.
I have put subtotals above the headings so if you filter on the Date column you will get a total for that day. The subtotal ends at row 3000 which should be sufficient for a year.
I have added a pivot table tab which will give you some figures at a glance. Right-click the pivot table and select Refresh to update it after adding info to the rows on tab 2023. Once again it is limited to row 3000
Finally, I added a Summary tab but it might get too resource intensive so probably best to delete the tab.
TotalTimeAndOvertime2EA.ods (21.4 KB)
BTW if D refers to drive time to a site then you might need to include driving back again unless you just charge that to the next job but you still need to get back to your workplace or home at the end of the day. Travel from workplace to home is normally your expense but claimable from site back to workplace.
Why?
A separate result deserves a separate cell. It’s about clarity and reliability, not about saving cells.
Also;
TimeOfDay is cyclic (modulo 1 day). Time is not.