Timesheet that Calculates overtime after 05:30 pm

Discuss the spreadsheet application

Timesheet that Calculates overtime after 05:30 pm

Postby charmar » Sat Dec 08, 2018 12:39 am

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.
Attachments
Time sheet.ods
(12.7 KiB) Downloaded 7 times
Last edited by charmar on Wed Dec 12, 2018 7:55 pm, edited 1 time in total.
openoffice 4.1.6 on Windows 10
charmar
 
Posts: 1
Joined: Sat Dec 08, 2018 12:30 am

Re: Timesheet that Calculates overtime after 05:30 pm

Postby RusselB » Sat Dec 08, 2018 2:22 am

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.
Code: Select all   Expand viewCollapse view
=B2-A2+IF(B2>TIME(17;30;0);B2-TIME(17;30;0))*1.5
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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4922
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Timesheet that Calculates overtime after 05:30 pm

Postby Villeroy » Sat Dec 08, 2018 4:45 am

A database would be the only adequate tool for this.
The attached spreadsheet is not easy to handle.
Attachments
shifts_t96177.ods
(78.81 KiB) Downloaded 12 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Timesheet that Calculates overtime after 05:30 pm

Postby Villeroy » Sat Dec 08, 2018 2:13 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 ...
Code: Select all   Expand viewCollapse view
=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")

... 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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Majestic-12 [Bot] and 16 guests