Building Excel Schedule that totals weekly time worked

[jmenzel30]

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

[JohnTopley]

Try


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

[mrice]

Try

Please Login or Register  to view this content.

[johnmpl]

Hi, to all!

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

Blessings!

[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?

[johnmpl]

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!