+ Reply to Thread
Results 1 to 9 of 9

Calculating penalty rates based on day of the week

  1. #1
    Registered User
    Join Date
    06-28-2017
    Location
    Australia
    MS-Off Ver
    Excel v15.26 for Mac
    Posts
    4

    Calculating penalty rates based on day of the week

    Hi,

    I am trying to calculate pay based on hours worked and the date/day of the week I worked.
    I have tried to use IF and WEEKDAY functions but haven't been able to work out the right formula, and am not sure if it's the correct approach.

    I want to be able enter the hours worked next to the day and date, and have excel calculate the amount earned for that day under the right rate column, to look a bit like this Screen Shot 2017-06-28 at 10.54.36 PM.png

    This will eliminate some error, and make it one step easier than the system I have.

    Any help is much appreciated.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Calculating penalty rates based on day of the week

    duplicated
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Calculating penalty rates based on day of the week

    can you attach the spreadsheet
    in C3
    =if(WEEKDAY(C3,2)<6, b3*25,"")

    in D3
    =if(WEEKDAY(C3,2)=6, b3*30,"")

    in E3
    =if(WEEKDAY(C3,2)=7, b3*38,"")

    however, you also need to test for a public holiday
    do you have a list of public holiday dates

  4. #4
    Registered User
    Join Date
    06-28-2017
    Location
    Australia
    MS-Off Ver
    Excel v15.26 for Mac
    Posts
    4

    Re: Calculating penalty rates based on day of the week

    That's perfect, thank you.

    I thought I'd probably need to enter the public holiday hours/pay manually.

    I have a list of public holidays for this spreadsheet:

    2016
    Monday, 1 August
    Monday, 3 October
    Sunday, 25 December
    Monday, 26 December
    Tuesday, 27 December

    2017
    Sunday, 1 January
    Monday, 2 January
    Thursday, 26 January
    Friday, 14 April
    Saturday, 15 April
    Sunday, 16, April
    Monday, 17, April
    Tuesday, 25 April
    Monday, 12 June

    Where might I start to make a rule for public holidays?
    Attached Files Attached Files

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Calculating penalty rates based on day of the week

    add the dates into a column and then lookup to see if the date exists in the list

    you could use a table to look up the hourly rate - so that you can make any changes to that value without updating all the formulas
    see how i have added to pub hols
    Attached Files Attached Files

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculating penalty rates based on day of the week

    Similar to etaf's this puts the pay rates in a helper row 1 and the weekday ranges in row 2.

    The holidays are listed in column J.

    Then this formula in C4 filled down and across column E.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and this formula in the holidays worked column F.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  7. #7
    Registered User
    Join Date
    06-28-2017
    Location
    Australia
    MS-Off Ver
    Excel v15.26 for Mac
    Posts
    4

    Re: Calculating penalty rates based on day of the week

    That's great, works well. Thanks again

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculating penalty rates based on day of the week

    To which post is that addressed?

  9. #9
    Registered User
    Join Date
    06-28-2017
    Location
    Australia
    MS-Off Ver
    Excel v15.26 for Mac
    Posts
    4

    Re: Calculating penalty rates based on day of the week

    I used elements of both solutions, so both yours and etaf's posts were useful.
    For this spreadsheet I used your second suggestion for public holidays, and a variation of etaf's for having the hourly rate separately, and the formula.

    I have just started to use Excel and will probably refer to both suggestions later.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formulas to Add shift penalty rates to specific times & days
    By sman1 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 03-26-2020, 11:24 PM
  2. [SOLVED] Formula for calculating a value based on tiered percentage rates
    By d_max_c in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2014, 04:04 AM
  3. [SOLVED] Formula Help... Calculating charges based on stepped rates.
    By HoosierIT in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-23-2014, 05:55 PM
  4. Replies: 6
    Last Post: 05-16-2014, 11:30 AM
  5. Hi Everyone! Penalty Rates question right off the bat!
    By Szuperwoman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-17-2012, 01:29 AM
  6. calculating penalty rates
    By piaclaire in forum Excel General
    Replies: 6
    Last Post: 08-26-2010, 08:02 PM
  7. [SOLVED] Calculating a Date Based on Year, Week Number and Day of the Week
    By amy in forum Tips and Tutorials
    Replies: 1
    Last Post: 08-23-2005, 11:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1