+ Reply to Thread
Results 1 to 13 of 13

Problem in counting hours of work in employee salary form.

  1. #1
    Registered User
    Join Date
    01-06-2015
    Location
    Bergen, Norway
    MS-Off Ver
    2010
    Posts
    5

    Problem in counting hours of work in employee salary form.

    I am making an electronical salary form for my employees to fill out. As they will be using a tablet to fill out the form, I can't use macros for any functions.

    The form has the following functions:
    - A determines day of the week, ie "ddd".
    - B determines date, defined by simple numbers.
    - C is simple text, used as code for shift leaders, or other bonus pay, see J.
    - D, E, F is text, any notifications about the shift, etc.
    - G determines the start of the shift, f.ex. 18:00
    - H determines the end of the shift, f.ex. 02:00
    - I counts number of hours worked, in this example 8.
    - J applies bonus pay, as determined by C.
    - K determines the amount of extra pay for working from 18:00h on fridays, and all hours throughout shifts starting on sundays, counted in number of hours worked.

    My problem is getting excel to count hours after 18:00 in the K column, without it counting all hours after 18:00h, even if the shift were to start f.ex. at 19:00h or 23:00h.

    I've used this code:
    Please Login or Register  to view this content.
    (I use a Norwegian version of Excel, so I apologize in advance if I've mistranslated any code.)

    This code works if the shift on a friday starts before 18:00h (it will start counting hours from 18:00h onwards). But if it starts at a later time, f.ex. at 19:00h, it will count the number of hours from 18:00h, regardless if the shift (G column) starts at a later time.

    Any ideas on how to fix this?

    Regards, Heine

  2. #2
    Registered User
    Join Date
    01-06-2015
    Location
    Bergen, Norway
    MS-Off Ver
    2010
    Posts
    5

    Re: Problem in counting hours of work in employee salary form.

    Is it possible to use the MAX function to only count the number of hours worked after 18:00?

  3. #3
    Registered User
    Join Date
    05-13-2014
    Location
    Best
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Problem in counting hours of work in employee salary form.

    please submit complete xcel file!

  4. #4
    Registered User
    Join Date
    01-06-2015
    Location
    Bergen, Norway
    MS-Off Ver
    2010
    Posts
    5

    Re: Problem in counting hours of work in employee salary form.

    Mal Timeliste.xlsx

    The only problem here, is that the file is in norwegian, so it may not open correctly.

  5. #5
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Problem in counting hours of work in employee salary form.

    Hello
    Greetings for the day..!
    Can you please give an example of the result you want in K column or either attach your workbook with results written manually to understand your problem more precisely...

    Regards
    Sourabh Gupta

  6. #6
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Problem in counting hours of work in employee salary form.

    Hello
    Greetings for the day..!
    Can you please give an example of the result you want in K column or either attach your workbook with results written manually to understand your problem more precisely...
    Regards
    Sourabh Gupta


    Thanks for submitting your file....
    I will try to solve your problem

  7. #7
    Registered User
    Join Date
    05-13-2014
    Location
    Best
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Problem in counting hours of work in employee salary form.

    OK. I can adapt the formulas. I can read enough Norwegian.

  8. #8
    Registered User
    Join Date
    01-06-2015
    Location
    Bergen, Norway
    MS-Off Ver
    2010
    Posts
    5

    Re: Problem in counting hours of work in employee salary form.

    Mal Timeliste2.xlsx

    Here is an updated version, as you requested. Comments are included in the file.

  9. #9
    Registered User
    Join Date
    05-13-2014
    Location
    Best
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Problem in counting hours of work in employee salary form.

    =I17-(18-((G17)*24)) returns 8 as you said!
    You have a number of issues:
    1).When I import it in Xcel(US) the dates translate into Fri, Sat, Sun aso, while you test fixed text like "Fr".
    If it is needed to use your solution internationally you must change your formula as such that it automatically changes into the local date settings.
    2). You want to count the hours after 18.00 hrs to allow for overtime!
    When this is the case you must include a test like =IF(g17*24>18,g17)
    You have 4 possibilities to verify: B AND E < 18:00 ,, B <18:00 E>18:00 aso,,
    After that I suggest you calculate the difference!
    suggestion: start 12/12/2014 19:00 end 13/12/2014 02:00:00 when you enter the time like this your formulas become simpler!However data entering is a little more complicated!
    But you avoid problems when working from let's say 23:59 until 23:59:59 + more than 24 hrs.

  10. #10
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Problem in counting hours of work in employee salary form.

    Put this in K13 and copy it down.....
    =IF(AND(G13="",H13=""),"",IF(WEEKDAY(A13)=6,IF(G13*24<=18,H13*24-18,(H13-G13)*24),""))

    I dont know exactly what you want....but I have tried
    See it and tell if this is what you want

    Note>>It only works for fridays

    Regards

  11. #11
    Registered User
    Join Date
    05-13-2014
    Location
    Best
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Problem in counting hours of work in employee salary form.

    =IF(G17*24>18,IF(H17*24>G17*24,H17*24-G17*24),)
    This is a partial solution. But it gives the correct result for 1 of the combinations of start and end time.
    Now you must be able to build yr solution yourself I guess!
    Hope this was helpful.

    bart

  12. #12
    Registered User
    Join Date
    01-06-2015
    Location
    Bergen, Norway
    MS-Off Ver
    2010
    Posts
    5

    Re: Problem in counting hours of work in employee salary form.

    Using

    =IF(AND(G13="",H13=""),"",IF(WEEKDAY(A13)=6,IF(G13*24<=18,H13*24-18,(H13-G13)*24),""))

    how, do I incorporate IF(OR(WEEKDAY(A13)=7;WEEKDAY(A13)=1);(H13-G13)

    I can't seem to get it to work.

    PS: The reason I used ddd="fri", etc. was that it seems it's not sensitive to when the time goes over midnight.

  13. #13
    Registered User
    Join Date
    05-13-2014
    Location
    Best
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Problem in counting hours of work in employee salary form.

    hope this helps
    Attached Files Attached Files

+ 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: 2
    Last Post: 05-27-2014, 01:17 AM
  2. [SOLVED] Annual employee salary increase - after one full year of service -- formula
    By macrorookie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-21-2014, 04:26 PM
  3. Neep Help ! With Employee salary calculation spread sheet ! Very Hard!
    By lmplhk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2014, 03:03 AM
  4. work hours counting formula
    By adamek174 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-20-2012, 07:26 PM
  5. Tracking the total work hours of each employee
    By Keerthivasan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2011, 03:53 AM

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