+ Reply to Thread
Results 1 to 10 of 10

Calculation of Night Allowance between specific hours using excel

  1. #1
    Registered User
    Join Date
    09-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Calculation of Night Allowance between specific hours using excel

    Hi,

    I am working on a timesheet (attached) that i hope will allow me to calculate the number of hours that are worked between 2200 and 0600, i have used the formula below

    =(G4-F4)*24-MEDIAN(22,6,MOD(G4,1)*24)+MEDIAN(22,6,MOD(F4,1)*24)-(INT(G4)-INT(F4))*(22-6)

    The number should be given in column K, however i am getting back 0 when the number should be 8 (with regards to the information that i have inputted to attempt the equation). column K is also formatted as number...

    If anyone is able to assist me with where i am missing something that would be most appreciated

    Many Thanks

    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculation of Night Allowance between specific hours using excel

    Hello Womble2011,

    That formula is only really valid when F4 and G4 contain dates and times, for times only try this version

    =MOD(G4-F4,1)*24-(G4<F4)*16+MEDIAN(F4*24,22,6)-MEDIAN(G4*24,22,6)
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculation of Night Allowance between specific hours using excel

    Hi Daddylonglegs,

    I have applied your formula to my spreadsheet and its given me a #VALUE! Error im not sure what that means??

    Thanks

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculation of Night Allowance between specific hours using excel

    Are you sure? It worked OK when I applied it in your sheet.....
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculation of Night Allowance between specific hours using excel

    Hi Daddylonglegs,

    I have applied your formula to my spreadsheet and its given me a #VALUE! Error im not sure what that means??

    Here is the attached...

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculation of Night Allowance between specific hours using excel

    I think you've added a row to that version so the first formula should refer to row 5

    =MOD(G5-F5,1)*24-(G5<F5)*16+MEDIAN(F5*24,22,6)-MEDIAN(G5*24,22,6)

  7. #7
    Registered User
    Join Date
    09-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculation of Night Allowance between specific hours using excel

    I dont know what happened, but i have re entered the formula it has worked, Thank you very much for you help

  8. #8
    Registered User
    Join Date
    09-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculation of Night Allowance between specific hours using excel

    So, I sent the timesheet to accounts for them to check.. they told me that night hours were 2000 - 0600.... i have tried to change the equation to

    =MOD(G4-F4,1)*24-(G4<F4)*16+MEDIAN(F4*24,20,6)-MEDIAN(G4*24,20,6)

    which changes the 22 in the original equation, however it doesnt give the 10 hours assuming G4 was 06:00 and F4 was 20:00

    Any thoughts please

    Thanks

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculation of Night Allowance between specific hours using excel

    You also need to change the 16, that represents the number of non night hours, so that should now be 14

    =MOD(G4-F4,1)*24-(G4<F4)*14+MEDIAN(F4*24,20,6)-MEDIAN(G4*24,20,6)

  10. #10
    Registered User
    Join Date
    09-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculation of Night Allowance between specific hours using excel

    Thank you so much! It works great now

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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