# Building Excel Schedule that totals weekly time worked

1. ## 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. ## Re: Building Excel Schedule that totals weekly time worked

Try

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

3. ## Re: Building Excel Schedule that totals weekly time worked

Try

``Please Login or Register  to view this content.``

4. ## 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. ## 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. ## Re: Building Excel Schedule that totals weekly time worked

Originally Posted by jmenzel30
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!

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

#### 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