+ Reply to Thread
Results 1 to 3 of 3

Formula: To calculate duration between two times, if duration is over 6hours subtract 30mi

Hybrid View

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Formula: To calculate duration between two times, if duration is over 6hours subtract 30mi

    Hi guys,

    I'm looking for some help with a formula, recently I have been promoted to dept. manager in a supermarket this requires me to create weekly rotas for my dept. on MS Excel.
    The weekly rotas are pinned on the notice boards for colleagues to read, but also totals up colleague hours per day and per week, and also totals up the hours used on the dept. for a particular day (for management purposes).

    Currently, I am required to work out how many hours each colleague works per day, enter a value in the "Hrs" column per shift, per colleague and theres a sum formula to total these up, providing the total number of hours a colleague
    has worked in the week (To ensure colleague is working contracted hours) (I have attached the new rota I have been working on - its relatively easy to understand looking at that)

    Ideally I would like a formula to work out the duration between two times (ie. a shift 07.00 - 12.00 = 5 Hours), and IF the shift exceeds 6hours to minus a 30min break (Ie. a shift 12.00 - 20.30 = 8hours) because the colleague
    clocks out for a 30minute break, hence does not get paid for that break period.

    I have been trying to work out a formula for hours, but no luck

    Colleague D is highlighted Orange as it contains no formulas/formatting etc. and ideally this is what I would like the entire rota to look like.

    Sorry if I'm asking a lot, just wanted to make my life easier and save time, while minimising mistakes.

    Any Help would be greatly appreciated
    Regards,
    Matt
    Attached Files Attached Files

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

    Re: Formula: To calculate duration between two times, if duration is over 6hours subtract

    Hello Matt,

    In cells B10 and D10 you don't have valid times, for excel at least, if you enter those times with a colon between hours and minutes like 12:00 and 20:30 then you can use this formula to calculate the total hours and deduct half an hour if over 6

    =(D10-B10)*24-IF((D10-B10)*24>6,0.5)
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Formula: To calculate duration between two times, if duration is over 6hours subtract

    Wow, Thanks DaddyL, Works a treat!! Thanks for the speedy reply aswell!!
    You're going to save me so much hassle!
    Can't thank you enough!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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