+ Reply to Thread
Results 1 to 2 of 2

Counting work hours using formulas

  1. #1
    Registered User
    Join Date
    10-14-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    5

    Counting work hours using formulas

    Hi there!
    I currently have a worksheet, that counts hours worked by the employees. The problem is, that each day that they work, they have a 1hr brake, which is not payed.
    Each day is calculated using this formula:

    =IF(ISBLANK(I2);"0,00";IF(ISBLANK(I4);"0,00";((((IF(I4<I2;I4+1;I4)-I2)-(MOD(IF(I2>I4;MIN(I4;TIME(7;0;0))-MAX(I2;TIME(22;0;0));MAX(0;TIME(7;0;0)-I2)+MAX(0;I4-TIME(22;0;0)));1)))*24)*A3)+((MOD(IF(I2>I4;MIN(I4;TIME(7;0;0))-MAX(I2;TIME(22;0;0));MAX(0;TIME(7;0;0)-I2)+MAX(0;I4-TIME(22;0;0)));1)*24)*(A3*1,5))))

    The day rate is regular, the night rate is *1.5(the night rate starts at 22:00, ends at 7:00) I need that each time the start and end times are filled in, there would be a one hour deduction in total day, the deducted rate is counted in regular day rate.

    Does anyone have any ideas how to achieve this?

    I'm also attaching an example workbook, to better understand my needs.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,173

    Re: Counting work hours using formulas

    use a macro instead of formula.

+ 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. Replies: 5
    Last Post: 09-12-2019, 04:16 AM
  2. Excel Work Hours Sheet: need help with Cumulative Totals Formulas
    By tke490 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-03-2016, 03:08 PM
  3. Problem in counting hours of work in employee salary form.
    By johansen.heine in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-06-2015, 05:47 PM
  4. Condition Formulas for Counting hours between Time Shifts
    By intercon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-16-2014, 02:37 PM
  5. [SOLVED] formulas for counting the analyst work during a day
    By Megatronixs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-26-2012, 01:49 PM
  6. work hours counting formula
    By adamek174 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-20-2012, 07:26 PM
  7. translate work schedule to chart counting hours
    By wilsonta in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2011, 10:37 AM

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