Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Timesheet with Night shift Calculation

  1. #1
    Join Date
    4th July 2010
    Posts
    5

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th July 2010
    Posts
    8

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th July 2010
    Posts
    5

    Re: Timesheet with Night shift Calculation

    Thanks for your hard work. It really works!

    Thanks once again,

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    17th November 2005
    Location
    Kent
    Posts
    639

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th July 2010
    Posts
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    9th July 2010
    Posts
    8

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    4th July 2010
    Posts
    5

    Re: Timesheet with Night shift Calculation

    The format of the fields are set to TIME. Thanks.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    4th July 2010
    Posts
    5

    Re: Timesheet with Night shift Calculation

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    3rd February 2014
    Posts
    1

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    17th November 2005
    Location
    Kent
    Posts
    639

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 7
    Last Post: October 1st, 2008, 10:54
  2. Report Shift Timesheet
    By rwc231 in forum EXCEL HELP
    Replies: 7
    Last Post: December 18th, 2006, 05:22
  3. Timesheet - TAX Calculation Formula Dilemma...
    By Mark B in forum EXCEL HELP
    Replies: 7
    Last Post: June 22nd, 2005, 11:34
  4. Rota shift calculation
    By mstodge in forum EXCEL HELP
    Replies: 9
    Last Post: May 14th, 2005, 18:58
  5. Excel timesheet calculation
    By Mamamech in forum EXCEL HELP
    Replies: 8
    Last Post: May 25th, 2003, 12:32

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
  •  
porno