Formula Help
Solved/Closed
Neo
-
Jul 27, 2010 at 09:19 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 17, 2010 at 09:52 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 17, 2010 at 09:52 PM
Related:
- Formula Help
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Credit summation formula - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 27, 2010 at 01:50 PM
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 3, 2010 at 08:45 AM
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
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.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 5, 2010 at 08:14 AM
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
=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
I applied the formula, there seems to be a problem. please see attachment for details. thanks
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 5, 2010 at 10:23 AM
Aug 5, 2010 at 10:23 AM
Try the updated formula. I have corrected the original formula
Jul 28, 2010 at 11:29 AM
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.....
Jul 30, 2010 at 08:13 AM
Jul 30, 2010 at 08:31 AM
I have removed the password, sorry about that, I forgot. See attached, let me know if this works for you.
Aug 3, 2010 at 08:16 AM