I have created a time sheet but I need a formula for the following scenario: Any hours over 40 hours oer week is overtime AND any hours past 5:30 pm are overtime. I can easily do the formula for the over 40 hours but not for the past 5:30 pm. Can anyone help me?
Sorry everyone. I have attached the timesheet. I don't have to calculate pay, just hours worked.
[SOLVED] Timesheet that Calculates overtime after 05:30 pm
[SOLVED] Timesheet that Calculates overtime after 05:30 pm
- Attachments
-
- Time sheet.ods
- (12.7 KiB) Downloaded 145 times
Last edited by robleyd on Fri Nov 08, 2019 1:38 am, edited 3 times in total.
Reason: Add green tick
Reason: Add green tick
openoffice 4.1.6 on Windows 10
Re: Timesheet that Calculates overtime after 05:30 pm
Presuming the following: Start Time is in column A, End Time is in column B, End Time will be before midnight of the same day, over-time is calculated at time and a half.
will give you the correct number of hours, regular and over-time (if applicable) for that shift.
If you want/need help with incorporating this with your current spreadsheet, as your formula for calculating over-time after 40 hours will need to be changed, the attach a sample of your spreadsheet.
Code: Select all
=B2-A2+IF(B2>TIME(17;30;0);B2-TIME(17;30;0))*1.5
If you want/need help with incorporating this with your current spreadsheet, as your formula for calculating over-time after 40 hours will need to be changed, the attach a sample of your spreadsheet.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Timesheet that Calculates overtime after 05:30 pm
A database would be the only adequate tool for this.
The attached spreadsheet is not easy to handle.
The attached spreadsheet is not easy to handle.
- Attachments
-
- shifts_t96177.ods
- (78.81 KiB) Downloaded 155 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Timesheet that Calculates overtime after 05:30 pm
You may have noticed 2 issues with my spreadsheet draft:
1. Some overtime hours are calculated twice as late hours after 17:30 and weekly hours >40 as well
2. Using the weeknum function to identify the weeks should not be done like that. Notice that the hours of 2018-12-31 belong to the first week of 2019 but they are aggregated with the hours of the first week in January. The weeknum function follows the ISO standard which counts the week with more than 3 days as first week of next year or last week of the old year.
Instead of =WEEKNUM($B2) in column G you can insert this ...
... and then refresh the pivot on Sheet2. The pivot's year column is obsolete then. The weeks are still identified by ISO standard with Monday being the first day of week.
Problem 1. is not that easy to handle. May be you want it like that. May be you want to not count late hours summed up with the week hours.
Problem 1. can be handled by subtracting the late hours on Sheet2: Sheet2.F5 =MAX(0;$D5-40/24-$E5)
1. Some overtime hours are calculated twice as late hours after 17:30 and weekly hours >40 as well
2. Using the weeknum function to identify the weeks should not be done like that. Notice that the hours of 2018-12-31 belong to the first week of 2019 but they are aggregated with the hours of the first week in January. The weeknum function follows the ISO standard which counts the week with more than 3 days as first week of next year or last week of the old year.
Instead of =WEEKNUM($B2) in column G you can insert this ...
Code: Select all
=YEAR($B2+IF(AND(WEEKNUM($B2;1)=1;MONTH($B$2)=12);1;IF(AND(WEEKNUM($B2;1)>51;MONTH($B$2)=12);-1;0)))&"-"&TEXT(WEEKNUM($B2;1);"00")
Problem 1. can be handled by subtracting the late hours on Sheet2: Sheet2.F5 =MAX(0;$D5-40/24-$E5)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Timesheet that Calculates overtime after 05:30 pm
I am so sorry for taking nearly a year to reply.I was off of this project for a long time. Thanks so much Villeroy! I was able to use the information you provided as a guideline and with some tweaks I was able to make it work.
openoffice 4.1.6 on Windows 10