Formula Help

Solved/Closed
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I need a formula to calculate overtime. I have this spredsheet that tracks everyones time, a regular day is 8 hours, anything above that is overtime, any time worked on the weekends is also overtime. So basically whenever a number greater than 8 is entered from Mon to Fri, the difference is considered ot. When ever any number is entered on Sat and Sun that number is also ot.

I would like the ot hours to add up in a different column.

eg. a person time for Sun to Sat is, Sun 5, Mon 8, Tue 10, Wed 8, Thur 12, Fri 8, Sat 0. The ot for this person is 11 hours.

Thanks



2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
https://authentification.site/files/23562832/SAMPLE1.xls

Please see attach sample. If a person works more than 8 hours, then the difference of hours is considered ot, those hours are then placed in a separate column, if that same person works more than 8 hours the following day, then the difference is added to the column with the ot hours, etc.....
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You need to provide every thing since you already have so many macros that do this and that. also when you upload the file, could you either provide the password for the file or have it removed.
https://authentification.site/files/23589903/SAMPLE1.xls

I have removed the password, sorry about that, I forgot. See attached, let me know if this works for you.
Greatful for any help.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
OT based on what ? Week or month. If week then when a week starts and when it ends
OT based on days. The spredsheet tracks time on a daily basis, it continues from Jan 1 to Dec 31st. Whenever an employee works their full shift the number 8 is entered into the corresponding cell for for that date. 8 is a full shift, any number greater than 8 is considered OT, so if 12 is entered then that person worked 4 ot hours. Also ANY number entered on a WEEKEND is also OT hours. I basically want all the OT hours to add up into a separate column for each employee. Hope this helps.
not sure if you got the reply.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You can use this formula
=SUMIF(N$16:GL$16, "S*",N18:GL18) + SUMPRODUCT((N18:GL18>8) * (LEFT(N$16:GL$16,1) <> "S") *(N18:GL18 -8)*1)

You would have issue if there is a holiday on a weekday
https://authentification.site/files/23673755/SAMPLE1.xls

I applied the formula, there seems to be a problem. please see attachment for details. thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try the updated formula. I have corrected the original formula