Formula Help

Solved/Closed
Neo - Jul 27, 2010 at 09:19 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 17, 2010 at 09:52 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 27, 2010 at 01:50 PM
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
0
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.....
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 30, 2010 at 08:13 AM
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.
0
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.
0
Greatful for any help.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 3, 2010 at 08:45 AM
OT based on what ? Week or month. If week then when a week starts and when it ends
0
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.
0
not sure if you got the reply.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 5, 2010 at 08:14 AM
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
0
https://authentification.site/files/23673755/SAMPLE1.xls

I applied the formula, there seems to be a problem. please see attachment for details. thanks
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 5, 2010 at 10:23 AM
Try the updated formula. I have corrected the original formula
0