Forum Discussion

Mike_Geraghty's avatar
Mike_Geraghty
Copper Contributor
Mar 25, 2022

Calculating an hourly penalty rate within a shift that may have non-penalty hours within that shift.

Dear discussion members, I have tried finding if there is an issue already addressed with my problem, and I have not been able to find any to date. Therefore I need your kind assistance and draw up...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Mar 27, 2022

    Mike_Geraghty 

    In the attached version, I have cleared the start and end times for day you don't work,

    Instead of first calculating the penalty hours, I have first calculated the normal hours, then calculated penalty hours as total - normal.

Mike_Geraghty's avatar
Mike_Geraghty
Copper Contributor

HansVogelaar 

Hi Hans,

Please find attached the workbook I have been working on.

I hope you have received my previous post re the reason for my inquiry.

Also note I am using an old laptop with an old version of excel.

I have a number of issues to resolve,

1. I am trying to calculate a penalty rate. A friend has tried to help but we are having problems as you can see.

For example if I am not working on that day then I still have a default of 4 hrs is calculated, and we cannot calculate the 2nd. half.

2. Another issue is I have to build a VLOOKUP so that when I enter in a Work Status (Column C and Drop Down List sheet)) information is calculated by the specific cell. For example if I have annual leave the default time of 8 hrs will show in column AA. Conversely if I am sick it will show in AB.

 

Anyway step-by-step. Right now I need to resolve the penalty rate issue.

 

Once again thank you ... Cheers Michael

HansVogelaar's avatar
HansVogelaar
MVP
Mar 27, 2022

Mike_Geraghty 

In the attached version, I have cleared the start and end times for day you don't work,

Instead of first calculating the penalty hours, I have first calculated the normal hours, then calculated penalty hours as total - normal.

  • Mike_Geraghty's avatar
    Mike_Geraghty
    Copper Contributor
    Mar 28, 2022
    hans Vogelar
    Thank you sooooo much.
    I can now push ahead with what I am capable of.
    Is it possible to seek your assistance further if so required?
    My next phase is putting together VLOOKUP table so when I enter in the work status drop down entry the start time will recognise it to be zero where applicable.
    For example if I have Annual Leave as the work status then column AA will show the desired hours, in this case 8. Conversely for sick day, and so on.
    I do hope I am not adversly drawing on your 'intellectual property' ... hahha.
    As a side note from a excel professional, do you think the way I am setting it out looks easy to read and interpret?
    Kind regards once again ... Michael




    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Mar 28, 2022

      Mike_Geraghty 

      You cannot use a formula for the start time, since you want to be able to enter the start time manually in most situations. It would be possible to use VBA code, but that might be overkill:

      • It would disable undo when the user changes work status.
      • It would require all users to enable macros when they open the workbook.

      In the version I attached to my previous reply, I already entered formulas in columns AA and AB to fill in annual leave hours and sick day hours based on work status.

      • Mike_Geraghty's avatar
        Mike_Geraghty
        Copper Contributor
        Mar 30, 2022

        HansVogelaar 

        Thank you Hans,

        I was not expecting you to help me so much, that is why I did not notice what you kindly did to help me.

        How do I arrange a case of beers to be sent to you ??? 🙂 Or a single malt whisky ??

         

        Cheers ... Michael

Resources