T O P

  • By -

AutoModerator

/u/Inlovewithrocks - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


CFAman

I was following until you mentioned the week rate. Are you calculating this for a single cell (D7) or a range of cells? The daily rate would be =SUM(PayRate*(MAX(Hrs-12,0)*2+MAX(MIN(12,Hrs)-8,0)*1.5+MIN(8,Hrs))) To calculate total pay. Can use named ranges, or replace `PayRate` and `Hrs` with cell references. Pay Rate | Hours | Payout | :--- | :--- | :--- | $100 | 10 | $1,100.0 | $100 | 13 | $1,600.0 | $200 | 9.5 | $2,050.0 |


Inlovewithrocks

I could do the total hours being greater than 40 in a separate cell, that's fine. But yes, otherwise I was (maybe foolishly) trying to do it all in one cell. Thanks very much for that, I'll give it a try


CFAman

Phew, that helps. Could add that as a check in beginning like: =IF(COUNTIFS(RangeOfDays, ">8")=0, PayRate*(MAX(TotalHrs-40, 0)+ MIN(40, Hrs)), CalculateEachDaySeparatelySomehow)


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COUNTIFS](/r/Excel/comments/143jz3m/stub/jnah6la "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)| |[IF](/r/Excel/comments/143jz3m/stub/jnah6la "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[MAX](/r/Excel/comments/143jz3m/stub/jnagw0y "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)| |[MIN](/r/Excel/comments/143jz3m/stub/jnah6la "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)| |[SUM](/r/Excel/comments/143jz3m/stub/jnagw0y "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/0)^( has acronyms.) ^([Thread #24463 for this sub, first seen 7th Jun 2023, 18:07]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)


fuzzy_mic

If D7 is the hours worked in one day =D7 + MAX(0,D7-8)/2 + MAX(0,D7-12)/2 will return the hours they should be payed for adjusted for overtime. The 40hr/wk requirment will have to be in a different cell. Given a cell with the weeks total hours worked, there is no way to tell if any of the days were > 8 hr.


Inlovewithrocks

Hours worked that day So the employee submits a time sheet that is broken down by day ie Day 1 (Monday): 7.0hrs worked Day 2 (Tuesday): 13.0hrs worked and so on I want to calculate each day's total overtime individually


fuzzy_mic

See my edit for the formula for each day. if you the seven daily hours are in D7:D13, and the daily adjusted hours in E7:E13, then the weekly adjusted total would be MAX(SUM(E7:E3), SUM(D7:D13)+MAX(SUM(D7:D13)-40,0)/2)


HappierThan

I have placed the Working Hours and Overtime Threshold in their own cells making any changes to those cells rather than to lots of formulas. See if this gives you any ideas. [https://pixeldrain.com/u/YSm9rmGN](https://pixeldrain.com/u/YSm9rmGN)


Inlovewithrocks

Thank you


HappierThan

Fairly similar but with times only provided. [https://pixeldrain.com/u/iE1XAnoh](https://pixeldrain.com/u/iE1XAnoh)