+ Reply to Thread
Results 1 to 6 of 6

Building Excel Schedule that totals weekly time worked

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    St. Louis
    MS-Off Ver
    2016
    Posts
    24

    Building Excel Schedule that totals weekly time worked

    Hello,

    I am trying to build an excel schedule for a bank staff. The potential hours of operation are from 8:45 AM - 6:15 PM. My staff is complaining about the lunches not being taken out of the daily and weekly calculations. The daily total hours formula I am using is =((IF(C14<B14,C14+1,C14)-B14)*24)-0.5. In one cell I put the time in, the next cell I put the time out, without scheduled lunches or entry. I have it built as a half hour lunch, But if the staff isn't working that day it automatically calculates the total hours as -.50. How can I make it so that the lunches are subtracted only if there they are working? Or that it won't calculate the negative value. The weekly total hours worked formula is: =SUM(D14,G14,J14,M14,P14,S14). When not times are entered that cell shows at -2.5 I would like it to just display as a "0" and auto subtract the lunches on a daily basis.

    The program won't let me attach the file, but maybe that is user error or bc I'm on a work computer. Your help is greatly appreciated!

    Thanks

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: Building Excel Schedule that totals weekly time worked

    Try


    =MAX(((IF(C14<B14,C14+1,C14)-B14)*24)-0.5,0)

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,797

    Re: Building Excel Schedule that totals weekly time worked

    Try

    Please Login or Register  to view this content.
    Martin

    If my solution has saved you time and/or money, please consider donating to Cancer Research UK or your local equivalent.

    https://www.cancerresearchuk.org/

  4. #4
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    239

    Re: Building Excel Schedule that totals weekly time worked

    Hi, to all!

    You could try:
    =MAX(,24*MOD(C14-B14,1)-0.5)

    Blessings!

  5. #5
    Registered User
    Join Date
    01-03-2018
    Location
    St. Louis
    MS-Off Ver
    2016
    Posts
    24

    Re: Building Excel Schedule that totals weekly time worked

    These all did great... now I just thought of something that could make it more tricky... I have people that work a half days etc, is there a way to have it auto calculate that if they are at work for less than 4.5 hours it doesn't calculate the lunch?

  6. #6
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    239

    Re: Building Excel Schedule that totals weekly time worked

    Quote Originally Posted by jmenzel30 View Post
    These all did great... now I just thought of something that could make it more tricky... I have people that work a half days etc, is there a way to have it auto calculate that if they are at work for less than 4.5 hours it doesn't calculate the lunch?
    This could help:
    =MAX(,24*MOD(C14-B14,1)-IF(24*MOD(C14-B14,1)>=4.5,0.5))

    Blessings!

+ 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