Posts by Excel Noob

  • Update Cells On Opening

    Re: Update Cells On Opening


    Dave,


    I tried what you gave me and it seems to work for the most part but it does not put any totals in the Starting count column. I would not be surprised if I did something wrong but I copied and pasted what you wrote. I have attached the workbook with the macros in place. Thanks



    Code
    Private Sub Workbook_Open() 
    Range("Used,Restocked").ClearContents 
    Range("StartingCount") = Range("EndingCount") 
End Sub

  • Update Cells On Opening

    Hi,


    I have attached a workbook that has 2 documents in it. The first sheet is an inventory list. It is formulated so that when somebody enters amounts in the "Count, Used and Restocked" fields the "Ending Total" shows the final count. I would like this sheet to auto update the "Starting Count" field and erase the "Used" and "Restocked" fields so the next time the document is opened the "Starting Count" would be the same as the "Ending Count" from the last time the document was edited and the "Used" and "Restocked" fields would be blank.


    I assume this would be done with a Macro but I'm a Noob so I might be wrong.


    Hope this makes sense. Thanks

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    I've amended your sheet. The error arose because the formula calculating total overtime hours had "" which I replaced with zero. Works now.


    Stephen,


    That works great. I will amend my sheet to replace the "" with a 0 and it's perfect. You Rock.


    Thanks a bunch.

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from Ranger

    Hi Excel Noob,


    Why don't you hide the 2 rows with the separate calculations as in the attached, then it shows the format you are looking for.


    Bill


    Bill,


    The problem with that is I send these timesheets on to my head office and if the values aren't visible then they will scratch their heads. Some of them are not the sharpest tools in the drawer so even if I tell them to unhide the rows I sure it will cause confusion.

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time




    Bill,


    I see what you mean and if I use it I will carry the formula across. Thanks for your time.

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    On your sheet, try in Q35
    and in Q36



    Stephen,


    That looks like it should work but I still get the value warning as a result. I have read that you are supposed to hit Ctrl, Shift & Enter all at the same time to enter an array formula but it doesn't appear to work. I copied the formula you supplied and paste it into my worksheet but as I said it comes up with the value error. :confused:

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from Ranger

    Hi Excel Noob,


    I have added the formulas to the bottom of your spreadsheet, they seem to work ok


    Bill



    Thanks Bill,


    If all else fails then I will have to break it out and add the rows like you did. I would much rather work within the existing format as it is cleaner but if it can't be done then it is what it is. Thanks much.

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    Let you off then. Ranger's provided you with a solution but you could just amend the formulae to:


    Well I tried the formula and changed the cells referenced to D33 to S33 and it comes up with a value warning. The problem here is that the days worked will change from week to week so the overtime values will be anywhere from D33 to S33. I need the formula to work within the timesheet without breaking it into other columns like Ranger made. These are timesheets that my employee's fill out and e-mail me every week. The numbers at the top of the sheet represent days of the month. Whatever date it is that's the column where they enter their time worked.


    Not trying to be difficult and I really do appreaciate all of your efforts

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    Well, that was based on the info you supplied in the first post.


    Sorry Mate I'm a Noob:p I did say that the OT was calculated in a seperate box though ;)

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time




    Thanks for the formula but it appears that you are factoring in the 8 hours of regular time. That is completely removed from the forula because I am already showing the OT as a seperate value. I have attached a file on a earlier post.

  • Calculate Overtime

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    You'll have to explain when overtime kicks in, i.e. where does your 17/8.5 hours come from?


    The overtime is already totalled and it's in a seperate box. 8 hours is a regular day. The first 4 hours after the 8 is 1.5x and anything beryond that is 2x.
    14 hour work day 8 hours of regular 4 hours of 1.5x and 2 hours of 2x = 14hours.


    As I said I do not need to calcualte the regular time as I have already seperated the regular time from the overtime. D33 to S33 only contains the total overtime hours worked for each day.
    Thanks for the quick response.

  • Calculate Overtime

    Hi I'm new to the site so be gentle : D


    I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates.


    Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go.


    Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x. Hope this makes sense.:confused: