+ Reply to Thread
Results 1 to 18 of 18

Timesheet Day hours Night hours Nght Rates Overtime rates

  1. #1
    Registered User
    Join Date
    01-07-2018
    Location
    Derby
    MS-Off Ver
    2010,365
    Posts
    8

    Question Timesheet Day hours Night hours Nght Rates Overtime rates

    Hi guys,
    I need help with my time-sheet.
    I work different shifts all the time so start times vary from 6:00 to 9:30, 12:00 16:00 21:00 etc. The shifts are mostly over 12 hours.
    I have different rates of pay for day shift 06:00 to 18:00 £12/hour, night shift 18:00 to 6:00 £13 and overtime is x1.5 accordingly. SO if day O/T it is £12*1.5=-£18 and if night O/T £13*1.5=£19.5

    I would like to calculate all the rates automatically and then sum up into 1 cell. I don't mind extra columns for data on side.
    I will attach a sample of my current time-sheet.

    Thank you in advance for your help. I have spent 2 nights on it already without any good results
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    602

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    I use two helper tabs: Aux1 to list all dates & hours worked and Aux2 to exhibit hours values only, which I use in COUNTIF formulas to distinguish Day from Night hours.

    I noticed that you use special rates as per customers and per weekday, so I added columns in your rates definition area to contemplate all instances. I guesstimated values for cells that were blank, so you may have to revise them. Then I use LOOKUP function to determine exact hour rate, as per weekday and per customer.
    Please run some test and let us know how it goes.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-07-2018
    Location
    Derby
    MS-Off Ver
    2010,365
    Posts
    8

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    This is amazing. I just need few changes. The overtime starts after 8 hours from starting time.
    I am so sorry, I should have mentioned it in the first post. I was trying to make the changes myself but can't figure it out sorry.
    And Saturdays and Sundays are flat rate. There is no difference between day-night-or overtime, but I don't think it makes any difference. It is good to have it in there
    for the future. Thank you so much for your hard work and effort. If you could sort out the O/T after 8 Hours that would be great. I hope there won't be too much to change
    Also I just realized, that the hours come from Aux1 will I have to copy it for every following week? I have all 52 weeks of the year in one sheet, I only posted four as an example, I thought I could just copy and paste
    once I have the formulas done, but it looks like I would have to adjust it for each new week.
    Thank you so much.
    Last edited by viper8283; 01-13-2018 at 06:20 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    602

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Youíre welcome. Iím glad to help.
    The calculation is just like that. The first Day (orange) column and the first Night (gray) column show total respective hours, but from that total, only eight hours are multiplied by regular Day or Night rate. The remaining, i.e. o/t, is multiplied by its correspondent o/t rate.
    For example, January 6, total of 10 hours, you get 8 regular hours (£116,00) plus 2 overtime (£43,50).

  5. #5
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    602

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    As for new rows for your weeks to follow, I had dragged the formulas in Aux1 and Aux2 to row 120, if memory serves, so if you need more rows, just copy them down as required.
    Then paste your weeks in Timesheet tab, following the same pattern as those you showed, for some formulas use words from column A as reference.

  6. #6
    Registered User
    Join Date
    01-07-2018
    Location
    Derby
    MS-Off Ver
    2010,365
    Posts
    8

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Oooh, I got it now. I got confused. because it showed 2 reg hours and 13.75 night and 3.75 overtime. I thought it was adding it altogether, but now I see in the O/T formula subtracts from 13,75 8 and only the rest multiplies by O/T rate.
    I just noticed that overtime is not calculated properly. If you look on 3/1/2018 for example I worked 14 hours. Started at 23:00 so it should give 7 night + 1 day hour + 6day Overtime but nothing comes up.
    Same for night O/T if the case is that the overtime falls on the night. Could you fix this please?

    Thank you
    Last edited by viper8283; 01-14-2018 at 10:46 AM.

  7. #7
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    602

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Hello, viper8283.
    Sorry I missed that part in sorting overtime.
    It is indeed trickier than I first thought.
    I have not yet given up, but my formulas fail in some cases, so I would ask the Excel experts to take a look and maybe some idea comes up.
    I'll get back to you if I manage to do it properly and consistently.

  8. #8
    Registered User
    Join Date
    01-07-2018
    Location
    Derby
    MS-Off Ver
    2010,365
    Posts
    8

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    No problem at all . Thank you for spending so much time on it and trying really hard to perfect it.

  9. #9
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    602

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Hello, viper8283.

    I have managed to build a volatile formula with COUNTIFS and OFFSET functions to accurately and consistently count the first eight hours worked and tell Day from Night hours.
    Then I do the same for the overtime hours.
    All this calculation is in helper columns in Aux2 tab.

    Again, please run some test and let us know how it goes.
    Attached Files Attached Files
    Last edited by Estevaoba; 01-16-2018 at 03:49 PM.

  10. #10
    Registered User
    Join Date
    01-07-2018
    Location
    Derby
    MS-Off Ver
    2010,365
    Posts
    8

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Hi there,
    sorry I didn't reply earlier. I have been very busy since the last time. I have worked on the timesheet today and it seems to work fine except it returns a negative value on one of the days.
    I attached my actual timesheet so you have better idea and to see exactly where it happens. It is on TBC-2018 sheet at position "Y-38". You'll see it. Also, if you put work on Sunday the Day O/T rate for example at Q-25
    returns error, All other days work fine. Other then that seem to be working perfect so far.
    Thank you again for your help and time. It is amazing what you have done with it
    Attached Files Attached Files
    Last edited by viper8283; 01-30-2018 at 03:21 PM.

  11. #11
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    602

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Hi, viper! You're welcome!

    I see that I forgot to change formulas for regular hours pay. Old ones were based on my first model.
    Sorry about that.

    Since you changed the structure of your sheet, I made these formulas simpler:

    Please paste in R25:
    =IF(N25="","",N25*O25)

    And Y25:
    =IF(U25="","",U25*V25)

    Then copy down.

    That will take care of that erroneous negative value.

    For work on Sundays, the error cause must be in column C. The LOOKUP function seeks a client there, so please check if it is Day off instead of a client name from the range A2:A18.

    Anyway, I changed the LOOKUP range in the formula, for O25

    =IF($A25="Day",$O$24,IF($N25="","",VLOOKUP($C25,$A$2:$N$18,IF(WEEKDAY($B25)=7,9,IF(WEEKDAY($B25)=1,10,5)),0)))

    And Q25:

    =IF($A25="Day",$Q$24,IF($P25="","",VLOOKUP($C25,$A$2:$N$18,IF(WEEKDAY($B25)=7,11,IF(WEEKDAY($B25)=1,13,6)),0)))

    Then copy down.

    There will be errors though, if, for example, client Clipper is selected for Sunday. There is no value for Clipper in Sunday column.
    The same for other clients depending on column selected.

    Please check that if an error comes.


    Godspeed!
    Last edited by Estevaoba; 02-01-2018 at 08:19 PM.

  12. #12
    Registered User
    Join Date
    01-07-2018
    Location
    Derby
    MS-Off Ver
    2010,365
    Posts
    8

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Hi Estevaoba
    the negative value issue is sorted. But Sunday still does not work regardless of which customer you use. I understand that all the rates have to be filled in in order for it to work. I did fill rates for
    Arla and still comes up with an error. Tried any other supplier and same result. Even Yusen.

    Thank you

  13. #13
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    602

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Hello, Viper!
    I'm glad we are getting there!
    I made a few adjustments in formulas from column J thru Z, just removed some conditions that are no longer required, and it works with Sunday and Saturday clients.
    Please find attachment.
    I use winrar to compress files, but it's not compatible here, so I deleted rows in auxiliary tabs. Please copy them back down to fit your rows in front tab.

    Take care!
    Attached Files Attached Files
    Last edited by Estevaoba; 02-03-2018 at 07:18 PM.

  14. #14
    Registered User
    Join Date
    01-07-2018
    Location
    Derby
    MS-Off Ver
    2010,365
    Posts
    8

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Hurraaaaay,
    I think we made it. I have been using the timesheet for last 2 weeks and seem to be working fine. Thank you so much for your time and effort it makes my life so much easier, it is truly amazing. I think this is one of the best timesheets out there
    I will get back to you should any issues come up in the future, for now we re GOOOOOOD
    If anyone else needs a good timesheet feel free to use it. Thank you again Estevaoba

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,875

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  16. #16
    Registered User
    Join Date
    01-07-2018
    Location
    Derby
    MS-Off Ver
    2010,365
    Posts
    8

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    Done. Sorry I am new to forums surprisingly as I have been using computers since 1994 LoL :D :D :D
    Thank you for your guidance
    Have an awesome day

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,875

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    You, too. OT, but I was born in Derby.

  18. #18
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    602

    Re: Timesheet Day hours Night hours Nght Rates Overtime rates

    You're welcome, viper8283.
    Glad to help.
    Take care!

+ 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. Overtime hours and different rates of pay
    By gmcq in forum Excel General
    Replies: 1
    Last Post: 08-15-2017, 08:07 PM
  2. overtime hours day / night
    By Berna11 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-11-2017, 01:57 PM
  3. Replies: 2
    Last Post: 07-28-2017, 01:33 PM
  4. Separating overtime hours from regular hours in timesheet
    By aphatmc in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-20-2017, 10:00 AM
  5. Replies: 3
    Last Post: 06-18-2014, 07:26 AM
  6. Timesheet formula for calculating ordinary hours and overtime hours.
    By zoen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-18-2011, 04:23 AM
  7. Timesheet for Working hours and Standby Rates
    By euwest30 in forum Excel General
    Replies: 1
    Last Post: 10-18-2006, 11:34 PM

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