+ Reply to Thread
Results 1 to 4 of 4

calculating time values

  1. #1
    Registered User
    Join Date
    03-07-2008
    Posts
    3

    calculating time values

    Hello

    I have on situation.
    one worker works 09:00 pm to 01:00 am
    other 05:00 pm to 04:00 am
    i have to pay about different time values:
    06:00 pm to 10:00 pm 10% more than 50$
    10:00 pm to 06:00 am 20% more than 50$

    I want that excel calculate this if I give start time and end time

    I hope You understand what I mean, because my english is not wery good.

    Help please

  2. #2
    Registered User
    Join Date
    03-07-2008
    Posts
    3

    explain

    I think I have to explain more what I need:

    H.Gustavson start 09:00 pm and finish 01:00 am
    06:00 pm to 10:00 pm 10% more than 50$
    10:00 pm to 06:00 am 20% more than 50$

    so

    i have to pay 10% more 1 hour (09:00pm-10:00pm)
    and 20% more 3hour(10:00pm-01:00am)
    1x50$x1,1=55$
    3x50$x1,2=180$
    sum: 55+180=230$
    this is what I want that excel to if I give start and end time

    Please help

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    If start time of shift is in A2 and end time in B2 then use this formula in C2 to get "regular hours" (06:00 - 18:00)

    =((A2>B2)*MEDIAN(0,B2-1/4,1/2)+MAX(0,MIN(3/4,B2+(A2>B2))-MAX(1/4,A2)))*24

    Then in D2 for 10% hours (18:00 to 22:00)

    =((A2>B2)*MEDIAN(0,B2-3/4,1/6)+MAX(0,MIN(11/12,B2+(A2>B2))-MAX(3/4,A2)))*24

    and then for 20% hours in E2

    =MOD(B2-A2,1)*24-C2-D2

    format C2:E2 as number

  4. #4
    Registered User
    Join Date
    03-07-2008
    Posts
    3
    Thank you daddylonglegs,

    I tried this formula but i had an error (excel highlights in the first formula MEDIAN(0,B2-1/4,1/2)
    can you please make this function on excel for me and upload the file here.

    If i could make this formula work then its wery helpfull for me.

+ 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