Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Timesheet with Night shift Calculation

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Timesheet with Night shift Calculation

    Hi..

    Need help to calculate night shift hours as well in the timesheet. I tried to calculate regular hour and overtime. But donít' know how to make night time calculation. So here are the things I want to have in the timesheet. (See in the attachment).

    * Regular Hours- It should be 8 Hours (Except if there are no any night hours), lunch & dinner time should be deducted.
    * OT Hours- The remaining time after regular hour should be Overtime.
    * Night Hours- Before 6 Am and after 6 pm working hours should count as night hours.

    It should look like as per the attachment which I have highlighted in red.

    Your support will be highly appreciated.

    Saligram
    Attached Files

  • #2
    Re: Timesheet with Night shift Calculation

    See attached - added columns P-T. I'm sure there's a neater solution but this appears to work - you will have to scenario test with more data.

    Solution uses if statements using reference cells to determine if start/end shift times are nights, then works back from total hours using simple logic to calc breaks / reg hours and overtime.
    Attached Files

    Comment


    • #3
      Re: Timesheet with Night shift Calculation

      Thanks for your hard work. It really works!

      Thanks once again,

      Comment


      • #4
        Re: Timesheet with Night shift Calculation

        I have had a go at this, see attached.

        A couple of points I have noticed.

        If someone finishes at 18:00 then the second break should not be calculated.

        From what I can understand your working day is 8hrs plus 4hrs overtime so your reg hrs will all ways be 8.
        The only hours that would change are the OT Hours & the Night Hours.

        I have added more columns to ems sheet to calculate all the different hours.

        Hope it helps.

        Again someone else may come up with a more elegant way.

        Windy
        Attached Files

        Comment


        • #5
          Re: Timesheet with Night shift Calculation

          First of all thank you all for the promt response with some solutions. It will be really great if you help me to modify these things. To be honest I tried it by myself but couldn't get it done. I have attached the orginal timesheet with it I will n't get confuse about the cell #.

          The first sample I got is quite

          Actualy I really don't need any extra columns. Is it possible to make it without extra column? If we must add columns then we can use it in the last area (In the attachment I wrote it 1/2/3/4/). So it will be easy to hide. Actualy it is my company corporate timesheet and I cann't change anything on it's design.

          1> Total/Regular hours means the hours after deducting all break times.
          2> If someone go to dinner at 1815 than the 15 minutes should deduct from the Day hours and add to night hours. Because he works 15 minutes and after 1800hrs it is night.
          3> It should n't be always 8 hours regular. It depends on time. If some one work from 16:00 to 04:00 than day time should be 2 and night hours should be 6 and the rest is overtime.

          So anyone has solution for me? I would like to thank you in an advance.

          Last thing I would like to add that this is my first time using this website and really get great impression of it. Thanks for the webmaster as well.
          Attached Files

          Comment


          • #6
            Re: Timesheet with Night shift Calculation

            The actual times that are input into this sheet ... are you able to use a different format? They are currently set to text which is not very preferable in excel.

            Comment


            • #7
              Re: Timesheet with Night shift Calculation

              The format of the fields are set to TIME. Thanks.
              Attached Files

              Comment


              • #8
                Re: Timesheet with Night shift Calculation

                I am ready to pay $25 if someone help me.. Thanks !!

                Comment


                • #9
                  Re: Timesheet with Night shift Calculation

                  Hi,

                  I just want to ask your help on how to get the values(no. of hours) on the attached Timekeeping worksheet using complex formula. Currently I'm doing it by manually counting the no. of hours and I'm tired doing it. I hope you can help me guys with this.

                  Thanks in advance.

                  Peter
                  Attached Files

                  Comment


                  • #10
                    Re: Timesheet with Night shift Calculation

                    Peter

                    This is a very old thread, you need to start a new one of your own.
                    You can link to this one from there.

                    Windy

                    Comment


                    • #11
                      Re: Timesheet with Night shift Calculation

                      hi,

                      I need help regarding shift allocation, I have 3 shift A,B & C. I a trying to fixed the shift time but I am confused how can I fixed this problem???


                      A Shift =>06:00 to <=14:00 date like 13 Nov
                      B Shift =>14:00 to <=20:00 date like 13 Nov
                      C Shift =>20:00 to <=06:00 but in C Shift there are 2 date one is 13 and another is 14

                      can any one help me regarding this problem???

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X