Would One of you Excel Folks mind looking at the attached and give me an idea on how to automatically calculate the time for an employee if the H column is set to Y (for Yes) or N (for No)? The only entries made by the employee are in Column(s) B,C,H

Column B Time In
Column C Time Out
Column H Lunch Taken (Y or N defaults to Y)

Each Employee is entitled to 30 minutes of lunch so if time in is 0800 and time out is 1630 and H is Y, total work hours is 8:00 if N then total hours of course would be 8:30

Thank You

Hi John,

I don't understand all the auxilliary columns.

Convert the military time into a serial date/time value, apply the necessary maths and custom format.

Try the following:

=(C3-B3)/2400-(H3="y")*30/1440

apply custom format:

[hh]:mm

You may want to store 30 in a cell for the case that the lunch break duration changes.

Martin

Martin,

Thank You and I am with you on the auxiliary Columns, this is something the wife created, found,borrowed,? who knows and she uses it at work I told her I would see what i could find out of course with the caveot that i know little or nothing in dealing with Excel anyway =(C3-B3)/2400-(H3="y")*30/1440 is for which column?

Hi again John,

I can't remember which column, but it delivers the desired result - end minus start minus the 30 min. break if available.

MArtin

Thank You!

You're welcome

M.