Full Version: Time Card
jmcwk
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
KingMartin
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
jmcwk
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?
KingMartin
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
jmcwk
Thank You!
KingMartin
You're welcome

M.